IFS(E2≤5000, 10, E2≤10000, 20, E2≤15000, 30, E2≤20000, 40, E2≤25000, 50, E2≤30000, 60, E2≤35000, 70, E2≤40,0, 80, E2≤50,0, 90, E2≤65000,100, E2≤70,0, 110, E2≤75000, 120, E2≤85,0, 130, E2≤90,0, 140, E2≤100,0, 150, E2≤125,0, 175, E2≤150,0, 200, E2≤175,0, 225, E2≤200,0, 250, E2≤250,0, 300, E2≤300,0, 350, E2≤350,0, 400, E2≤400000, 425, E2≤500000, 450, E2≤600000, 500)
MacBook Pro 13″
IFS acts like a string of IF,THEN tests, testing each IF statement in the order they are listed, and acting on the first to return TRUE.
Here's a rearrangement of the argument pairs in your formula above.
IFS(
E2≤5000, 10,
E2≤10000, 20,
E2≤15000, 30,
E2≤20000, 40,
E2≤25000, 50,
E2≤30000, 60,
E2≤35000, 70,
E2≤40,0, 80,
E2≤50,0, 90,
E2≤65000,100,
E2≤70,0, 110,
E2≤75000, 120,
E2≤85,0, 130,
E2≤90,0, 140,
E2≤100,0, 150,
E2≤125,0, 175,
E2≤150,0, 200,
E2≤175,0, 225,
E2≤200,0, 250,
E2≤250,0, 300,
E2≤300,0, 350,
E2≤350,0, 400,
E2≤400000, 425,
E2≤500000, 450,
E2≤600000, 500)
Issues arise in the eighth pair, shown in bold italic text:
There are three arguments in this row: E2≤40, 0, 80
The same is true of all the other 'pairs' shown in bold italic.
Removing the ",0" from those 'pairs' will remove the 'requires an even number of arguments
' issue, but would leave a second issue active.
IFS tests each E2≤nn statement in the order it is listed. The largest number tested in the bold italic 'pairs' is 350.
any number that is less than or equal to this (or any of the 3 digit numbers in the highlighted values) is also less than the number 5000 in the first comparison. That first comparison would return TRUE, placing a 10 in its cell, and the table would move on to the next formula (if any).
Editing the argument pairs to ensure that the test value is increasing as IFS tests each in turn, and that NONE of those numbers include commas should remove the issue you are seeing.
Regards,
Barry
IFS acts like a string of IF,THEN tests, testing each IF statement in the order they are listed, and acting on the first to return TRUE.
Here's a rearrangement of the argument pairs in your formula above.
IFS(
E2≤5000, 10,
E2≤10000, 20,
E2≤15000, 30,
E2≤20000, 40,
E2≤25000, 50,
E2≤30000, 60,
E2≤35000, 70,
E2≤40,0, 80,
E2≤50,0, 90,
E2≤65000,100,
E2≤70,0, 110,
E2≤75000, 120,
E2≤85,0, 130,
E2≤90,0, 140,
E2≤100,0, 150,
E2≤125,0, 175,
E2≤150,0, 200,
E2≤175,0, 225,
E2≤200,0, 250,
E2≤250,0, 300,
E2≤300,0, 350,
E2≤350,0, 400,
E2≤400000, 425,
E2≤500000, 450,
E2≤600000, 500)
Issues arise in the eighth pair, shown in bold italic text:
There are three arguments in this row: E2≤40, 0, 80
The same is true of all the other 'pairs' shown in bold italic.
Removing the ",0" from those 'pairs' will remove the 'requires an even number of arguments
' issue, but would leave a second issue active.
IFS tests each E2≤nn statement in the order it is listed. The largest number tested in the bold italic 'pairs' is 350.
any number that is less than or equal to this (or any of the 3 digit numbers in the highlighted values) is also less than the number 5000 in the first comparison. That first comparison would return TRUE, placing a 10 in its cell, and the table would move on to the next formula (if any).
Editing the argument pairs to ensure that the test value is increasing as IFS tests each in turn, and that NONE of those numbers include commas should remove the issue you are seeing.
Regards,
Barry
Each test needs to have a value after it, so the total arguments need to be even; none of the numbers used in it can contain commas.
(242752)
Note how you have multiple ifs-true after some of the ifs-expression.
Also you will want to put your ifs-expressions in order, ig E2<=40 before E2<=5000 , etc.
More on IFS here:
IFS - Apple Support. (also accessible via Help > Formulas and Function Help in your menu).
SG
Hi tcwestover,
Looking at Barry's list of argument pairs, I wondered if the extra commas were typos (easy to make typos in complex formulas). We can do without all those IFS argument pairs by using a lookup table. I have taken best guesses as to the values.
Formula in F2 of Table 1:
XLOOKUP(E2,Lookup::A,Lookup::B, "Not Found",1,1)
Regards,
Ian.
EXPLAIN THE MEANING OF MAC NUMBERS ERROR MESSAGE "IFS requires an even number of arguments"
