Results 1 to 4 of 4

Simple If/Min Formula

Threaded View

  1. #1
    Registered User
    Join Date
    05-05-2016
    Location
    Melbourne
    MS-Off Ver
    Excel 2016
    Posts
    31

    Simple If/Min Formula

    Hello forum users.

    Although I can't provide a copy of my spreadsheet I will do my best to explain the result I am trying to achieve

    Okay

    There is a main limit of $700.00
    There is a sub limit of $300.00

    The benefits are 60% of the charge up-to the limits

    Quote first item under main limit $1,000.00.

    Quote second item under sub limit $400.00

    I have an initial min formula based on sub limit to give result based on the lower of the limit remaining or 60% of the cost.

    The result is $300.00 as the limit is less than 60% of the cost

    The main limit is calculated based on two other formulae

    It needs to be reduced by the benefit paid already under the sub limit i.e $700.00-$300.00.

    Balance of main limit $400.00

    The second formula needs to calculate the minimum of 60% of cost (1K) or remaining limit (after deduction of sub limit) being $300.00.

    So final formula needs to calculate 60% of 1k or limit (minimum of), being $300.00.

    60% of 1K is $600.00 however limit remaining after deduction of sub limit is $300.00, so benefits paid $300.00 & both limits now show as zero

    Hope this makes sense

    I have been getting a circular error as I am using the cell where the balance remaining of the main limit is twice for the main & sub limits linked

    How can I get around this?

    Thank you
    Last edited by Aussibuilder50000; 02-10-2017 at 11:25 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Simple Formula?!
    By caziscarol in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2015, 10:45 AM
  2. Help on a Simple Formula Please
    By seraphim666 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2013, 07:22 AM
  3. Replies: 4
    Last Post: 12-07-2012, 11:49 AM
  4. Formula for Discount Percentage
    By Ali H in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 09-18-2012, 04:05 AM
  5. [SOLVED] Simple formula help
    By Vasant Nanavati in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 AM
  6. [SOLVED] Simple formula help
    By zero in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. Need a simple formula please
    By Denise in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2005, 08:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1