Hi. Can somebody help me with the error if getting in excel?

Rica Opon 20 Reputation points
2024-03-26T02:01:40.1033333+00:00

Hi. Can somebody help me with the error if getting in excel?

I am trying to compute an overall scorecard for new hires, there are 8 metrics to be considered so Im trying to get the average of those but im getting a #DIV/0 error.

Each metrics has an IF function since each of them has different score level (1-5) please see attached for each.

All I want to do is get the overall scorecard using an average..

Please help


Screenshot_20240326_095910

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,450 questions
{count} votes

Accepted answer
  1. Emi Zhang-MSFT 21,626 Reputation points Microsoft Vendor
    2024-03-26T07:57:57.7466667+00:00

    Hi,

    Please remove the symbol "" from the IF formula:

    =IF(R4<=74.99,1,IF(R4<=83.32,2,IF(R4<=91.66,3,IF(R4<=99.99,4,IF(R4>=100,5)))))

    Then check if it works.


    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Barry Schwarz 1,946 Reputation points
    2024-03-26T17:30:05.5666667+00:00

    The error is in your IF formulas. You have quoted the values. This makes them text values, not numerical values. An additional clue is that the values are displayed as centered and not right justified.

    Removing the quotes form all the values should solve your problem.

    Since you are going to update these formulas, may I suggest that you replace the 5 nested IF formulas with a single IFS formula with 5 pairs of arguments. Furthermore, most decimal values cannot be represented exactly in binary. It appears that your test N4<=74.99 is meant to catch N4<75. But the value that displays as 74.99 might actually be 74.9900001 and it would fail your test. Accepting both of these suggestions results in a formula like =IFS(N4<75,1,N4<83.33,2,N4<91.67,3,N4<100,4,TRUE,5).

    1 person found this answer helpful.
    0 comments No comments