IF statement

Question

I am trying to do an IF statement where if the invoice days past due is non-overdue, <10days, 10-30days, 31-60 days…etc the invoice amount will be placed in the appropriate column based on the date. So an invoice from Mar 26 would appear in the non-overdue column and on Apr 1 would then jump to display in the <10day column. The formula I have is: =IF(CHOOSE(MATCH(TODAY()-$F9,{-99999,0,31,61,91,99999},1),"Future Date","0-30 Days","31-60 Days","61-90 Days","Over 90 Days")=H$1,$C9,"")

Answer 1

Hi,

Please share some data in a format that can be pasted in an MS Excel workbook.

Answer 2

Hello,

first take a look at your formula:

IF(CHOOSE(MATCH(TODAY()-$F9,{-99999,0,31,61,91,99999},1),"Future Date","0-30 Days","31-60 Days","61-90 Days","Over 90 Days")=H$1,$C9,"")

You choose the due value and compare that with H$1. If these match you return $C9 else you return nothing. The choose part can display the due category. Why the comparison with H$1? $C9 holds the invoice amount?

I came up with an IFS-formula (your comparisons are incremntal) for your selection which does the same as your CHOOSE-part (extended by '<10 Days'). See cell A9.

=IFS((TODAY()-$F9)<0,"Future Date",(TODAY()-$F9)<10,"< 10 Days",(TODAY()-$F9)<31,"10-30 Days",(TODAY()-$F9)<61,"31-60 Days",(TODAY()-$F9)<91,"61-90 Days",TRUE,"Over 90 Days")

To fill different columns from one formula you need to use an array-formula. Cell A13 holds the formula

=MAKEARRAY(1,6,LAMBDA(row,col,IF(AND(TODAY()-$F9<(INDEX({-99999,0,10,31,61,91,9999},1,col+1)),TODAY()-$F9>=(INDEX({-99999,0,10,31,61,91,9999},1,col))),"x","")))

There are no formulas in cells B13 to F13 - they get filled by MAKEARRAY and have to be empty else you will get an error.

If your invoice amount - that should be displayed in the appropriate due column - is in cell $C9 you have to replace "x" with $C9, else use any value / reference you want ...

Kind Regards

Gerhard

Answer 3

Try this formula:

=IF((TODAY()-F12)<=0,"Future date",CHOOSE(MIN(ROUNDUP((TODAY()-F12)/30,0),4),"0-30 Days","31-60 Days","61-90 Days","Over 90 Days"))

This will generate an attribute for your invoice in a single column which you can filter.

If you want to generate new columns which will contain only invoice amounts between certain number of Due Dates, try these formulas:

=IF(ROUNDUP((TODAY()-$F12),0)<=0,$C12,"") Future date

=IF(ROUNDUP((TODAY()-$F12),0)=30,$C12,"") 0-30 Days

=IF(ROUNDUP((TODAY()-$F12),0)=60,$C12,"") 31-60 Days

=IF(ROUNDUP((TODAY()-$F12),0)=90,$C12,"") 61-90 Days

=IF(ROUNDUP((TODAY()-$F12),0)>=90,$C12,"") Over 90 Days

They are all using rather simple IF formulas, and you don't seem to need anything more complicated that this.