Forum Discussion
Grading System
MS_64804 That's a bit abstract. Would be helpful if you could show the formulae.
Riny_van_Eekelen , you are right. It's a bit complicated. So it's hard for me to explain.
But the company is a delivery company and the spreedsheet is to grade my drivers.
So I have a column for driver, driver number, stops, miles, service, complaints, disputes/misdeliveries, pickup/compliance, safey and total.
So far, I have a formula for all except stops, miles and total. I would like to add all the formulas together in the total but 3 of my formulas have n/a in it. Like if a driver 1 complaint "NA" he total will then be "NA" .If the driver do not have any complaints then I would like to sum all the columns up in the total.
=IFS(Table1[@Complaints]>=1,NA(),Table1[@Complaints]="","") - THAT IS THE FORMULA FOR COMPLAINTS
The others are similar with NA.
MS_64804 So the problem is that you have several grades, some of which ar NA. Then you add them up and you get NA as well. True?
Why not give a score of 0 (zero) in stead of NA(). Alternatively, look into the AGGREGATE function, rather than the regular sum.
If this doesn't make sense at all, perhaps you can upload an example of your workbook with the formulae in it, indicating the problem. Just remove any private and confidential information.
Okay,
The spreasdsheet is attached, the table with the formulas are all the way to the end.
MS_64804 You added a table with just headers. No data to test the formulae you have columns BI to BQ.
Without really knowing what I'm looking at, my initial though is that a formula like below is unnecessarily complicated.
=IF(Table1[@Service]="","",IF(Table1[@Service]<99,0,IF(AND(Table1[@Service]>=99,Table1[@Service]<=99.3),1,IF(AND(Table1[@Service]>=99.31,Table1[@Service]<=99.49),2,IF(AND(Table1[@Service]>=99.5,Table1[@Service]<=99.69),3,IF(AND(Table1[@Service]>=99.7,Table1[@Service]<=99.8),4,IF(AND(Table1[@Service]>=99.81,Table1[@Service]<=100),5,"")))))))You need to provide som example data and explain the end result you are after.