"The twists and turns" Excel skill hundred example 05. Numerical Calculation of ladder

First we look at the following table:

For example, this is the result of some students in a class, the school in order to allow students to study harder to give the credit reward mechanism:

1. If a subject obtains 100, 20 credits will be awarded;

2. If a subject obtains more than 90 points (including 90 points), 10 credits will be awarded;

3. If a subject obtains more than 80 points (including 80 points), 5 credits will be awarded;

Less than 4.80 points, no bonus credits.

How do I do a quick calculation in Excel? If it is for the whole school students, the amount of data is very large, if fast processing?

Let's use the formula to deal with it.

Let's judge the math score with If+and first:

Formula: =if (C2<80,0,if (and (c2>=80,c2<90), 5,if (and (c2>=90,c2<100), 10,if (c2=100,20)))

If you have multiple grades, add them together:

Formula: =if (C2<80,0,if (and (c2>=80,c2<90), 5,if (and (c2>=90,c2<100), 10,if (c2=100,20)))
+if (D2<80,0,if (and (d2>=80,d2<90), 5,if (and (d2>=90,d2<100), 10,if (d2=100,20)))
+if (E2<80,0,if (and (e2>=80,e2<90), 5,if (and (e2>=90,e2<100), 10,if (e2=100,20)))
+if (F2<80,0,if (and (f2>=80,f2<90), 5,if (and (f2>=90,f2<100), 10,if (f2=100,20)))
+if (G2<80,0,if (and (g2>=80,g2<90), 5,if (and (g2>=90,g2<100), 10,if (g2=100,20)))

We can quickly get the following results:

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

"The twists and turns" Excel skill hundred example 05. Numerical Calculation of ladder