workaround for exceeding 7 nested functions in Excel?

I'm new to excel but reckon it can help me do a lot of the mundane tasks i've been landed with so - I'm attempting to work out overdue invoices using IF AND function in Excel. The worksheet is one I use for work so cannot be changed - I have to work with what's there. Attached - it's quite big so I've deleeted some of it ...

In column AA I've created a formula that looks at the current month entered in month in AA2 and then checks to see if invoices are overdue for the preceding months - trouble is there are 12 months that I need to check and the function only allows me to check 7.

Formula:

=(IF((AND(AO3>0,current_month="April")),"Due",IF((AND(AO3>0,current_month="May")),"overdue",IF((AND(AQ3+AO3>0,current_month="June")),"overdue",IF((AND(AS3+AQ3+AO3>0,current_month="July")),"overdue",IF((AND(AU3+AS3+AQ3+AO3>0,current_month="August")),"overdue",IF((AND(AW3+AU3+AS3+AQ3+AO3>0,current_month="September")),"overdue",IF((AND(AY3+AW3+AU3+AS3+AQ3+AO3>0,current_month="October")),"overdue","OK"))))))))

anyone got any work around or can suggest how I can do this more easily using another function? - bear in mind I have no power to change how the file is laid out!!

another approach is to calculate the total days overdue and use a look up table to give you months, then make a summary table from your data using either a pivot table or the sumproduct function.....

please delete this if it is a frivoulous use of the forum - but really wanted to thank you...

Originally Posted by marcia21

I'm new to excel but reckon it can help me do a lot of the mundane tasks i've been landed with so - I'm attempting to work out overdue invoices using IF AND function in Excel. The worksheet is one I use for work so cannot be changed - I have to work with what's there. Attached - it's quite big so I've deleeted some of it ...

In column AA I've created a formula that looks at the current month entered in month in AA2 and then checks to see if invoices are overdue for the preceding months - trouble is there are 12 months that I need to check and the function only allows me to check 7.

Formula:

=(IF((AND(AO3>0,current_month="April")),"Due",IF((AND(AO3>0,current_month="May")),"overdue",IF((AND(AQ3+AO3>0,current_month="June")),"overdue",IF((AND(AS3+AQ3+AO3>0,current_month="July")),"overdue",IF((AND(AU3+AS3+AQ3+AO3>0,current_month="August")),"overdue",IF((AND(AW3+AU3+AS3+AQ3+AO3>0,current_month="September")),"overdue",IF((AND(AY3+AW3+AU3+AS3+AQ3+AO3>0,current_month="October")),"overdue","OK"))))))))

anyone got any work around or can suggest how I can do this more easily using another function? - bear in mind I have no power to change how the file is laid out!!

Use defined names.

Try this link Nested Cells

I know this is answered yet wanted to add another suggestion.

My view, in general, is that if you need more than 7 IFs you're using the wrong function. Try this formula in AA3

=IF((AND(B3>0,current_month="April")),"Due",IF( SUMPRODUCT(--(COLUMN(B3:X3)<MATCH(LEFT(current_month,3),{"Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar"},0)*2),--(MOD(COLUMN(B3:X3),2)=0),B3:X3),"Overdue","OK"))