Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If function
I'm trying to set up a worksheet to age my accounts payable. I want to
determine 30 days, 60 days, 90 days etc. My formula is: =if((today()-b2)30,"30 days overdue") Where b2 is the cell w/ my invoice date in it, and "30 days overdue" appears if the test is true. However, I need to insert tests for 60, 90, and 120 days as well. My help index talks about nested if functions, and even gives an example, but when I try to type it that way, it just gives me an error message! Help!! TIA |
#2
|
|||
|
|||
To nest your IFs you would use the following formula:
=IF(TODAY()-B2120,"120 days overdue",IF(TODAY()-B290,"90 days over due",IF(TODAY()-B260,"60 days overdue",IF(TODAY()-B230,"30 days overdue","not overdue")))) An easier way if you only want to see over due in 30 day increments you could use the following formula: =IF(TODAY()-B2=30,IF(MOD(TODAY()-B2,30)=0,TODAY()-B2&" days overdue","overdue but not on 30 day increments."),"not overdue.") The second one will work for 30,60,90,120, 150, 180, ... Ryan "BetsyW" wrote: I'm trying to set up a worksheet to age my accounts payable. I want to determine 30 days, 60 days, 90 days etc. My formula is: =if((today()-b2)30,"30 days overdue") Where b2 is the cell w/ my invoice date in it, and "30 days overdue" appears if the test is true. However, I need to insert tests for 60, 90, and 120 days as well. My help index talks about nested if functions, and even gives an example, but when I try to type it that way, it just gives me an error message! Help!! TIA |
#3
|
|||
|
|||
Hi Betsym
Try this instead of nested "If's" =IF(TODAY()-B2<30,0,INDEX({30,60,90,120},1,MATCH(TODAY()-B2,{30,60,90,120},1)))&" Days Overdue" HTH Alan P. "BetsyW" wrote in message ... I'm trying to set up a worksheet to age my accounts payable. I want to determine 30 days, 60 days, 90 days etc. My formula is: =if((today()-b2)30,"30 days overdue") Where b2 is the cell w/ my invoice date in it, and "30 days overdue" appears if the test is true. However, I need to insert tests for 60, 90, and 120 days as well. My help index talks about nested if functions, and even gives an example, but when I try to type it that way, it just gives me an error message! Help!! TIA |
#4
|
|||
|
|||
Betsy
One mo =IF(TODAY()-B2<30,"", INT((TODAY()-B2)/30)*30&" days overdue.") -- Best Regards Leo Heuser Followup to newsgroup only please. "BetsyW" skrev i en meddelelse ... I'm trying to set up a worksheet to age my accounts payable. I want to determine 30 days, 60 days, 90 days etc. My formula is: =if((today()-b2)30,"30 days overdue") Where b2 is the cell w/ my invoice date in it, and "30 days overdue" appears if the test is true. However, I need to insert tests for 60, 90, and 120 days as well. My help index talks about nested if functions, and even gives an example, but when I try to type it that way, it just gives me an error message! Help!! TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging function | Excel Discussion (Misc queries) | |||
Excel function help facilities | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
need to save values from a function before it changes | Excel Worksheet Functions | |||
SUMIF(AND) FUNCTION | Excel Worksheet Functions |