# Need help cell with MIN and MAX values

#### leopardhawk

##### Well-known Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### kweaver

##### Well-known Member
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
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
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
Works like a charm and thank you SO much for helping...!

Replies
1
Views
45
Replies
3
Views
126
Replies
4
Views
556
Replies
5
Views
199
Replies
3
Views
113

1,129,396
Messages
5,636,060
Members
416,896
Latest member
Hozier

### 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.

### Which adblocker are you using?

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

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