Including 0 in formula (#DIV/0!)

[UMOP]

Hi all,

I am trying to create a marksbook that will ignore blank cells and calculate a percentage based on the amount of tasks completed.
So far I have managed to get it all working EXCEPT if I enter 0 as a value.

For example, suppose there are two tasks, both of equal value. If a person was to get 100% for the first task, and 0 for the second task, you would expect the total to be 50%. I cannot get my formula to recognise that a 0 has been entered. Can anyone please help?

(Cells F5:T5 are what the task is out of. Cells F6:T6 is the weighting. I want to be able to enter a 0 in cells F8:T8 to include it in the final percentage.)

CODE:

=IF(AND(ISBLANK(F8),ISBLANK(G8),ISBLANK(H8),ISBLANK(I8),ISBLANK(J8),ISBLANK(K8),ISBLANK(L8),ISBLANK(M8),ISBLANK(N8),ISBLANK(O8),ISBLANK(P8),ISBLANK(Q8),ISBLANK(R8),ISBLANK(S8),ISBLANK(T8)),"",(SUM((IF(LEN(TRIM(F8))=0,,SUM((F8/$F$5)*$F$6/100))+IF(LEN(TRIM(G8))=0,,SUM((G8/$G$5)*$G$6/100))+IF(LEN(TRIM(H8))=0,,SUM((H8/$H$5)*$H$6/100))+IF(LEN(TRIM(I8))=0,,SUM((I8/$I$5)*$I$6/100))+IF(LEN(TRIM(J8))=0,,IFSUM((J8/$J$5)*$J$6/100))+IF(LEN(TRIM(K8))=0,,SUM((K8/$K$5)*$K$6/100))+IF(LEN(TRIM(L8))=0,,SUM((L8/$L$5)*$L$6/100))+IF(LEN(TRIM(M8))=0,,SUM((M8/$M$5)*$M$6/100))+IF(LEN(TRIM(N8))=0,,SUM((N8/$N$5)*$N$6/100))+IF(LEN(TRIM(O8))=0,,SUM((O8/$O$5)*$O$6/100))+IF(LEN(TRIM(P8))=0,,SUM((P8/$P$5)*$P$6/100))+IF(LEN(TRIM(Q8))=0,,SUM((Q8/$Q$5)*$Q$6/100))+IF(LEN(TRIM(R8))=0,,SUM((R8/$R$5)*$R$6/100))+IF(LEN(TRIM(S8))=0,,SUM((S8/$S$5)*$S$6/100))+IF(LEN(TRIM(T8))=0,,SUM((T8/$T$5)*$T$6/100)))*100/(SUMIF(F8:T8,">0",$F$6:$T$6)))))

Link for excel doc --> http://www.4shared.com/file/21197544...ARKSBook1.html

[pike]

Hi UMOP
can you attach a zipped workbook?

[UMOP]

Yep. Done. Thanks for any help you can provide Pike.

[teylyn]

UMOP,

please don't quote whole posts. It's just clutter. In fact, only quote when you are referring to something particular that is not in the previous post, and then only quote the pertinent lines.

Thank you.

[pike]

Hi UMOP
try .. in U9

Please Login or Register  to view this content.

[Cheeky Charlie]

95% reduction in formula length... that is hot

[UMOP]

Thank you for all your help, I have solved my problem with it not accepting a 0 as an input.

Cheers to you all.

[pike]

did you try the formula ? it does it all