Donate SIGN UP

HELP! Excel Nested IF Formula Limits!

Avatar Image
funkylad20 | 11:39 Thu 24th Sep 2009 | Computers
6 Answers
I am trying to create a nested formula in Excel, but am struggling with the maximum of 7 nested entries limitations. Is there any way around this?!?
Gravatar

Answers

1 to 6 of 6rss feed

Best Answer

No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
In my experience, always, but how you get round it will depend on your exact requirements.

First remember it's 7 levels of nesting, not 7 IF statements. So if you are trying to find a preselected square on a chessboard. Is it A1?, is it A2? is it A3? etc isn't going to work. However, is it in the top half?, is it on the right hand side? , is it in the top half?, is it on the right hand side?, is it in the top half?, is it on the right hand side? will get you down to one square after only six questions. You can write an excel statement to mimic this operation which will use many more IF statements than six but any path taken in the formula will only encounter six so that's OK.

Another more pedestrian way is to write lots of formulae each one depending on the previous one. The first one can deal with 7 discrete values. The next on only operates if the first failed to find a value and carries on for the next 6 discrete values. You can keep on writing these "working" formulae for as long as it takes. If you've written the formulae correctly then one and only one formula will bring back a result. A final result column will assemble the correct answer from the "working" columns.

There are other ways round this sort of problem using lookup tables, modulus arithmetic etc. Your particular problem may need something different or a bit out of the usual. If the above isn't clear some more information on your particular problem might help.
Question Author
OK, I have to establish when a piece of work is completed, dependent on the deadline will depend on what meeting this will be sent to (I work in grants, so basically we have assessment deadlines just before each monthly panel date, dependent on the deadline will return a result as to which month it has to go into).

So, for example, an assessment is due for completion on 21st September 2009. The date is entered in A1. The next panel it can possibly go into is the September one (end of the month). If the date given at A1 is AFTER say 30th September, it needs to recognise this as after the September 'cut off' and the result need to read October 2009 panel.

I have listed all of the monthly panel names, actual panel dates and deadline dates in some 'hidden' cells at the top, and the formula works, but only for 7 months. The simple formula is:
=IF(A1
Question Author
=IF(R39
Question Author
bloody answerbank won't let me type the formula I am using!
Final try...
=IF(R39 LESS THAN D20-2,B19)

THE LESS THAN TEXT IS ACTUALLY THE SYMBOL USED IN EXCEL, MATHS AND SCIENCE, MAYBE ANSWERBANK DOESN'T LIKE THIS?! IT'S A V ON IT'S SIDE!
Question Author
Ah, it worked. So:
R39 is the date given for when the applications assessment needs completing by staff
D20 is the date given as the final cut off for the assessments
-2 is something which I need to put in to add a further 2 days for some reason
B19 is the month of the panel i.e. September 2009
For this I'd ignore IF completely.
Set up another tab - call it Data - and on it list in two columns your deadline dates and the meetings they apply to. E.g. cell A1 Deadline, A2 31/1/2009, A3 28/02/2009 etc B1 Meeting, B2 Jan 09, B3 Feb 09 etc The big advantage of this is it allows you to rejig meetings and dates to allow for weekends, holidays, rearranged meetings etc.

Now write a formula on your original sheet to retrieve the appropriate date. So the work date in cell a2
=vlookup(a2,Data!$A:$B,2,true). Drag this formula down past the other dates to retrieve the data for the other pieces of work

1 to 6 of 6rss feed

Do you know the answer?

HELP! Excel Nested IF Formula Limits!

Answer Question >>

Related Questions

Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.