Need help cell with MIN and MAX values

[leopardhawk]

My formula below works when the value in F69 is higher than the value in E27. However, if the value in F69 is lower than the value in E27, it is returning an amount that is higher than D27 and for the purposes of this workbook, this will not work. This is the problem that I am having trouble figuring out. Here is the formula:

=MAX(0,('effective tax rate'!D27-(F69-'effective tax rate'!E27)*0.15))

The formula itself is in cell F73 on my sheet (if that matters).

Currently, the value in D27 is 7494, the value in F69 is zero (0) and the value in E27 is 37790. The maximum value that should ever be shown in F73 is 7494. With the value in F69 at 0, the value being returned in F73 is 13162.5 which is way too high. I think I know why this amount is being returned but I can't find a way to do it correctly.

So, I am hoping someone might be able to help me to show the maximum in F73 at 7494 and the minimum value at 0, regardless of the value in F69??

This is my conundrum.

Appreciate any help.

 

[kweaver]

Try this:

='effective tax rate'!D27-MAX(0,F69-'effective tax rate'!E27)*0.15

If I understand you correctly, you don't want the F69-E27 to be negative or it will ADD it to D27. Yes?

 

[leopardhawk]

Okay, your formula is now returning the correct amount (7494) in F73 when F69 is 0 however, as soon as F69 reaches the threshold of 87750, F73 is returning a negative number and this will not work in the workbook. The lowest number that can be displayed in F73 is 0.

Thanks for the effort though!

 

[kweaver]

You didn't say that F69 had a max value (of 87750). Does this do it?

=MAX(0,'effective tax rate'!D27-MAX(0,MIN(87750,F69)-'effective tax rate'!E27)*0.15)

 

[leopardhawk]

Works like a charm and thank you SO much for helping...!