Error in AVERAGE(IF...) formula; involves Zero Value and Blank Cells

[Arnold]

Hi there,
I am getting an error in this formula:
=AVERAGE(IF(($Y13:AH13<>0)*($Y$10:AH$10<>0),$Y13:AH13/$Y$10:AH
$10))*100

Excel states that a value used in the formula is of the wrong data
type, and the calculation steps underlines the first ($Y13:AH13 and
results in #Value!<>0.

The long-hand way of typing it is (up to column AG):
=(((Y13/Y$10)+(Z13/Z$10)+(AA13/AA$10)+(AB13/AB$10)+(AC13/AC$10)+(AD13/
AD$10)+(AE13/AE$10)+(AF13/AF$10)+(AG13/AG$10))*100)/
COUNT(Y13,Z13,AA13,AB13,AC13,AD13,AE13,AF13,AG13)

The data values, which are student scores on assignments, are
currently entered from Y13 to AH13, but there are cells that are blank
(students who didn't have to do the assignment). Also, there are
cells that may contain 0 (students who failed).

The max point values for the assignments are stored in the range Y10
to AH10. The point values are not the same across assignments, some
are 5 points, others can be 50 points.

I've looked at posts on averaging and arrays, and cannot seem to
correct this error. Any help would be greatly appreciated.

Sincerely,
Arnold

 

[Bernard Liengme]

If I copy your formula to an Excel cell and press ENTER I get a #VALUE!
error because the formula is clearly an array formula. But when I commit the
formula with CTRL+SHIFT+ENTER I get a #DIV/0! because I have no data in the
large range of cell.
With values in the cells I get a numeric answer.
best wishes

 

[Arnold]

Thanks Bernard,

I had tried (after reading past posts) pressing ctrl + shift + enter
to make it an array and I guess I just goofed. The array works now.

However, I just realized that the average formula is wrong--the result
does NOT equal the long-hand averaging formula.

I'm trying to come up with an automated way of updating the formula
when new assignments (columns) are added. When a new assignment
column is added, I'd like Excel to take the student's score in row 13
and divide it by the points possible in row 10. For instance, the
formula should include a new assignment put in column AH:

=(((Y13/Y$10)+(Z13/Z$10)+(AA13/AA$10)+(AB13/AB$10)+(AC13/AC$10)+(AD13/
AD$10)+(AE13/AE$10)+(AF13/AF$10)+(AG13/AG$10) + (AH13/AH) )*100)/
COUNT(Y13,Z13,AA13,AB13,AC13,AD13,AE13,AF13,AG13 , AH13 )

I don't want to have to manually enter the +(AH13/AH$10) in the
numerator and ,AH13 in the denominator.

Thanks

 

[Bob Phillips]

Sorry Arnold, it should have been

=AVERAGE(IF(($Y13:AI13<>0)*($Y$10:AI$10<>0),$Y13:AI13/$Y$10:AI$10))*100

still array-entered, where you insert new columns before ciolumn AI

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ragdyer]

And the reason your array formula doesn't match your "long" formula, is that
you're keying in 0's where a student failed, but your array formula is *not*
calculating those cells.

Try this addendum to Bob's *array* formula:

=AVERAGE(IF(($Y13:AI13<>"")*($Y$10:AI$10<>0),$Y13:AI13/$Y$10:AI$10))*100

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

 

[Arnold]

A big THANKS to Bernard, Bob, and RD. I greatly appreciate each of
your responses. Works great.

 

[Ragdyer]

And we appreciate the feed-back.