Grading System

%3CLINGO-SUB%20id%3D%22lingo-sub-2159169%22%20slang%3D%22en-US%22%3EGrading%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2159169%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20doing%20a%20grading%20system%20for%20my%20company%20and%20need%20assistance%20with%20the%20last%20formula%20because%202%20of%20the%20other%20formulas%20have%20%22n%2Fa%22%20in%20them.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2159169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2159201%22%20slang%3D%22en-US%22%3ERe%3A%20Grading%20System%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2159201%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F976068%22%20target%3D%22_blank%22%3E%40MS_64804%3C%2FA%3E%26nbsp%3BThat's%20a%20bit%20abstract.%20Would%20be%20helpful%20if%20you%20could%20show%20the%20formulae.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, 

 

I am doing a grading system for my company and need assistance with the last formula because 2 of the other formulas have "n/a" in them. 

7 Replies

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

The data is all the way at the end, that's what I was telling you. You can put the data in the table and when you scroll all the way to the end you will see the sum.

 

Scroll all the way to the end to column BI , the formulas are in there. 

It's very hard to explain, can I call you or you call me?