Need help cell with MIN and MAX values

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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:

Code:
=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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,714
Office Version
  1. 365
  2. 2010
Try this:

Code:
='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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 8, 2018
Messages
1,714
Office Version
  1. 365
  2. 2010
You didn't say that F69 had a max value (of 87750). Does this do it?

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

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Works like a charm and thank you SO much for helping...!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,284
Messages
5,635,306
Members
416,852
Latest member
kanaikls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top