+ Reply to Thread
Results 1 to 8 of 8

IF formula help on calculating a score

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    58

    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.
    Attached Files Attached Files
    Last edited by liqt; 02-22-2018 at 07:14 AM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    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
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    58

    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. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,387

    Re: IF formula help on calculating a score

    Since your column G is linked to your tick boxes, use this in K11:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or, replacing the L11 with the formula there:
    Formula: copy to clipboard
    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.
    Last edited by Aardigspook; 02-22-2018 at 09:25 AM.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: IF formula help on calculating a score

    Percentage is

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

    Display as a percentage

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    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)
    Attached Files Attached Files
    Glenn



  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,892

    Re: IF formula help on calculating a score



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

  8. #8
    Registered User
    Join Date
    11-07-2012
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: IF formula help on calculating a score

    Quote Originally Posted by Special-K View Post
    Percentage is

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

    Display as a percentage

    Quote Originally Posted by Glenn Kennedy View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Median Absolute Deviation and Modified Z-score
    By hodgsona in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2014, 01:20 PM
  2. Calculating Supplier Performance Score
    By metborn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2013, 04:56 AM
  3. [SOLVED] Calculating Position Based on Score
    By shanipk82 in forum Excel General
    Replies: 5
    Last Post: 03-28-2012, 11:31 AM
  4. Replies: 2
    Last Post: 03-20-2012, 06:30 PM
  5. Replies: 4
    Last Post: 01-28-2010, 01:30 PM
  6. formula to work out score based on score system
    By Nathaniel82 in forum Excel General
    Replies: 5
    Last Post: 08-10-2009, 11:25 AM
  7. Calculating score based on criteria
    By Kumara_faith in forum Excel General
    Replies: 6
    Last Post: 03-16-2009, 08:36 PM
  8. Calculating the closest score
    By AussieM8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2007, 08:21 PM

Bookmarks

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