Forum Discussion
Solved
Excel Formula with a max limit
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 outp...
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.
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.25In 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.
- 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!!
