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

O_ch

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

O_ch

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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

O_ch

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,557
Office Version
  1. 365
Platform
  1. Windows
For negative values such as -25, I would like to see, "-0 Results".
Then try

Excel Formula:
=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?

Excel Formula:
=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
ABCDE
1Days_OverdueAging BucketLookup
22516-30 Days Past Due-9999-0 Results
34031-60 Days Past Due00-15 Days Past Due
410091-120 Days Past Due1616-30 Days Past Due
5NULLNULL3131-60 Days Past Due
650-15 Days Past Due6161-90 Days Past Due
77061-90 Days Past Due9191-120 Days Past Due
8195181-365+ Days Past Due121121-1805 Days Past Due
9121121-1805 Days Past Due181181-365+ Days Past Due
10-25-0 Results"NULL"NULL
1100-15 Days Past Due
Sheet2
Cell Formulas
RangeFormula
B2:B11B2=VLOOKUP(A2,$D$2:$E$10,2)
 
Last edited:
Solution

O_ch

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

Excel Formula:
=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. :confused:
 

Peter_SSs

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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,557
Office Version
  1. 365
Platform
  1. Windows
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
1634864634831.png
icon near the top left and pasting into cell A1 of a blank worksheet.

Cell Formulas
RangeFormula
B2:B11B2=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

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

Cell Formulas
RangeFormula
B2:B11B2=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.
 

Forum statistics

Threads
1,176,014
Messages
5,900,891
Members
434,857
Latest member
lowiscoetzee

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