Nested IF Functions

Hi Guys

not sure if I can reopen a query, he goes

Jb helped with the info I gave him or did not, now I have added another section in sheet 5

Help JB

Stephen

I see some if functions here and there, but without knowing which ones you want to have precedence I can't really put them together for you.

Anytime I need to put together a large group of "if"s, I do them one at a time, a-like-a-so

Please Login or Register  to view this content.

That way you can test each section while still only changing your initial data, also works if they're dependant upon eachother ( The code would simply refer to the cell above it for it's criteria ). When you want to conglomerate them all, start at the bottom, (I'd recommend making a copy of them while seperated for future use), anyways, start at the bottom, and work your way up, replacing "Next step" with the formula from the line below it, without the = of course ( If the cells are dependant upon eachother, you'd replace any part of the code which had say.. A2, with the formula in A2) . Add one more bracket on the end for each IF you add, and there you have it - An insanely complex if statement which is completely indicipherable. But functional.

p.s.
I'd REALLY recommend keeping a copy of the broken down versions for future use, saves a lot of trouble if criteria changes.

You can nest 7 if statements together. To go beyond that would require you use a second column to do some followup. 14 IFs would probably take 3 cells.

Are you sure you even need IF? It appears you're looping the same formula over and over looking for a value, and that is what LOOKUP is best suited for.

I added some range markers at the top of sheet5 in green, and changed the formulas on Sheet4 to show how a much shorter LOOKUP gets you the same result (green cells).

Is that cell N14 the one that was giving you problems? It works now.

Please download and review the sheet I posted above. The green cells on sheets 4 & 5 are my edits. I think this is all you need, to stop using IF and use LOOKUP instead.

I created a LOOKUP designed to replicate the answers you showed in your first post. Were those answers wrong? You didn't say that...

What value in E2 was generating these answers? 1 7 7 7 5 5 2 2 ?

OK, I realized your cell D5 was critical, too. So I built in a check into the formulas in N7-N14 to watch that cell, too.

When I parse out the value in E2, it's equivalent to column F on Sheet5, right? In that case, based on the choice in D5, the data in green now flows from column F for that value.

I added the missing 2320 chart at the bottom of Sheet5 and it needs your actual part # and values in the chart. (yellow cells) But the sheet4 green cells are watching that part already, so they'll work once you put those values in.

Be sure to EDIT your original post and mark the PREFIX box [SOLVED]

Did you just ask a question I missed? I thought this thread was solved.

How many more? Any chance you can lay that out completely before submitting it for any more help? Add all the sections you want, then post it up again.

Here you go.

I made the formulas a little easier to read in the cells, so you can decipher it more readily if you want to add another level. You can go 3 more, then you need a new design since IF only goes 7 deep in Excel 2003.

It was really bugging me bumping up into those IF limitations, so here is your sheet without IF usage, changing over to an Indexed TABLE (name range). To make it work simply, I used an added column on Sheet5 to make a single lookup range from the small table and sub item, you'll see what I mean when you look at column A.

When you expand your tables to include more sections, just make sure those helper cells are included and your Sheet4 Index/Match formulas will just keep plugging along.

Cheers.

I noticed you had triggered M22 with a reference that didn't take into account that Batten figures started at 1, and that makes 1-1=0, so the cell stayed blank.

Take care.

Well, built into LOOKUP is the ability to drop down to the next lowest value in a table and still keep working. That's why it was fine in earlier versions.

INDEX/MATCH is an exact science. Sorry, I forgot that. I added in a FLOOR() formula to force the E2 calculation to drop down to the next 250 mark, which is what you've changed your chart to use for breakpoints on Sheet5.

I added in some fun conditional formatting, too, in the bottom right section.

I love Excel.