IF statement needs TEXT "NULL" on "column B2" when "Column A2" reads NULL.

[O_ch]

Hi, I am tracking 'aging overdue invoices', and I am creating an IF statement formula where it finds the total per days outstanding. Due to the reason that My database have the correct updated syntax script my output provides the correct "days outstanding" and many ROWS I get the text "NULL". All overdue days list correctly on second column "B" with current formula. How can I insert the TEXT "NULL" on "column B2" Aging Bucket when "Column A2" reads NULL?

Here is my current formula inserted on "Column "Aging Bucket" ROW "B2":

=IF(A2>90,"91-120 Days Past Due ",IF(A2>60,"61-90 Days Past Due",IF(A2>15,"16-30 Days Past due",IF(A2>-0.1,"0-15 Days Past Due","0 Current"))))


See attached image.


My current data output:

Column A2
Days_Overdue

25​

25​

25​

25​

25​

NULL​

NULL​

NULL​

NULL​

[Formula Set on B2]
Column B2
Aging Bucket

16-30 Days Past due​

16-30 Days Past due​

16-30 Days Past due​

16-30 Days Past due​

16-30 Days Past due​

181-365+ Days Past Due​

181-365+ Days Past Due​

181-365+ Days Past Due​

181-365+ Days Past Due​

 

[Sufiyan97]

Try

=IF(A2="Null","Null",IF(A2>90,"91-120 Days Past Due ",IF(A2>60,"61-90 Days Past Due",IF(A2>15,"16-30 Days Past due",IF(A2>-0.1,"0-15 Days Past Due","0 Current")))))

 

[Toadstool]

Hi O_ch,

Is this what you want?

O_ch.xlsx
	A	B
1	Days Overdue	Aging Bucket
2	25	16-30 Days Past due
3	25	16-30 Days Past due
4	25	16-30 Days Past due
5	25	16-30 Days Past due
6	NULL	NULL
7	NULL	NULL
8	NULL	NULL
9	NULL	NULL
10	NULL	NULL
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="NULL","NULL",IF(A2>90,"91-120 Days Past Due ",IF(A2>60,"61-90 Days Past Due",IF(A2>15,"16-30 Days Past due",IF(A2>-0.1,"0-15 Days Past Due","0 Current")))))

 

[Peter_SSs]

Welcome to the MrExcel board!

There seems to be a discrepancy between the formula you quoted and the results shown in your image. Have you left a couple of aging buckets (perhaps 121-180 and 181-365+) out of that formula?
Also, from your image, it looks like that data is in a formal Excel table. So, my guess is that you may be able to use this?

O_ch.xlsm
	A	B
1	Days_Overdue	Aging Bucket
2	25	16-30 Days Past Due
3	150	121-180 Days Past Due
4	100	91-120 Days Past Due
5	NULL	NULL
6	NULL	NULL
7	NULL	NULL
8	195	181-365+ Days Past Due
9	NULL	NULL
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=LOOKUP([@[Days_Overdue]],{0,16,61,91,121,181,""},{"0-15","16-30","61-90","91-120","121-180","181-365+","NULL"})&IF([@[Days_Overdue]]="NULL",""," Days Past Due")

 

[O_ch]

Try

=IF(A2="Null","Null",IF(A2>90,"91-120 Days Past Due ",IF(A2>60,"61-90 Days Past Due",IF(A2>15,"16-30 Days Past due",IF(A2>-0.1,"0-15 Days Past Due","0 Current")))))

Hi, and thank you! Excel formula posted has fixed "NULL" issue, all appears correct on columns.

 

[O_ch]

Hi O_ch,

Is this what you want?

O_ch.xlsx
	A	B
1	Days Overdue	Aging Bucket
2	25	16-30 Days Past due
3	25	16-30 Days Past due
4	25	16-30 Days Past due
5	25	16-30 Days Past due
6	NULL	NULL
7	NULL	NULL
8	NULL	NULL
9	NULL	NULL
10	NULL	NULL
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="NULL","NULL",IF(A2>90,"91-120 Days Past Due ",IF(A2>60,"61-90 Days Past Due",IF(A2>15,"16-30 Days Past due",IF(A2>-0.1,"0-15 Days Past Due","0 Current")))))

Hi, and thank you! Excel formula posted has fixed "NULL" issue, all appears correct on columns.

 

[O_ch]

Welcome to the MrExcel board!

There seems to be a discrepancy between the formula you quoted and the results shown in your image. Have you left a couple of aging buckets (perhaps 121-180 and 181-365+) out of that formula?
Also, from your image, it looks like that data is in a formal Excel table. So, my guess is that you may be able to use this?

O_ch.xlsm
	A	B
1	Days_Overdue	Aging Bucket
2	25	16-30 Days Past Due
3	150	121-180 Days Past Due
4	100	91-120 Days Past Due
5	NULL	NULL
6	NULL	NULL
7	NULL	NULL
8	195	181-365+ Days Past Due
9	NULL	NULL
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=LOOKUP([@[Days_Overdue]],{0,16,61,91,121,181,""},{"0-15","16-30","61-90","91-120","121-180","181-365+","NULL"})&IF([@[Days_Overdue]]="NULL",""," Days Past Due")

Hi, I tried your formula and I get #N/A. Wondering what could be the issue?

 

[O_ch]

Hi, I tried your formula and I get #N/A. Wondering what could be the issue?

Looked at Function Argument, and I see Logical Test displays "Invalid" !

 

[Sufiyan97]

Try this

=LOOKUP(A2,{0,16,61,91,121,181,""},{"0-15","16-30","61-90","91-120","121-180","181-365+","NULL"})&IF(A2="NULL",""," Days Past Due")

 

[O_ch]

Try this

=LOOKUP(A2,{0,16,61,91,121,181,""},{"0-15","16-30","61-90","91-120","121-180","181-365+","NULL"})&IF(A2="NULL",""," Days Past Due")

Thank you! All working properly. Closely looked at Insert Function Arguments Window and understood “Logical Test”, “Value_if_true”, “Value_if_false”.