Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
HELP! I need to average a colum of numbers omiting zero values, I found the
formula in the help menu and copied it using my cell ranges when I try to enter formula it returns an error message with the false value highlighted this is my formula: =AVERAGE(IF(d5:d32<0,d5:d32,**)) it will not accept the ** as a false value. If I can get past this I also need to find a min. value with zero values omited. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
Try this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter], instead of
just [enter]): =AVERAGE(IF(D5:D32<0,D5:D32)) Does that help? ---------------------- Regards, Ron Microsoft MVP (Excel) "rustygun3" wrote in message ... HELP! I need to average a colum of numbers omiting zero values, I found the formula in the help menu and copied it using my cell ranges when I try to enter formula it returns an error message with the false value highlighted this is my formula: =AVERAGE(IF(d5:d32<0,d5:d32,**)) it will not accept the ** as a false value. If I can get past this I also need to find a min. value with zero values omited. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging
On Wed, 6 Jun 2007, in microsoft.public.excel.worksheet.functions,
Ron Coderre said: "rustygun3" wrote HELP! I need to average a colum of numbers omiting zero values, I found the formula in the help menu and copied it using my cell ranges when I try to enter formula it returns an error message with the false value highlighted this is my formula: AVERAGE(IF(d5:d32<0,d5:d32,**)) it will not accept the ** as a false value. As Ron says, you don't need to enter special characters in the "if false" section: it's optional. Just leave it out of the expression completely (leave the comma out as well), and the expression evaluates as FALSE if the condition is false. If you block out a range with this array function: =IF(D5:D32<0,D5:D32) you'll see the FALSE values appear. And since AVERAGE ignores FALSE in an array, you're all set. Try this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter], instead of just [enter]): =AVERAGE(IF(D5:D32<0,D5:D32)) (if only charts ignored FALSE values as well! sadly, charts plot FALSE as if it were 0.00) Also, the answer to the second half of your question is =MIN(IF(D5:D32<0,D5:D32)) If I can get past this I also need to find a min. value with zero values omited. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averaging | Excel Discussion (Misc queries) | |||
Averaging | Excel Discussion (Misc queries) | |||
averaging & ??? | Excel Worksheet Functions | |||
Help with averaging... | Excel Discussion (Misc queries) | |||
More Averaging | Excel Worksheet Functions |