Minimum Value in a Range if Greater Than "X"

**DCSwearingen** · 02-23-2007, 10:57 AM

I have a range of Q3:T14 containing monthly totals for four fiscal years, including this year which is not complete.

I would like to calculate the minimum value for the months that have all input totaled (input coming from 10 different locations and summarized).

So, what I would like to do is calculate the minimum value if it it over, say, 100,000,000.

I would like to also look at the StDev for the same range for the values over that same 100,000,000.

Any advice? I am sure it is simple, but I am stuck.

Thanks for all the help this forum provides!!

**daddylonglegs** · 02-23-2007, 12:36 PM

Not sure about the stdev but to get a minimum value over 100000000 either

=MIN(IF(Q3:T14>100000000,Q3:T14))

confirmed with CTRL+SHIFT+ENTER

or

=LARGE(Q3:T14,COUNTIF(Q3:T14,">100000000"))

**jasoncw** · 02-23-2007, 12:46 PM

You can use an array formula to accomplish this:

=MIN(IF(B1:B8>5,B1:B8))

For standard deviation:

=MIN(IF(B1:B8>5,B1:B8))

Since these are array formulas, you need to confirm with CTRL + SHIFT + ENTER, rather than just ENTER.