Nested IF function causing formula overflow

Hi, there

First of all, I'm very inexperienced with Excel, have only been dabbling with it for a couple days, so I know only the basic's basics (and probably not even all of them).

The situation is that I want, let's say, cell C1 to do B1-A1, which is simple enough. The problem is that I don't want plain B1's or A1's values, I want them to be applied to a formula, which would also be simple enough, except the formula they must be applied to depends on what initial value A1 and B1 have. So the only thing I could find that could do something like this was nested IF functions.

Here's a more concrete example, in case my explanation made no sense: C1 = IF(1<=B1<500;[Formula#1];IF(500<=B1<1000;[Formula#2]... and so on and so forth.

There are 20 different formulas, meaning 20 nested if statements (which I may eventually need to expand to 40), which very quickly hits the token limit, especially since each individual formula is quite lengthy and complex. Even if I try splitting it to, for example, calculate A1 applied to a formula in cell D1 and B1 applied to a formula be calculated in E1, they both still overflow.

My searches on Google haven't turned out too well, since, considering I'm pretty new with this, I don't quite know what to search for, what I find doesn't seem to be related to it and even if it is, I can't identify it, due to not knowing my way around Excel that well, yet. So if anybody knows how to solve this issue and if possibly explain it more or less thoroughly, I'd greatly appreciate it.


Edit: here's a sample of what I'm trying to do Sample.xls

you can provide a sample workbook for the members to play around.

To attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic


and possible put some note's on what you expected.

C1 = IF(AND(1<=B1,B1<500);[Formula#1];IF(AND(500<=B1,B1<1000);[Formula#2]...

But, if you have that many it will still fail after 7 IFs.

Have a look at:

http://www.cpearson.com/excel/nested.htm


Regards, TMS

Originally Posted by TMShucks

C1 = IF(AND(1<=B1,B1<500);[Formula#1];IF(AND(500<=B1,B1<1000);[Formula#2]...

But, if you have that many it will still fail after 7 IFs.

Have a look at:

http://www.cpearson.com/excel/nested.htm


Regards, TMS

Thanks, that link's solution looks exactly like what I need.

Edit: nvm, just noticed there's a link in there explaining named formulas.

Just a hint as well, you should change your profile location to where you actually are...there are regional differences on separators (yours are obviously different than mine, I use ",", you are using ";"),so that at the very least, we can suggest changing the separators if there are problems with offered solutions

Ok, I think I got it with the naming formulas thing, but I just have one last question: how do I define the named formula in a place, then set the variable to be used on another cell?

I tried uploading a sample here, but I couldn't figure out, so here goes a raw written description each cell's contents:

Please Login or Register  to view this content.

Is there a way to make the named formula have their variable unknown, and then set the variable on the named formulas on the cell that will use the named formula? If so, how? I know that you can assign the variable on the named formula itself, but then I'll need to copy the named formula columns before each new set of data, which I'd rather avoid.

Secondly: each individual formula that composes the named formulas has several sets of parenthesis in them, will that not possibly make the formula overflow anyway?

To attach a sample workbook.

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

Yes, I know all that. I tried it before and it didn't work. Selected the file to upload, nothing happened, I tried everything I could. It was a supported file extension.

Anyway, it's a rather simple question, I guess. Is a sample workbook really necessary? How to set the variable on a named formula to be a different cell than the one set when the formula was named? Like I set the formula on A1 as =5B1-2B1^2+5, for example, then name it F_1. How do I make it so another cell, say F8, uses this same F_1 formula, but with C3 as the variable, instead of B1?

Not too simple. the distance from A1 to F8 very different with distance from B1 to C3, we may have to use ADDRESS function to convert.

That looks very promising, Andrew, thank you, I'll give it a try.

Edit: tried it, and unless I fluffed up, somehow, which is actually very likely, it still gives me a formula overflow error, before I even put the formulas in, and only make it match to five different values.

Guess I'll eat my own words to dredwolf about the location thing, it does seem to be getting in the way for this. My version is in BR Portuguese, so identifying the corresponding function names is not being as easy as I first imagined.

Thank you, those were the ones I tried to use after Andrew's post, and gave me the formula overflow error as soon as I had it at "=CHOOSE(MATCH(A2,{0,500}" (or "=ESCOLHER(CORRESP(A2{0,500}" in my language) regardless of if I tried closing brackets and finishing the function properly, which is rather odd and is why I wasn't sure if I picked the right function for MATCH, as I'm fairly sure I filled it out properly.

At any rate, I chose a less elegant way to solve my problem. I created 21 columns, and put "IF(AND(1<=A1;500>A1);Formula#1;0" on B1, "IF(AND(500<=A1;1000>A1);Formula#2;0" on C1 and so on till Formula#20, then on V1 I set it to =SUM(B1:U1). I then chose to hide columns B through U to keep things tidy and easy to read. Solves my problem nicely, except for one last thing. I need to copy the columns over and over for every new data input and the ones I chose to hide are not hidden upon being pasted. Is there a way to make the copied hidden columns remain hidden after pasting?


Edit: finally managed to get a working upload of the initial problem. Here it is Sample.xls

The range starts at 1-500 then proceeds at 500 steps up until 10000.

The formulae only have one variable, they're simply curve functions. What I posted in #14 was referring to the fact that I have cells that do, for example, A1 applied as variable to one of the 20 formulae depending on it's range subtracted by B1 applied to one of the same 20 formulae depending on it's range.

The formula of how I want to calculate D1 is in the sample I posted, except with placeholders for the individual formulas, all you have to do is put a "=" in front of it, as commented beneath it. Unless there was some sort of issue when saving or incompatibilities between our versions or something which is preventing you from seeing what I wrote in D1, which more or less happened earlier when I tried to upload a sample workbook and couldn't.

And yes, to be honest, I am a bit reluctant to release the specific formulas not because I think anybody here can misuse it, but because I'd rather not have them out in a place where google and other search engines may find them, although the reason I gave on my previous post was also a big factor in it. Frankly, I don't understand why the specific formulas are required to solve my problem, but if you absolutely must have them in order to do so, I can send them to you via private message. The reason I hadn't posted a sample workbook from the start, however, was that for some reason the upload tool wasn't accepting it or changing the data in the cells to useless gibberish.

Don't get me wrong, by the way, I truly appreciate the help you guys are offering.

Okay, I figured out how to do the VLOOKUP function, more or less, which should save me a lot of trouble. The problem is that it is returning the raw number from the index cells, rather than applying the value from the search criteria cell to the formula in the index cell.

Here is a sample that illustrates it better: vlookup sample.xls

Keep in mind that everything is in the Brazillian Portuguese version of formatting both in this and my previous sample, so if your excel can't read it or gives you errors, it's because of language incompatibilities.

Edit: oops, put the link to the wrong sample there, fixed it now.

@Vicius

Would you also change your location to something more meaningful as requested by dredwolf in post #5.

This can help in all sorts of ways since knowing your location helps not only with knowing things like your parameter separator character as dredwolf says, but also your system locales for date standards and indeed your time zone.

Originally Posted by Pete_UK

That was not what I meant when I suggested using VLOOKUP, but to draw this to a conclusion I've picked up on Andrew's suggestion in post #10 and put this formula in cell D1 of your original sample:

=CHOOSE(INT(B1/500)+1,B1+100,B1-100,B1/100)

If you download the attached file then this should be translated for you, as Excel uses tokens to store function names and separators.

The first parameter of the CHOOSE function, i.e. INT(B1/500)+1, will return 1 for values of B1 in the range 0-500, then 2 for the range 501-1000, then 3 for the range 1001-1500, and so on. So for the first range the CHOOSE function will evaluate the expression B1+100. If B1 is between 501 and 1000, then the CHOOSE function will evaluate the expression B1-100, and so on for other ranges.

These are fairly trivial examples, and there are only 3 of them, but if you put your various formulae into the CHOOSE function (without the = sign), and extend it to a list of 20 of them, then you should have a formula which does what you are requiring. You can have a similar CHOOSE function following it, using A1 as the variable. The formula can be copied into cell G1, as in the example file, and the references to B1 will change to E1.

I hope this satisfies your requirements - if so, please mark the thread as Solved by clicking on "Thread Tools" immediately above your first post. You might also like to show your appreciation to those who have tried to help you by clicking on the "star" icon in the bottom left corner of any post that you have found to be helpful (not just in this thread).

Hope this helps.

Pete

Thank you, your explanation helped me understand better how the CHOOSE function works, and the INT bit was very clever.

Apparently this seems to solve my issue, for now at least. It may no longer work once I need to expand to using 40 formulae instead of 20, but it'll probably be some time until then.

I have learned much from all the replies. I'll mark it as solved and spread some rep around.

Thanks for the rep. Feels a bit undeserved in relation to the effort Pete put in but, nevertheless, gratefully received.

Regards, TMS

The nested IF limit was raised to 64 levels in Excel 2007... just FYI for anyone stumbling upon this thread.

- Moo

Hmm, apparently it is not quite as solved as I thought.

For some reason, the CHOOSE function is only allowing me to input 15 out of the 30 values (20 of which I would need). If I try to input a 16th or more, it instantly becomes an Error #512, meaning formula overflow again...

Edit: nvm, solved it by using 3 cells, each with 7 nested IF functions for the formula, then hid the lines. Seems like the only way to escape this damn formula overflow is getting a new Excel...

Originally Posted by Pete_UK

I don't have XL2003 anymore, but XL2007 allows up to 254 choices in CHOOSE. If the limit is 12 in XL2003, then you can overcome it by having something like this:

=IF(INT(B1/500)+1<=12,CHOOSE(INT(B1/500)+1,B1+100,B1-100,B1/100,formula_4,formula_5,formula_6,formula_7,formula_8,formula_9,formula_10,formula_11,formula_12),
CHOOSE(INT(B1/500)+1,formula_13,formula_14,formula_15,formula_16,formula_17,formula_18,formula_19,formula_20,formula_21,formula_22,formula_23,formula_24))

Hope this helps.

Pete

The limit is supposed to be 30 in mine. But it's overflowing way earlier than that, probably due to the complexity of the formulas. I'll give your suggestion a try, looks interesting too, thanks.

Edit: still causes formula overflow.

From your last sample file in post #22, isn't it possible to change all cell reference in those formulas to A23 then your vlookup??
sample formulas from that file

6.22303E-20*A23^7 - 2.537E-28*A23^10 initially it refred to A1

=VLOOKUP(A23,A1:B21,2)

Yes, but that would require me to make a new table for every new data input, since the variables on the formulas are fixed, which would be kind of an unnecessary hassle.

make a new table for every new data input

from your file attached ,no.....the vlookup will return the range where the formula is located and use that formula for computation


edit attaching sample file..

vladvlook.xlsx

You mean making a cell entry on the table for each unit between 1 and 10000, if I understood correctly?
Unfortunately that's not a possibility, since the input data includes decimals, which have a significantly different result when applied to the formulas. Basically, for 2 decimals, which is the least I need for the spreadsheet, it'd require nearly one million entries from 1 to 10000, then another million once I expand the spreadsheet to also include another 20 formulas for the 10000 to 20000 range.

I give up, I'm getting Excel 2010 just for the higher token limit.

You mean making a cell entry on the table for each unit between 1 and 10000, if I understood correctly?

No you will not create a table for each unit. I just created that just for comparison purposes as the note on the sheet above that tells.
....

just try may sample workbook with different numbers you wish then compute that number manually, if necessary compare the resulting figures.

for example the number 501.23 - so it should use the formula 500 to 1000 right? the original formula is 40.029-0.000000000000000485*A2^6-0.000300705537*A2^4-0.0014195*A2^2
so change A3 with the number 501.23

---what is your expected output for this number (501.23 computed manually)

Put the number 501.23 on the sample workbook I provided with the vlookup....
compare results..

@Vicius

Are you going to change your location as requested in post #s 5 & 24?

@Vicius

I would really appreciate it since you've already been asked if you'd take the time to change your location.

I don't agree for the reasons that you have been given. Knowing your location can only help and it is NOT irrelevant. It can never be a disadvantage.

Please change it.

Why would you not want to do so when you have been asked? Those who answer your questions give freely of their time and knowing people's locations can often save time by avoiding wasted effort and uncertainty.

Now you have been asked I expect to see it changed.

It DOES help.
you get responses from from people in you region, or using same excel as you, or can translate the formulas...etc...
However, I still dont get what problem you have with vlady's VLOOKUP solution in post #34,I have 65 formulas working with that particular solution..similar results with just about all the solutions offered...so my question is, have you ACTUALLY tried the solutions offered with what you have posted? (ie.. put different values in the cells TOLD to you to put them in, LOOKED at the results..etc..)
Here is a simpler version of Vlady's post...trying to explain what is happening with the vlookup..:Vicius.sol1.xls
Actually E1 or F1 result will be based on the value you enter into D1, works out to the same for your purposes, change the formulas in B or C Columns, and the Result will be based on what you enter in D1...the way I have it set-up is to show YOU which formula is used for the value YOU enter into D1

Edit-
Sorry for typos, got a flaky keyboard...

darnit..meant this:Vicius.sol2.xls.xlsx

Originally Posted by Vicius

@ Richard

Fine, if it's so important there it is. Told you it doesn't really help at all, though.

Thank you. We'll have to differ on this subject. But since I've over 8000 posts on this forum I think I'm entitled to have the view that I - and others do. Believe me I've wasted time in the past when knowing this simple fact might have prevented this.

Just by chance, you don't have show formulas checked, rather than show values?
I only ask this because I have NO problems getting the expected results and you are having such a difficult time with it..

I'm going to offer one more solution, PLEASE look at it closely before you dismiss it(AND it is just a variation of whats already been offered, I believe by Pete_UK), it just uses excel functions to do the brute work:
NOTE+++ this particular solution ONLY works on the value directly to the left of it +++
Check the attached:Vicius.sol2.xlsm