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.

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

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?

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.

5. Re: IF formula help on calculating a score

Percentage is

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

Display as a percentage

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)

7. Re: IF formula help on calculating a score

=SUMIF(K3:K10,"<>NA",L3:L10)/SUMIF(K3:K10,"<>NA",C3:CL10)

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1