# IF formula help on calculating a score

1. ## IF formula help on calculating a score

Hi everyone,

I need some help on a particular column on L (see attached test excel file).

I've managed to get this far doing this but still need help on the last step.

I'm trying to create a sheet of questions and using a tick box method to calculate whether they passed the question or not, which will give a score. however if the question if not applicable, i would tick the N/A box as shown in the document however this shouldn't penalize the score the overall score.

for example, if theres 5 questions and only 4 of them apply which they all pass except 1 question that is non-applicable, the total score should still be 100% but as to rather 80%. can someone take a look at what exactly i did wrong or what is missing?

is it my formula for the total SUM wrong? or the IF? this is what i'm unsure of..

i hope someone can help me with this, thanks.

thanks.  Register To Reply

2. ## Re: IF formula help on calculating a score

This doesnt solve your problem but...

You dont need SUM(L11/C11)*1 in K11, and you dont need the *1
You can only SUM two or more numbers
L11/C11 is one number, youre not SUMming it with anything else
This should simply be L11/C11  Register To Reply

3. ## Re: IF formula help on calculating a score

hi, thank you for the suggestion

but that i still need help on this formula in regards of having N/A ticked whilst not to affect the scoring.

anyone have any suggestions? on how to fix that?  Register To Reply

4. ## Re: IF formula help on calculating a score

Formula:  `Please Login or Register  to view this content.`

or, replacing the L11 with the formula there:
Formula:  `Please Login or Register  to view this content.`

Edit:
On another look, I'm not sure which tick-box you want to check against. The formulae above assume it's the ones in column H. If it's the ones in column J instead, then replace \$G\$3:\$G\$10 above with \$I\$3:\$I\$10.  Register To Reply

5. ## Re: IF formula help on calculating a score

Percentage is

=COUNTIF(G3:G10,TRUE)/COUNTIF(I3:I10,FALSE)

Display as a percentage  Register To Reply

6. ## Re: IF formula help on calculating a score

Change L3 to:

=IF(AND(G3=TRUE,I3=FALSE),C3,0)

copied down and K1 to:

=L11/SUMIF(I:I,FALSE,C:C)  Register To Reply

7. ## Re: IF formula help on calculating a score =SUMIF(K3:K10,"<>NA",L3:L10)/SUMIF(K3:K10,"<>NA",C3:CL10)  Register To Reply

8. ## Re: IF formula help on calculating a score Originally Posted by Special-K Percentage is

=COUNTIF(G3:G10,TRUE)/COUNTIF(I3:I10,FALSE)

Display as a percentage Originally Posted by Glenn Kennedy Change L3 to:

=IF(AND(G3=TRUE,I3=FALSE),C3,0)

copied down and K1 to:

=L11/SUMIF(I:I,FALSE,C:C)
these are both perfect and works fine. i wasn't sure what was the best way of doing it but thank you so much both.  Register To Reply