Max-Min Function

[PJM45]

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?

 

[Jonmo1]

Welcome to the board..

Try this array formula enterd with CTRL + SHIFT + ENTER

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

Same for MIN..

 

[PJM45]

So how would I combine the two?

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

 

[Jonmo1]

Try

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

Entered with CTRL + SHIFT + ENTER

 

[Jonmo1]

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

 

[PJM45]

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%

 

[Jonmo1]

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