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

Question

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

Answer 1

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.

Answer 2

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).