Streamlining Formula

[Brutium]

Hello all,
I have this formula in a cell that adds-up entries in various cells (10 per row), and gives me a percentile.
Is there a way I could streamline it so I do nor have such a LOOOOONG formula?

=IFERROR((IF(ISBLANK(C9),0,C9/C$7)+IF(ISBLANK(D9),0,D9/D$7)+IF(ISBLANK(E9),0,E9/E$7)+IF(ISBLANK(F9),0,F9/F$7)+IF(ISBLANK(G9),0,G9/G$7)+IF(ISBLANK(H9),0,H9/H$7)+IF(ISBLANK(I9),0,I9/I$7)+IF(ISBLANK(J9),0,J9/J$7)+IF(ISBLANK(K9),0,K9/K$7)+IF(ISBLANK(L9),0,L9/L$7))/(IF(ISBLANK(C9),0,1)+IF(ISBLANK(D9),0,1)+IF(ISBLANK(E9),0,1)+IF(ISBLANK(F9),0,1)+IF(ISBLANK(G9),0,1)+IF(ISBLANK(H9),0,1)+IF(ISBLANK(I9),0,1)+IF(ISBLANK(J9),0,1)+IF(ISBLANK(K9),0,1)+IF(ISBLANK(L9),0,1)),"")

Any help would be greatly appreciated!!!!

 

[shg]

Maybe ...


=SUMPRODUCT(IF(C$7:L$7<>0, C9:L9/C$7:L$7)) / COUNT(C9:L9)

 

[krausr79]

Interesting problem. I still have no idea what you're doing.

dividing a blank by something will give you zero, so no ifblanks are needed for the top of the big divide. To divide each of the cells by their counterpart, use =SUM(C9:L9/C7:L7) and press ctrl-shift-enter because it is an array formula acting across many cell pairs.

On the bottom, you want 1's for things that exist and 0's if they don't, which is what the count function will do for us.
=count(C9:L9)

Then do the big divide:
=SUM(C9:L9/C7:L7)/COUNT(C9:L9) with ctrl-shift-enter. Once it knows it's an array formula, it will appear as:
{=SUM(C9:L9/C7:L7)/COUNT(C9:L9)}

 

[Brutium]

Thank you all....

SHG, Thank you , but your formula does not give me any values.

krausr79, what I am doing is calculate the average of marks that students get in various tests. On row 7 I have the "out of" marks and on row 9 I will have the mark that the students get on the individual test. What I am trying to accomplish is omit the cells that do not have any marks due, possibly, to a student who missed a test. I hope this gives you some more information on what I am trying to do....

Thank you