IFS formula that allows whole and decimals

[Biggs61us]

Writing a points system for work and wanting to use a formula to calculate the amount of incentive points handed out to students daily. If they score a daily average (column O) 1.0 - 1.9 they receive a 0, 2.0-2.9 a 1, 3.0-3.9 a 4, 4.0 they get 10 points. Every formula I have tried/found won't hold true for all the numbers. On the attached sheet column P is the one that needs the formula. Thanks in advance.

[MrShorty]

Are you required to use an IFS() function? This seems to me like it would be better handled with one of Excel's lookup functions (such as VLOOKUP()) with an approximate match option. For example:

1) Create the lookup table. In AA7 put 1, AA8 put 2, AA9 put 3, AA10 put 4. In AB7 put 0, in AB8 put 1, and so on.
2) In P7 (I assume this is where you want the result) enter =VLOOKUP(O7,$AA$7:$AB$10,2,TRUE) and copy/paste/fill down.

If I understand what you are trying to do, that should work.

[alansidman]

Build a table as follows

Excel 2016 (Windows) 32 bit

	U	V
7	1	0
8	2	1
9	3	4
10	4	10

Sheet: Sunday

then in P7 type =VLOOKUP(AVERAGE(H7,N7,O7),U7:V10,2) and copy down.

[Gregb11]

I agree with the other posts here, but if you wanted to use IFS, you could try this (Note, you don't say what it is if they score less than 1.0, but this answer assumes they'd still get a 0).

=IFS(O7<2,0,O7<3,1,O7<4,4,TRUE,10)

[bebo021999]

=lookup(o7,{1,2,3,4},{0,1,4,10})