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

O_ch

New Member
Joined
Jan 7, 2021
Messages
14
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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​
 

Attachments

  • 476346475Mr.png
    476346475Mr.png
    21.6 KB · Views: 20

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try

Excel Formula:
=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")))))
 
Upvote 0
Upvote 0
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
AB
1Days_OverdueAging Bucket
22516-30 Days Past Due
3150121-180 Days Past Due
410091-120 Days Past Due
5NULLNULL
6NULLNULL
7NULLNULL
8195181-365+ Days Past Due
9NULLNULL
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=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")
 
Upvote 0
Try

Excel Formula:
=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.
 
Upvote 0
Hi O_ch,

Is this what you want?

O_ch.xlsx
AB
1Days OverdueAging Bucket
22516-30 Days Past due
32516-30 Days Past due
42516-30 Days Past due
52516-30 Days Past due
6NULLNULL
7NULLNULL
8NULLNULL
9NULLNULL
10NULLNULL
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=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.
 
Upvote 0
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
AB
1Days_OverdueAging Bucket
22516-30 Days Past Due
3150121-180 Days Past Due
410091-120 Days Past Due
5NULLNULL
6NULLNULL
7NULLNULL
8195181-365+ Days Past Due
9NULLNULL
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=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?
 
Upvote 0
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" !
 
Upvote 0
Try this

Excel Formula:
=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")
 
Upvote 0
Try this

Excel Formula:
=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”.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top