MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 387|Reply: 7

Simple If/Min Formula

[Copy link]

7

Topics

295

Posts

715

Integral

Lord

Rank: 4

Integral
715
Post on 2-10-2017 07:10:27 | All posts |Read mode
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




Reply

Props Report

0

Topics

587

Posts

1368

Integral

King

Rank: 6Rank: 6

Integral
1368
Post on 2-10-2017 08:20:37 | All posts



If you're unable to provide a spreadsheet create a mock-up one and post that instead.
Make sure it includes the results youre looking for.
Reply Support Opposition

Props Report

0

Topics

628

Posts

1450

Integral

King

Rank: 6Rank: 6

Integral
1450
Post on 2-10-2017 10:17:35 | All posts



If you're unable to provide a spreadsheet create a mock-up one and post that instead.
Make sure it includes the results youre looking for.
Reply Support Opposition

Props Report

0

Topics

615

Posts

1422

Integral

King

Rank: 6Rank: 6

Integral
1422
Post on 2-12-2017 00:32:18 | All posts



If you're unable to provide a spreadsheet create a mock-up one and post that instead.
Make sure it includes the results youre looking for.











Reply Support Opposition

Props Report

0

Topics

628

Posts

1450

Integral

King

Rank: 6Rank: 6

Integral
1450
Post on 2-12-2017 00:55:44 | All posts



File uploaded as requested
Have updated 95% of the formula just wanted to get the final balance to calculate based on the resulting balance of main limit less balance of sub limit (i.e final balance)
I am getting a circular error as using the same cells in the formulas








  • dental.xlsx
    (9.1 KB, 2 views)
    Download

  • Reply Support Opposition

    Props Report

    19

    Topics

    1896

    Posts

    4035

    Integral

    Forum Legend

    Rank: 8Rank: 8

    Integral
    4035
    Post on 2-12-2017 03:24:40 | All posts



    If you're unable to provide a spreadsheet create a mock-up one and post that instead.
    Make sure it includes the results youre looking for.











    Reply Support Opposition

    Props Report

    19

    Topics

    1896

    Posts

    4035

    Integral

    Forum Legend

    Rank: 8Rank: 8

    Integral
    4035
    Post on 2-12-2017 04:57:53 | All posts



    File uploaded as requested
    Have updated 95% of the formula just wanted to get the final balance to calculate based on the resulting balance of main limit less balance of sub limit (i.e final balance)
    I am getting a circular error as using the same cells in the formulas








  • dental.xlsx
    (9.1 KB, 6 views)
    Download








  • Reply Support Opposition

    Props Report

    19

    Topics

    1896

    Posts

    4035

    Integral

    Forum Legend

    Rank: 8Rank: 8

    Integral
    4035
    Post on 2-12-2017 06:18:23 | All posts



    1.  No need to use reply with quotes unless you need to point to a specific comment.  Otherwise it's just clutter
    2.  Can you show what you expect here, Im having a hard time understaning
    Reply Support Opposition

    Props Report

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 11-18-2017 09:02 , Processed in 0.185873 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!