HELP! Excel Nested IF Formula Limits!

[funkylad20]

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?!?

[dundurn]

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.

[funkylad20]

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

[funkylad20]

=IF(R39

[funkylad20]

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!

[funkylad20]

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

[dundurn]

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