Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
I need to get the maximum and minimum values in a list of numbers, some of
the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
Here's one way...
For the Minimum: =IF(MIN(A1:A10)=0,SMALL(A1:A10,2),MIN(A1:A10)) For the Maximum: =IF(MAX(A1:A10)=0,LARGE(A1:A10,2),MAX(A1:A10)) Hope this helps, Hutch "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
MAX won't be affected by the zero (unless you had negative numbers),
so you can do: =MAX(A1:A10) For the minimum, excluding zeros, you can use this array* formula: =MIN(IF(A1:A10<0,A1:A10,10E100) 10E100 is just a big number, and zeroes are replaced by this (in the formula, not in the cells) so that they have no effect. *An array formula must be committed using the key combination of CTRL- SHIFT-ENTER (CSE) instead of the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. You need to use CSE again if you subsequently edit/amend the formula. Hope this helps. Pete On Nov 10, 12:03*am, dal_101 wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: *A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? * Please help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
Hi,
=MIN(IF(A1:A10<0,A1:A10,"")) and =MAX(IF(A1:A10<0,A1:A10,"")) Both of these formulas are arrays - to enter them you press Shift+Ctrl+Enter rather than enter. If this helps please click the Yes button -- Thanks, Shane Devenshire "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90.
Not sure how much "robustness" you want/need but try these. Assuming there are no negative numbers: For the MIN: =SMALL(A1:A10,COUNTIF(A1:A10,0)+1) For the MAX: =MAX(A1:A10) -- Biff Microsoft Excel MVP "dal_101" wrote in message ... I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
Thank You. Could you explain to me what that formula means? I know the
=MIN(If(the range is less than or greater than 0, true will return the range minimum, value_if_false???) Why would you use that ("")? Because there isnt going to be a false agrument? Im just wondering for future reference, in other basic formulas could I use the same things to not include zero values and using "" to not have anything returned? Thank you sooooo much btw!!!! "ShaneDevenshire" wrote: Hi, =MIN(IF(A1:A10<0,A1:A10,"")) and =MAX(IF(A1:A10<0,A1:A10,"")) Both of these formulas are arrays - to enter them you press Shift+Ctrl+Enter rather than enter. If this helps please click the Yes button -- Thanks, Shane Devenshire "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
Why would you use that ("")?
You don't need it. If the logical_test is FALSE then it evaluates as "" which is an empty *TEXT* string and the MIN/MAX functions will ignore text. You can leave it out and you will still get the same result: =MIN(IF(A1:A10<0,A1:A10)) In this version if the logical_test is FALSE and no value_if_false argument is included then it defaults to FALSE and the MIN/MAX functions will also ignore that. -- Biff Microsoft Excel MVP "dal_101" wrote in message ... Thank You. Could you explain to me what that formula means? I know the =MIN(If(the range is less than or greater than 0, true will return the range minimum, value_if_false???) Why would you use that ("")? Because there isnt going to be a false agrument? Im just wondering for future reference, in other basic formulas could I use the same things to not include zero values and using "" to not have anything returned? Thank you sooooo much btw!!!! "ShaneDevenshire" wrote: Hi, =MIN(IF(A1:A10<0,A1:A10,"")) and =MAX(IF(A1:A10<0,A1:A10,"")) Both of these formulas are arrays - to enter them you press Shift+Ctrl+Enter rather than enter. If this helps please click the Yes button -- Thanks, Shane Devenshire "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
No good if there's more than one zero. Shane's formula would be better.
-- David Biddulph "Tom Hutchins" wrote in message ... Here's one way... For the Minimum: =IF(MIN(A1:A10)=0,SMALL(A1:A10,2),MIN(A1:A10)) For the Maximum: =IF(MAX(A1:A10)=0,LARGE(A1:A10,2),MAX(A1:A10)) Hope this helps, Hutch "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
Hi Biff,
I've a question.. My MIN formula is: =MIN((IF(AF5="N",AB5)),(IF(AO5="N",AK5)),(IF(AX5=" N",AT5)),(IF(BG5="N",BC5)),(IF(BP5="N",BL5))) When i click on evaluate formula, the result is: =MIN($AB$5,$AK$5,FALSE,FALSE,FALSE) -- Where my cell $AB$5 = 1.17 & cell $AK$5 is 0.847, i get a result of 0. Would you be able to enlighten me why this is so? I thought text are not considered in MIN formula? Frenz "T. Valko" wrote: Why would you use that ("")? You don't need it. If the logical_test is FALSE then it evaluates as "" which is an empty *TEXT* string and the MIN/MAX functions will ignore text. You can leave it out and you will still get the same result: =MIN(IF(A1:A10<0,A1:A10)) In this version if the logical_test is FALSE and no value_if_false argument is included then it defaults to FALSE and the MIN/MAX functions will also ignore that. -- Biff Microsoft Excel MVP "dal_101" wrote in message ... Thank You. Could you explain to me what that formula means? I know the =MIN(If(the range is less than or greater than 0, true will return the range minimum, value_if_false???) Why would you use that ("")? Because there isnt going to be a false agrument? Im just wondering for future reference, in other basic formulas could I use the same things to not include zero values and using "" to not have anything returned? Thank you sooooo much btw!!!! "ShaneDevenshire" wrote: Hi, =MIN(IF(A1:A10<0,A1:A10,"")) and =MAX(IF(A1:A10<0,A1:A10,"")) Both of these formulas are arrays - to enter them you press Shift+Ctrl+Enter rather than enter. If this helps please click the Yes button -- Thanks, Shane Devenshire "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting the min and max of a list of numbers
=MIN($AB$5,$AK$5,FALSE,FALSE,FALSE)
MIN/MAX only ignore TEXT and logicals (TRUE, FALSE) when they are part of an array or a range reference. Since you're referencing individual cells Excel evaluates the result of the IF, logical FALSE, as 0 and that is the result you're getting. Try this simple test. Enter these formulas in some cells and you'll see what I mean: =MIN(10,"x") =MIN(10,FALSE) =MIN(10,TRUE) Now, if we force the text and logicals into arrays then we'll get the expected results: =MIN(10,{"x"}) =MIN(10,{FALSE}) =MIN(10,{TRUE}) So, you need to do the same thing with your formula: =MIN(IF(AF5="N",AB5,{""}),IF(AO5="N",AK5,{""}),IF( AX5="N",AT5,{""}),IF(BG5="N",BC5,{""}),IF(BP5="N", BL5,{""})) -- Biff Microsoft Excel MVP "Frenz" wrote in message ... Hi Biff, I've a question.. My MIN formula is: =MIN((IF(AF5="N",AB5)),(IF(AO5="N",AK5)),(IF(AX5=" N",AT5)),(IF(BG5="N",BC5)),(IF(BP5="N",BL5))) When i click on evaluate formula, the result is: =MIN($AB$5,$AK$5,FALSE,FALSE,FALSE) -- Where my cell $AB$5 = 1.17 & cell $AK$5 is 0.847, i get a result of 0. Would you be able to enlighten me why this is so? I thought text are not considered in MIN formula? Frenz "T. Valko" wrote: Why would you use that ("")? You don't need it. If the logical_test is FALSE then it evaluates as "" which is an empty *TEXT* string and the MIN/MAX functions will ignore text. You can leave it out and you will still get the same result: =MIN(IF(A1:A10<0,A1:A10)) In this version if the logical_test is FALSE and no value_if_false argument is included then it defaults to FALSE and the MIN/MAX functions will also ignore that. -- Biff Microsoft Excel MVP "dal_101" wrote in message ... Thank You. Could you explain to me what that formula means? I know the =MIN(If(the range is less than or greater than 0, true will return the range minimum, value_if_false???) Why would you use that ("")? Because there isnt going to be a false agrument? Im just wondering for future reference, in other basic formulas could I use the same things to not include zero values and using "" to not have anything returned? Thank you sooooo much btw!!!! "ShaneDevenshire" wrote: Hi, =MIN(IF(A1:A10<0,A1:A10,"")) and =MAX(IF(A1:A10<0,A1:A10,"")) Both of these formulas are arrays - to enter them you press Shift+Ctrl+Enter rather than enter. If this helps please click the Yes button -- Thanks, Shane Devenshire "dal_101" wrote: I need to get the maximum and minimum values in a list of numbers, some of the cells have 0 (suppressed), and I do not want to include those, and not get the value output 0??? I can not delete the zero values either I just dont want to include them. in other words....ex: A1:A10 is 10,20,30,0,40,50,60,70,80,90. How do I get the min and max of all the numbers except the zero? Please help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I add +ve numbers only in a list of +ve and -ve numbers | Excel Discussion (Misc queries) | |||
compare a list of numbers | Excel Worksheet Functions | |||
Count numbers in list | Excel Worksheet Functions | |||
change a vertical list of numbers to horizontal list from 1 cell | Excel Discussion (Misc queries) | |||
how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) |