# Max-Min Function

#### PJM45

##### New Member
Hello,

I'm looking to create a max-min formula that only calculates when any of the values are greater than 5%.

Examples:

4 rows of data

Ex#1
A1:16 B1:4 C1:0 D1:4. In this case I want the Max-Min function to calcuate.

Ex#2:

A1:0 B1:3 C1:4.5 D1:3. In this case I do not want the Max-Min function to cacluate, but have it return 0 has my value.

Can someone help?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Welcome to the board..

Try this array formula enterd with CTRL + SHIFT + ENTER

=MAX(IF(A1:D1>0.05,A1:D1))

Same for MIN..

So how would I combine the two?

My orginal formula was Max(a1:d1)-Min(a1:d1)

Try

=MAX(IF(A1:D1>0.05,A1:D1))-MIN(IF(A1:D1>0.05,A1:D1))

Entered with CTRL + SHIFT + ENTER

Actually, don't need the max(IF part...
Because you want a 0 if none of the values are above 5%

Try
=IF(MAX(A1:D1)>0.05,MAX(A1:D1)-MIN(IF(A1:D1>0.05,A1:D1)),0)

Entered with CTRL + SHIFT + ENTER

Thanks for the response... These formulas are working for the most part...but in a situation where....

A1:3 B1:6 C1:7 D1:8

The results returns 2

I need it to return 5...

I want the result to return 0 only when all 4 cells are less than 5%

OK, I see now.. Lot's of missunderstanding there...

I thought you meant

MAX(A1:D1)-MIN(smallest number larger than 5%)

try just
=IF(MAX(A1:D1)>0.05,MAX(A1:D1)-MIN(A1:D1),0)

No need for the CTRL + SHIFT + ENTER

Replies
0
Views
55
Replies
3
Views
69
Replies
3
Views
151
Replies
8
Views
280
Replies
1
Views
522

1,202,905
Messages
6,052,473
Members
444,584
Latest member
gsupike

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