What is the right Excel formula to write an “IF” statement?

null May 4, 2011
Pinterest Stumbleupon Whatsapp
Ads by Google

For quality feedback to employees, we use 3 classifications for performance:

Y (with a score from 1-5)
N (equals 0 score)
N/A (equals 0 score; this question’s 5 points are removed from total points available

My question is how to write the “IF” statement for the total available points.

Here’s what I have but it isn’t working for all of it:

=IF(C8>=0,”5″,IF(C8=””&D8=”n”,”5″))

C8 = Yes cell with score 1-5
D8 = No cell with score N
E8 = N/A cell with N/A as the value

Basically, if they performed well, they received a score of 1-5 out of the “total points” of 5 for the question. If they failed the task, they get a score of “N” out of a “total points” of 5 for the question. If the task was not applicable, N/A is scored out of a “total points” of 0 for the question.

Any assistance is greatly appreciated.

  1. Andrei
    May 11, 2011 at 4:21 pm

    Please upload somewhere youe excel sheet if the above did not work so that we can look at it. I am tempted to advise to skip if functions alltogether. In theory you can create an unique id for each scenario you need and then look up the corresponding information. But, like most of the respondents, i do not really understand the question, so i prefer to stop here rather than continue with confusing detail.

  2. Bruce Epper
    May 11, 2011 at 8:50 am

    The first IF segment of your forumula will always return 5 in your equation, the remainder will ALWAYS be ignored since Excel will evaluate an empty cell as 0 in a numeric comparison, so the first comparison is always True and will always return "5". So this needs to be changed to: IF(C8>0,C8,..

    Now that the C8 column is dealt with properly so your equation returns the value that is in C8 if anything is there, we can look at the No column. If we made it this far, we can ignore C8, so your reference to it in the second IF statement is redundant. We already know that it is blank. We want to return 0 if there is a value of 'n' or "N" in it (making it case-insensitive). We do that like this: IF(UPPER(D8)="N",0,...). This portion will return a value of 0 if the No column contains either an upper- or lower-case 'n.'

    The False portion of the previous test needs to be filled with our test for the N/A column. This is done in a similar manner: IF(UPPER(E8)="N/A",0.

    Putting it all together, we end up with this:
    IF(C8>0,C8,IF(UPPER(D8)="N",0,IF(UPPER(E8)="N/A",0)))

    I believe this will give you the results you are looking for, but there is an even easier way to accomplish your task here. Instead of using 3 columns of data, you can use just one column to indicate the result of each task. You change your scoring so that instead of using a No column, you just enter 0 in the Results column. For a passing score, you enter 1 thru 5 as you did with your Yes column. If a task does not apply, you enter 'N'. Then you can highlight the range in the column that contains the task results and name it 'TaskResults' (just to make things easier to follow).

    Now, you can get their total score with this: SUM(TaskResults)
    You can get the total count of tasks with this: COUNTA(TaskResults)
    You can get the number of tasks that don't apply with: COUNTIF(TaskResults,"N")
    Finally, you can get a percentage score with the following:
    100*SUM(TaskResults)/((COUNTA(TaskResults)-COUNTIF(TaskResults,"N"))*5)

    Hope this helps.

    • Aibek
      May 11, 2011 at 10:35 am

      thanks for the input!

  3. KatalinM
    May 11, 2011 at 3:02 am

    I don't understand completely your question either, but I think this is what you are looking for if the answer is in C8:

    =IF(C8="N/A",0, IF(C8="N",5,IF(C8>0,5)))

    if N/A means empty cell by you, then you should have it like this:

    =IF(C8="",0, IF(C8="N",5,IF(C8>0,5)))

  4. Ben
    May 5, 2011 at 8:01 am

    I don't think I quite understand your question. You say you would like an IF statement for the total number of available points for that question.

    It appears from your description that the available points are always 5 unless E8 = "N/A", if this is the case I would use something like:

    IF(E8="N/A",0,5)

    I have assumed that in your example row 8 represents one question and thus only one cell of C8, D8, and E8 can contain a value.