[Solved] Err:514 after long chain of "IF" statements

[kcharles520]

Code: Select all

=IF(F19=-114;"1.8772";IF(F19=104;"2.04";IF(F19=-142;"1.7042";IF(F19=132;"2.32";IF(F19=-126;"1.7937";IF(F19=116;"2.16";IF(F19=135;"2.35";IF(F19=-145;"1.6897";IF(F19=170;"2.7";IF(F19=-180;"1.5556";IF(F19=107;"2.07";IF(F19=-131;"1.7634";IF(F19=184;"2.84";IF(F19=-198;"1.5051";IF(F19=-112;"1.8929";IF(F19=102;"2.02";IF(F19=-123;"1.8130";IF(F19=113;"2.13";IF(F19=-117;"1.8547";IF(F19=306;"4.06";IF(F19=-351;"1.2849";IF(F19=141;"2.41";IF(F19=-151;"1.6623";IF(F19=105;"2.05";IF(F19=-115;"1.8696";IF(F19=-107;"1.9346";IF(F19=125;"2.25";IF(F19=-105;"1.9524";IF(F19=-109;"1.9174";IF(F19=127;"2.270";IF(F19=-102;"1.9804";IF(F19=164;"2.640";IF(F19=110;"2.1";IF(F19=100;"2.0";IF(F19=229;"3.29";IF(F19=161;"2.61";IF(F19=179;"2.79";IF(F19=151;"2.51";IF(F19=-121;"1.8624";IF(F19=-128;"1.7813";IF(F19=130;"2.3")))))))))))))))))))))))))))))))))))))))))

In column "F" is a number ranging from -300 to 300. I am using "IF" statements to convert that number to a decimal (based on a chart.)
My issue is I am no longer able to add any more "IF" statements to the formula without getting "Err:514", some sort of overflow error.

Any suggestions on how I could continue to add "IF" statements without returning this error? Or perhaps there's a simpler formula for converting the numbers? I am no expert with these formulas by any means

[robleyd]

Perhaps you might find VLOOKUP more helpful; possibly there is another solution but you haven't given details of the actual problem you are trying to solve with this complex formula.

[kcharles520]

It might seem complicated but it's actually quite simple. Column F contains a number from -300 to 300—that number represents the “American” betting odds for a database of sports games. All I am doing is taking that number—and converting it to its “European” odds equivalent. European odds are in decimal form.

So for example, the “American” odds of 135 would be converted to the decimal of 2.35. To do this, I am using “IF” statements to convert the “American” odds in column F is to the appropriate “European” odds, in decimal form.

I was hoping that if I converted a large enough number of the American odds (using “IF” statements) I would have a giant formula. I could then take the formula and simply copy and paste it to convert any set of American odds to its European equivalent, instantly.

However due to Err:514 I am no longer able to add “IF” statements to the formula and therefore cannot finish the project. Hopefully all of that makes sense—what I'm doing is actually quite simple, but perhaps I'm doing it in an overly complicated way.

[Lupp]

Please, please, please, do not post these annoying images. Post text (as code e.g.) or attach files that we can work with.
(@ kcharlses520: Thank you for replacing the image with readable code.)

It might seem complicated but it's actually quite simple.

and that's the reason for what obviously a variant of accessing a lookupt table is the appropriate way to do it. In fact it's not a matter of simple or complicated, but one of clever or poor. Even working with array constants like in

Code: Select all

=INDEX({-3.888;99.5;841};MATCH(F20;{-123;123;456};0))

is better than lots of nested IF calls.

[Villeroy]

It might seem complicated but it's actually quite simple.

Then use a simple formula.

[Lupp]

Yes, I thought it was simple arithmetic. And it was. It's really strange to what degree tasks are mystified and helpers are deceived here.

Code: Select all

=ROUND(IF(F19<0;1-100/F19;F19/100+1);4)

[Lupp]

Back to the original question:

As already stated, the formula is inexpedient. However, it is syntactically correct, and might do as expected by the author if not...

Code: Select all

Err:514 | Internal overflow | Sort operation attempted on too much numeric data (max. 100000) or a calculation stack overflow.

Of course, there wasn't a sort operation on a too large dataset. A stack overflow caused the error. Each new call to the IF() function while the previous one isn't closed has to open a new level on the calculation stack. The given formula needs 41 levels. Obviously that's too much. The behaviour of the formula calculator of Calc insofar is unchanged since "prehistoric" times. All versions of AOO I can test with show it. Concerning LibreOffice it's the same: The very first version (3.3) and the most recent version (6.0) report the same error.

Once again @kcharles520:
Use a lookup table were actually needed.
In the specific case also a formula with a few arithmetic operations and a single conditional alternative (See my previous post!) would do. That's the most simple and the least error-prone way.
By the way: Check the assignment of "1.8624" to the value -121 in your formula. It is wrong. It must read "1.8264".

[kcharles520]

Update: I figured out how to use VLOOKUP and I think it should work nicely for this particular project. Thanks for the help.

[Lupp]

By the way: Check the assignment of "1.8624" to the value -121 in your formula. It is wrong. It must read "1.8264".

If there aren't very special restrictions I cannot imagine, the calculating formula I suggested earlier would clearly be the best way to get the wanted results. In specific it would avoid typos of the kind you had in your original formula, and wich also may occur in a lookup table or in any case where you have to type in lots of entries.

And if you definitely want to get returned texts (strings) instead of numbers, you may use

Code: Select all

=TEXT(ROUND(IF(F19<0;1-100/F19;F19/100+1);4);"#.####")

(Stay open for efficient alternatives. After all we don't still trade across tthe oceans using sailing ships.)

Editing: You (the OQ) or some visitor may want to have a look into the attached demo.