Nested IFs In Excel Formula

**Sean04** · 02-04-2010, 02:40 PM

Hey!
So I have the following:

Please Login or Register  to view this content.

That is all in a for loop and thats why "i" is there...

So the above code works fine but when I try to copy/paste another IF at the end of that like:

Please Login or Register  to view this content.

I get this error in visual studio:

Exception from HRESULT: 0x800A03EC

I'm assuming the problem is with the formula but it doesn't make any sense because all im doing is copy/pasting which I did for the rest of that formula... Now when I need to add one more it errors?! Dont know...

For example, this wont work:

Please Login or Register  to view this content.

The code in red is what i added, and now doesnt work!
Thanks,
Sean

**jj72uk** · 02-04-2010, 05:03 PM

Simple answer... excel cannot handle more that 7 nested IF statements. Hence why you add one more and it won't work.

**JBeaucaire** · 02-04-2010, 05:37 PM

It's time to replace your IFs with a lookup table. Create a table somewhere in your workbook with the text strings in the first column, the matching numeric columns in the second column.

Please Login or Register  to view this content.

Then use a VLOOKUP() or INDEX/MATCH():

Please Login or Register  to view this content.

Also, if you're looping through the "i" values inserting that formula one row at a time, that's unnecessary. You can skip the loop and insert the formula in the entire range all at once, or do a FILLDOWN...several ways to eliminate the looping.

Nested IFs In Excel Formula

LinkBack

Thread Tools

Rate This Thread

Display

Nested IFs In Excel Formula

Re: Nested IFs In Excel Formula

Re: Nested IFs In Excel Formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions