=IFS formula working in Google Sheets but not Excel?-CodePudding

=IFS(L83<49.9,"Referral",L83<60,"Pass",L83<70,"Merit",L83<100.1,"Distinction",L83<1000,"Over Assessed")

I have the above formula in one of my cells. It works perfectly fine in Google Sheets. However, once I donwload the sheet into Excel/LibreOffice Calc, the cell displays as #NAME?. If I then alter the formula by adding a space after one of the IF queries, eg. =IFS(L83<49.9,"Referral", L83<60,"Pass"... the formula then works again as intended and removes the error, even if I then delete the previously added space?

Can I use a different formula here that will be more reliable? Or am I doing something wrong that is causing this to happen every time?

Please help!

CodePudding user response：

So, even works based on percentile - taken as just numbers:

VLOOKUP(D2,$A$1:$B$6,2,1)

The vlookup works by not looking for an exact match, which you can have bby putting 0 instead of 1 as the 4th argument.