SMALL function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wally3178 - 20 May 2008 05:10 GMT

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,

Signature

Wal

Reply to this Message

T. Valko - 20 May 2008 05:50 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Hi,
>
[quoted text clipped - 10 lines]
>
> Cheers,

Reply to this Message

Wally3178 - 20 May 2008 06:47 GMT

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,

Signature

Wal

> 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
[quoted text clipped - 21 lines]
> >
> > Cheers,

Reply to this Message

T. Valko - 20 May 2008 07:48 GMT

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

Signature

Biff
Microsoft Excel MVP

> Hi Biff,
>
[quoted text clipped - 32 lines]
>> >
>> > Cheers,

Reply to this Message

Wally3178 - 20 May 2008 09:37 GMT

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,

Signature

Wal

> >Once we get the first frosts then the minimums will start
> >to be minus numbers. Will that alter the formula?
[quoted text clipped - 43 lines]
> >> >
> >> > Cheers,

Reply to this Message

Bob Phillips - 20 May 2008 09:52 GMT

Try this

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

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

Signature

---
HTH

Bob

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

> Hi again Biff,
>
[quoted text clipped - 62 lines]
>> >> >
>> >> > Cheers,

Reply to this Message

Fred Smith - 20 May 2008 06:30 GMT

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.

> Hi,
>
[quoted text clipped - 10 lines]
>
> Cheers,

Reply to this Message

Wally3178 - 20 May 2008 06:54 GMT

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,

Signature

Wal

> 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?
[quoted text clipped - 16 lines]
> >
> > Cheers,

Reply to this Message