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

O_ch

New Member
Joined
Jan 7, 2021
Messages
12
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: 11

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
529
Office Version
  1. 2013
Platform
  1. Windows
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")))))
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,173
Office Version
  1. 2016
Platform
  1. Windows

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,769
Office Version
  1. 365
Platform
  1. Windows
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")
 

O_ch

New Member
Joined
Jan 7, 2021
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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.
 

O_ch

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

O_ch

New Member
Joined
Jan 7, 2021
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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?
 

O_ch

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

Well-known Member
Joined
Apr 12, 2019
Messages
529
Office Version
  1. 2013
Platform
  1. Windows
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")
 

O_ch

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

Forum statistics

Threads
1,147,924
Messages
5,743,894
Members
423,824
Latest member
leahpatterson

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
Top