SMALL function

[Wally3178]

Hi,

I am trying to display the minimum temperature for the year by using the
following formula:

=SMALL(E51,H51,K51,N51,Q51,T51,W51,Z51,AC51,AF51,AI51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$51,$W$51,$Z$51,$AC$51,$AF$51,$AI$51,$AL$51,0)+1)

Can anyone tell me why this formula won't work?

The cells are each months minimum temperature which is calculated using the
MIN function

Cheers,

 

[T. Valko]

Looking at the way your formula is constructed it appears that you want the
min that is greater than 0 so I guess that means then are no negative
numbers.

Try this array formula** :

=MIN(IF((MOD(COLUMN(E51:AL51),3)=2)*(E51:AL51>0),E51:AL51))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Fred Smith]

Why can't you calculate the Min for the year the same way you do for each
month? Isn't just the Min of all readings you have in the year?

Regards,
Fred.

 

[Wally3178]

Hi Biff,

I see what you are saying.

Once we get the first frosts then the minimums will start to be minus
numbers. Will that alter the formula?

Cheers,

 

[Wally3178]

G'day Fred,

You are quite right, it would be easier. However I am using the exercise
to teach myself more about the functions in Excel so I am deliberately not
using the easy way.

I started using the SMALL function because blank cells were giving me a
result of zero for the months with no reading entered. Of course one I had
the first record for the month then the lowest reading was that entered for
the first day but it was still zero for the remaing months of the year, this
played havoc with my charts.

Cheers,

 

[T. Valko]

Once we get the first frosts then the minimums will start

to be minus numbers. Will that alter the formula?

Yes.

I've read your reply to Fred. I think you need to explain in more detail
what exactly is the criteria for the min.

Empty cells can be excluded but are you sure you want to exclude numeric 0?
0 can be a valid min temp.

 

[Wally3178]

Hi again Biff,

OK. Minimum temperatures here can be down as low as -10C and as high at
35C, whilst maximum temperatures can be as low as 0C (or lower) and as high
as 52C. Empty cells need to be excluded but zero does not.

So that empty cells do not show up as 0 in the totals, I am using the formula:

=IF(COUNT(T12:T41)=0,"",MIN(T12:T41))

Could this be part of the problem?

Cheers,

 

[Bob Phillips]

Try this

=MIN(IF(T12:T41<>"",T12:T41)

which is an array formula, so commit with Ctrl-Shift-Enter

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)