Getting Error When IFS function gets too long ("too many arguments")

[Wx4Mark]

This formula works:

=IFS(AC2>233,"B10/R0", AC2>215, "B8/R2", AC2>199, "B6/R4", AC2>189,"R6/B4", AC2>179, "R8/B2", AC2>169, "R10/O0", AC2>159, "R8/O2", AC2>149, "R6/O4", AC2>139, "O6/R4", AC2>129, "O8/R2", AC2>119, "O10/Y0", AC2>109, "O8/Y2", AC2>99, "O6/Y4", AC2>89, "Y6/O4", AC2>79, "Y8/O2")

But when I try to expand it further, adding one more term:

=IFS(AC2>233,"B10/R0", AC2>215, "B8/R2", AC2>199, "B6/R4", AC2>189,"R6/B4", AC2>179, "R8/B2", AC2>169, "R10/O0", AC2>159, "R8/O2", AC2>149, "R6/O4", AC2>139, "O6/R4", AC2>129, "O8/R2", AC2>119, "O10/Y0", AC2>109, "O8/Y2", AC2>99, "O6/Y4", AC2>89, "Y6/O4", AC2>79, "Y8/O2", AC2>69, "Y10/G0")

I get the error "You entered more arguments for this function than are supported in the current file format. Any ideas as to that reference? I thought IFS formulas could have up to 127 arguments.

Thank you in advance!

[Sam Capricci]

must be something else, I don't get that error with either formula. Can you post a sample following the directions in the yellow banner at the top of the post?

[Fluff13]

Sounds like your trying to enter it in an old xls file, or a file that is open in compatibility mode.

[Wx4Mark]

It is an .xls. What extension is needed? .xlsx? I can give that a shot.

[Sam Capricci]

you might want to just note that these two conditions in your formula give the same output... AC2>199, "B6/R4", AC2>189,"R6/B4"

[Sam Capricci]

Yes, xlsx is needed, but i don't understand how you get the first one to work in an xls but not the second one, wasn't aware ifs was available in xls at all.

[Fluff13]

It is an .xls. What extension is needed? .xlsx? I can give that a shot.

As long as it's one of the new 4character extensions & not open in compatibility mode, it should be fine.

you might want to just note that these two conditions in your formula give the same output... AC2>199, "B6/R4", AC2>189,"R6/B4"

Not for me they don't, If I enter 190 into AC2 I get "R6/B4" & if I enter 200 I get "B6/R4"

[Wx4Mark]

Thanks for helping - I don't understand either. I changed the file type to .xlsx and it still didn't work. I'm attaching a stripped down sheet here.

[Sam Capricci]

@Fluff, yeah, you are right, I had to put on my magnifiers as I didn't notice the slight difference the way the B and R showed up in sheet. Thx. LOL. (don't get old.)

[Sam Capricci]

worked for me, if you are referring to cell L14, I just clicked inside the formula and removed a couple spaces and it got active and worked fine.

[Fluff13]

That works for me, but if you are happy to change the Risk Score data to
Excel 2016 (Windows) 32 bit

	AE
2	0
3	16
4	34
5	50
6	60
7	70
8	80
9	90
10	100
11	110
12	120
13	130
14	140
15	150
16	160
17	170
18	180
19	190
20	200
21	216
22	233

Sheet: SUMMARY

You can use
=XLOOKUP(AC2,AE2:AE22,AF2:AF22,"",-1)

[dosydos]

@Wx4Mark

I opened your file and the formulas in L14 and L15 both are returning O6/Y4.

The file that uploaded opened as an .xlsx file.
Make sure your actual file is not opening in compatability mode

[Wx4Mark]

Sorry - had a meeting, sorry to disappear for a bit there.

So - you said there are extra spaces in the formula that are breaking it? Should the formulas not have spaces at all? Between the comma and quotes, etc?

[Wx4Mark]

Thank you. This appears to be the case. I see that for some reason, it opened in compatibility mode for some reason. For my education, why would files open in that mode, or switch to it? I'm concerned that if another opens my spreadsheet, the formula may not work for some reason.

[Fluff13]

AFAIK only the old style files (such as .xls) open in compatibility mode