Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
I have a formula that takes a array that includes number grades, drops the
lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
One way:
=IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2 }))) Note, with the formula you're using now, someone who got two or more of the same low grades would have them BOTH/ALL dropped. In article , Naomi wrote: I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Hi Naomi
the formula is an array formula, so needs to be entered or amended using Control+Shift+Enter (CSE) When you use CSE, Excel encloses the formula within curly braces { }. Do not type them yourself {=AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13))} -- Regards Roger Govier "Naomi" wrote in message ... I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
You could trap with IF(ISERROR, indicatively like this, array-entered:
=IF(ISERROR(Average(...)),"",Average(...)) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Naomi" wrote: I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Note, with the formula you're using now, someone who got two or more of
the same low grades would have them BOTH/ALL dropped. Very good point, John. -- Regards Roger Govier "JE McGimpsey" wrote in message ... One way: =IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2 }))) Note, with the formula you're using now, someone who got two or more of the same low grades would have them BOTH/ALL dropped. In article , Naomi wrote: I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
What if there is more than 1 instance of the minimum grade? Should each
minimum grade be excluded? -- Biff Microsoft Excel MVP "Naomi" wrote in message ... I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
No even if there is a duplicate minimum number grade only one needs to be
dropped "T. Valko" wrote: What if there is more than 1 instance of the minimum grade? Should each minimum grade be excluded? -- Biff Microsoft Excel MVP "Naomi" wrote in message ... I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Works Perfect...Thanks So Much!!! :)
"JE McGimpsey" wrote: One way: =IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2 }))) Note, with the formula you're using now, someone who got two or more of the same low grades would have them BOTH/ALL dropped. In article , Naomi wrote: I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Try this:
=SUM(C13:E13,-MIN(C13:E13))/MAX(COUNT(C13:E13)-1,1) If all the cells are empty it will return 0. -- Biff Microsoft Excel MVP "Naomi" wrote in message ... No even if there is a duplicate minimum number grade only one needs to be dropped "T. Valko" wrote: What if there is more than 1 instance of the minimum grade? Should each minimum grade be excluded? -- Biff Microsoft Excel MVP "Naomi" wrote in message ... I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
I have one other question. The person im making this for has added columns
that might have zeros in them that will need to be included in this formula as well. How would i modify this formula to not only include the lowest number and zeros as well Thanks in advance for the help :) "JE McGimpsey" wrote: One way: =IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2 }))) Note, with the formula you're using now, someone who got two or more of the same low grades would have them BOTH/ALL dropped. In article , Naomi wrote: I have a formula that takes a array that includes number grades, drops the lowest grade and averages them. If there is no data in cell yet i get a #DIV/0! error is there a way to copy this formula to following cells without data in them yet and not get this error. The formula Im using is: =AVERAGE(IF(C13:E13<MIN(C13:E13),C13:E13)) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
One way:
=IF(COUNTIF(C13:G13,"0")<5,"",AVERAGE(LARGE(C13:G 13,{1,2,3,4}))) Adjust the '5' and '{1,2,3,4}' to suit the number of added columns. In article , Naomi wrote: I have one other question. The person im making this for has added columns that might have zeros in them that will need to be included in this formula as well. How would i modify this formula to not only include the lowest number and zeros as well "JE McGimpsey" wrote: One way: =IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2 }))) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
I adjusted the formula..and for reference there is 11 columns to average
therefore the formula i used was =IF(COUNTIF(C11:M11,"0")<11,"",AVERAGE(LARGE(C11: M11,{1,2,3,4,5,6,7,8,9,10}))) The formula generated by the cell that i placed the formula in is blank no zero or error message!. Any ideas?? Thanks Again!!! "JE McGimpsey" wrote: One way: =IF(COUNTIF(C13:G13,"0")<5,"",AVERAGE(LARGE(C13:G 13,{1,2,3,4}))) Adjust the '5' and '{1,2,3,4}' to suit the number of added columns. In article , Naomi wrote: I have one other question. The person im making this for has added columns that might have zeros in them that will need to be included in this formula as well. How would i modify this formula to not only include the lowest number and zeros as well "JE McGimpsey" wrote: One way: =IF(COUNT(C13:E13)<3,"",AVERAGE(LARGE(C13:E13,{1,2 }))) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Your formula works fine for me, and you adjusted it appropriately.
CHeck to see that the values are actually entered as values, not Text. In article , Naomi wrote: I adjusted the formula..and for reference there is 11 columns to average therefore the formula i used was =IF(COUNTIF(C11:M11,"0")<11,"",AVERAGE(LARGE(C11: M11,{1,2,3,4,5,6,7,8,9,10})) ) The formula generated by the cell that i placed the formula in is blank no zero or error message!. Any ideas?? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
OMG...what am i doing wrong all the cells are formatted correctly for numbers
and two decimal places. I know its something simple that i am missing cell c11 = 89.00 d11 = 91.00 e11 = 82.00 f11 = 92.00 g11 = 78.50 h11 = 82.00 i11 = 92.00 j11 = 0.00 k11 = 0.00 l11 = 85.50 m11 = 82.30 n11 = Should be the average of all numbers dropping the lowest number and all zeros..... Cell is blank and it is formatted for numbers two decimal places as well. "JE McGimpsey" wrote: Your formula works fine for me, and you adjusted it appropriately. CHeck to see that the values are actually entered as values, not Text. In article , Naomi wrote: I adjusted the formula..and for reference there is 11 columns to average therefore the formula i used was =IF(COUNTIF(C11:M11,"0")<11,"",AVERAGE(LARGE(C11: M11,{1,2,3,4,5,6,7,8,9,10})) ) The formula generated by the cell that i placed the formula in is blank no zero or error message!. Any ideas?? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Ah - Your first problem statement didn't want a calculation until you
had all 'valid' values, dropping the lowest. The added criterion was that 0's needed to be ignored - my assumption was that this made them non-valid values, and therefore there shouldn't be a calculation. Given that you want the zero's considered valid values, but ignored, and the calculation to take place when all the cells have numeric values (including zero) then one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF(COUNT(C11:M11)<11,"",LARGE(C11:M11, ROW(INDIRECT("1:"&COUNTIF(C11:M11,"0")-1))))) In article , Naomi wrote: OMG...what am i doing wrong all the cells are formatted correctly for numbers and two decimal places. I know its something simple that i am missing cell c11 = 89.00 d11 = 91.00 e11 = 82.00 f11 = 92.00 g11 = 78.50 h11 = 82.00 i11 = 92.00 j11 = 0.00 k11 = 0.00 l11 = 85.50 m11 = 82.30 n11 = Should be the average of all numbers dropping the lowest number and all zeros..... Cell is blank and it is formatted for numbers two decimal places as well. "JE McGimpsey" wrote: Your formula works fine for me, and you adjusted it appropriately. CHeck to see that the values are actually entered as values, not Text. In article , Naomi wrote: I adjusted the formula..and for reference there is 11 columns to average therefore the formula i used was =IF(COUNTIF(C11:M11,"0")<11,"",AVERAGE(LARGE(C11: M11,{1,2,3,4,5,6,7,8,9,1 0})) ) The formula generated by the cell that i placed the formula in is blank no zero or error message!. Any ideas?? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#DIV/0! error using Avgerage
Your a Genuis!!! :) Works Great! Thanks so much for the help. "JE McGimpsey" wrote: Ah - Your first problem statement didn't want a calculation until you had all 'valid' values, dropping the lowest. The added criterion was that 0's needed to be ignored - my assumption was that this made them non-valid values, and therefore there shouldn't be a calculation. Given that you want the zero's considered valid values, but ignored, and the calculation to take place when all the cells have numeric values (including zero) then one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =AVERAGE(IF(COUNT(C11:M11)<11,"",LARGE(C11:M11, ROW(INDIRECT("1:"&COUNTIF(C11:M11,"0")-1))))) In article , Naomi wrote: OMG...what am i doing wrong all the cells are formatted correctly for numbers and two decimal places. I know its something simple that i am missing cell c11 = 89.00 d11 = 91.00 e11 = 82.00 f11 = 92.00 g11 = 78.50 h11 = 82.00 i11 = 92.00 j11 = 0.00 k11 = 0.00 l11 = 85.50 m11 = 82.30 n11 = Should be the average of all numbers dropping the lowest number and all zeros..... Cell is blank and it is formatted for numbers two decimal places as well. "JE McGimpsey" wrote: Your formula works fine for me, and you adjusted it appropriately. CHeck to see that the values are actually entered as values, not Text. In article , Naomi wrote: I adjusted the formula..and for reference there is 11 columns to average therefore the formula i used was =IF(COUNTIF(C11:M11,"0")<11,"",AVERAGE(LARGE(C11: M11,{1,2,3,4,5,6,7,8,9,1 0})) ) The formula generated by the cell that i placed the formula in is blank no zero or error message!. Any ideas?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |