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​

 

[O_ch]

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

Correction needed!! After inserting your functional formula all ROWS listed "NULL Days Past Due", however, ater closely looking at Excel Function Argument there is no Invalid error, I am guessing formula is not reading column rows correctly.
Will need to get fixed!

 

[O_ch]

Correction needed!! After inserting your functional formula all ROWS listed "NULL Days Past Due", however, ater closely looking at Excel Function Argument there is no Invalid error, I am guessing formula is not reading column rows correctly.
Will need to get fixed!

I have several Days Overdue in "negative" such as -25 and if I set your formula on column B2 I get #N/A.

Formula being utilized that gives me error:
=LOOKUP(A13,{0,16,61,91,121,181,""},{"0-15","16-30","61-90","91-120","121-180","181-365+","NULL"})&IF(A13="NULL",""," Days Past Due")

 

[Peter_SSs]

I have several Days Overdue in "negative" such as -25

What result do you want for those rows?

 

[O_ch]

What result do you want for those rows?

For negative values such as -25, I would like to see, "-0 Results".

 

[Peter_SSs]

For negative values such as -25, I would like to see, "-0 Results".

Then try

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

However (I have only just noticed) you seem to be either missing yet another category or have a wrong range in your original formula (all the same in all the subsequent formulas. There does not seem to be any allowance for the range from 31-60 days. Surely it either needs another range or else the 16-30 or 61-90 ranges need to include those extra numbers.

So the formula might need to be like this instead?

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

Also, since the number of categories is quite large and the formula pretty long, perhaps you could consider including a lookup table in your worksheet, like this?

O_ch.xlsm
	A	B	C	D	E
1	Days_Overdue	Aging Bucket		Lookup
2	25	16-30 Days Past Due		-9999	-0 Results
3	40	31-60 Days Past Due		0	0-15 Days Past Due
4	100	91-120 Days Past Due		16	16-30 Days Past Due
5	NULL	NULL		31	31-60 Days Past Due
6	5	0-15 Days Past Due		61	61-90 Days Past Due
7	70	61-90 Days Past Due		91	91-120 Days Past Due
8	195	181-365+ Days Past Due		121	121-1805 Days Past Due
9	121	121-1805 Days Past Due		181	181-365+ Days Past Due
10	-25	-0 Results		"NULL"	NULL
11	0	0-15 Days Past Due
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=VLOOKUP(A2,$D$2:$E$10,2)

 

[O_ch]

Then try

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

Hi, after testing your formula, all rows read, NULL Days Past Due.

 

[Peter_SSs]

Did the earlier formulas actually work?

Notice also that I edited my previous post for another couple of suggestions (though I don't think that they will help with this latest problem)
Are the values being looked up actual numbers or text values?

 

[O_ch]

Did the earlier formulas actually work?

Notice also that I edited my previous post for another couple of suggestions (though I don't think that they will help with this latest problem)
Are the values being looked up actual numbers or text values?

Hi, Values are being looked up as numbers.
Earlier formulas did work.
Testing your VLOOKUP.
Thank you

 

[Peter_SSs]

Hi, Values are being looked up as numbers.

Here is one of the previous forms of the formula working.
If you are not getting results like these then there must be something different about your data or formula. If we were to try to see what was happening we would need some sample data with XL2BB or a link to a publicly shared file on DropBox, One Drive etc.

BTW, you can copy my test data and formulas from my mini sheet below by clicking the

icon near the top left and pasting into cell A1 of a blank worksheet.

O_ch.xlsm
	A	B
1	Days_Overdue	Aging Bucket
2	25	16-30 Days Past Due
3	40	31-60 Days Past Due
4	100	91-120 Days Past Due
5	NULL	NULL
6	5	0-15 Days Past Due
7	70	61-90 Days Past Due
8	195	181-365+ Days Past Due
9	121	121-180 Days Past Due
10	-25	-0 Results
11	0	0-15 Days Past Due
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=LOOKUP(A2,{-9999,0,16,31,61,91,121,181,""},{"-0 Results","0-15","16-30","31-60","61-90","91-120","121-180","181-365+","NULL"})&IF(OR(A2="NULL",A2<0),""," Days Past Due")

 

[O_ch]

Here is one of the previous forms of the formula working.
If you are not getting results like these then there must be something different about your data or formula. If we were to try to see what was happening we would need some sample data with XL2BB or a link to a publicly shared file on DropBox, One Drive etc.

BTW, you can copy my test data and formulas from my mini sheet below by clicking the View attachment 49563 icon near the top left and pasting into cell A1 of a blank worksheet.

O_ch.xlsm
	A	B
1	Days_Overdue	Aging Bucket
2	25	16-30 Days Past Due
3	40	31-60 Days Past Due
4	100	91-120 Days Past Due
5	NULL	NULL
6	5	0-15 Days Past Due
7	70	61-90 Days Past Due
8	195	181-365+ Days Past Due
9	121	121-180 Days Past Due
10	-25	-0 Results
11	0	0-15 Days Past Due
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=LOOKUP(A2,{-9999,0,16,31,61,91,121,181,""},{"-0 Results","0-15","16-30","31-60","61-90","91-120","121-180","181-365+","NULL"})&IF(OR(A2="NULL",A2<0),""," Days Past Due")

Hi, your updated Excel formula is correct and it all displays perfect on my Excel sheet. We can close this one!
Thank you for the help, very much appreciated.