|
SMALL function
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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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:AL510),E 51:AL51)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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, -- Wal "T. Valko" wrote: 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:AL510),E 51:AL51)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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, -- Wal "Fred Smith" wrote: 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" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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. -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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, -- Wal "T. Valko" wrote: 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:AL510),E 51:AL51)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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, -- Wal "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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, -- Wal "T. Valko" wrote: 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:AL510),E 51:AL51)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
SMALL function
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) "Wally3178" wrote in message ... 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, -- Wal "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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, -- Wal "T. Valko" wrote: 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:AL510),E 51:AL51)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Wally3178" wrote in message ... 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,A I51,AL51,COUNTIF($E51,$H$51,$K$51,$N$51,$Q$51,$T$5 1,$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, -- Wal |
| All times are GMT +1. The time now is 01:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com