Excel Formula with a max limit

[Jignasa_Patel]

Hi,

I am trying to figure out a formula to calculate 25% of a particular cell until the outpul cell reaches a certain total. e.g calculate 25% of C11 until the output totals $5,000. Then the output cell should continue showing $5,000 even if C11 keeps increasing. So in month 1, if C11 is $15,000 then calculate 25% on it=$3,750 will be the output. In month 2 if C11 is $20,000 then output will be $5,000. Then in month 3 if C11 is $25,000 then output should stay constant at $5,000. Any help will be greatly appreciated. Thank you in advance!

[#2 · Accepted solution]

@Jignasa_Patel 

Somewhat ironically, what you need is the MIN function.

=MIN(5000,C11*.25)

The largest this will go to is 5000; so long as one quarter of C11 is less than 5000, it will yield that lower number.

[#3]

@Jignasa_Patel 

if the amounts you need to make calculations on are in cell A2, and the calculated amounts are in column B, then in B2 we put a simple formula:

=H19*0.25

In B3 we accumulate total number and check the limit:

=(IF(SUM($B$2:$B2)>=5000,0,IF(SUM($B$2:$B2)+A3*0.25>=5000,5000-SUM($B$2:$B2),A3*0.25)))

Or you can use additional columns to calculate accumulated total, which will make it simpler to understand later on. Also, here we hardcoded 5000 that is our limit. To make it easily changable you can put it in a separate cell and reference in the formula.

[#4]

LOL........I am starting to learn something more than the very basic in excel so I thought I needed a max formula. Thank you so very much!! It works :)) Thank you for the quick help!! Good day to you!!

[#5]

Hi Andrew, thank you for taking time in giving me this formula. Let me try this and see how this goes for me. Many thanks!!