#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 })))

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 })))

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

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

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

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