Multiple IFS in google sheet giving Formula Parse Error

Question

I am trying to create an IELTS band calculator using Google Sheets. The conversions I am using are here in detail. https://ieltsliz.com/ielts-band-scores/

I want to enter the Listening raw score in Cell C2 and then have it populate the bands in H2 based on where it falls in the range. I am using IFS conditional to calculate the scores because there are some values that fall within a range.

However, I get the following error

Error
Formula parse error.

The IFS statement is below. I am not sure what I am doing wrong

=ifs(AND(C2>10, C2<=12), "4.0" , 
AND(C2>12,C2<=15), "4.5", 
AND(C2>15 C2<=17), "5.0" , 
AND(C2>17,C2<=22), "5.5", 
AND(C2>22,C2<=25), "6.0", 
AND(C2>25,C2<=29), "6.5" , 
AND(C2>29,C2<=31), "7.0" , 
AND(C2>31,C2<=34), "7.5" , 
AND(C2>34,C2>=36), "8.0" , 
AND(C2>36,C2<=38), "8.5" , 
AND(C2>38,C2<=40),"9.0" 
)

Any help would be appreciated.

MattKing MattKing · Accepted Answer · 2021-07-27T02:15:06

First, get rid of all the quotation marks entirely.

Then read about the Match() function, which gets the same results in a significantly simpler way.

This formula, for example, should suffice:

=(MATCH(C2-.01; {10;12;15;17;22;25;29;31;34;36;38})-1)/2+4

Multiple IFS in google sheet giving Formula Parse Error

1 Answers