Too many nested functions

[alison217]

Hi,

I wonder if someone could tell me how to condense this?

=IF(X6=60,"30",IF(X6=90,"45",IF(X6=120,"60",IF(X6=150,"75",IF(X6=180,"90",IF(X6=210,"105",IF(X6=240,"120",IF(X6=270,"112.50",IF(X6=300,"125",IF(X6=330,"137.50",IF(X6=360,"150"),IF(X6=390,"162.5",IF(X6=420,"175",IF(X5=450,"187.50"))))))))))))))

When I put this into Excel 2010, it tells me there are too many nested functions, but I don't know how else to get the same result?

Thanks in advance

[JohnTopley]

Try ...

=LOOKUP(X6,{60,90,120,0,0},{30,45,60,0,0})

The zeros are place holders for the rest of the data do not put the numbers in quotes (" " ) UNLESS you want them as text,

[pepe74287]

Place table below to A1:B14 and change formula to =lookup(X6,A1:A14,B1:B14). Please note values in column A must be sorted from smallest to largest.
60 30
90 45
120 60
150 75
180 90
210 105
240 120
270 112.5
300 125
330 137.5
360 150
390 162.5
420 175
450 187.5

Another approach is using vlookup:
With the same table: =vlookup(X6,A1:B14,2,false) - in this case values in column A doesn't need to be sorted.

Btw, the problem with your formula is right bracket in this part of the formula: X6=360,"150") - if you remove the bracket, your formula will work. But I strongly recommend not to use so many nested functions.

[José Augusto]

Hi

Create a table, for example in B3:C15, and use

Please Login or Register  to view this content.

for the corresponding value of E3.

Best regards

[JimBobBowie]

Try this it works on My 2013 version. You had one extra ")" after x6=360,"150".

Please Login or Register  to view this content.

JimBobBowie

[TMS]

Another option:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Or, you could put the whole lookup table in the formula:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

And your original formula corrected:

Please Login or Register  to view this content.

[alison217]

Hi All,

thank you all for your help!

This seems to be the easiest way for me to do it.

=IFERROR(INDEX({30,45,60,75,90,105,120,112.5,125,137.5,150,162.5,175,187.5},MATCH(X6,{60,90,120,150,180,210,240,270,300,330,360,390,420,450},0)),"error")

Thank you :D

[TMS]

You're welcome.




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.