Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging Formula Help
I have a worksheet with column AD currently showing the number of days based
upon todays date they are past due. I have the following criteria that I want the formula to return in column AC if the number in columns D is between those numbers. Days: 0-30 31-60 61-120 121-180 181-240 241-300 365+ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging Formula Help
=COUNTIF(AD:AD,"<=30")
=COUNTIF(AD:AD,"<=60")-COUNTIF(AD:AD,"<=30") etc. -- __________________________________ HTH Bob "klmiura" wrote in message ... I have a worksheet with column AD currently showing the number of days based upon todays date they are past due. I have the following criteria that I want the formula to return in column AC if the number in columns D is between those numbers. Days: 0-30 31-60 61-120 121-180 181-240 241-300 365+ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging Formula Help
I assume you mean column AD and not D?
=VLOOKUP(AD1,{0,"0-30";31,"31-60";61,"61-120";121,"121-180";181,"181-300";241,"241-300";301,"365+"},2) replace AD1 with the cell you want to use copy down -- Regards, Peo Sjoblom "klmiura" wrote in message ... I have a worksheet with column AD currently showing the number of days based upon todays date they are past due. I have the following criteria that I want the formula to return in column AC if the number in columns D is between those numbers. Days: 0-30 31-60 61-120 121-180 181-240 241-300 365+ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging Formula Help
Hi,
Modify your table to look like this: AA_____AB 0______0-30 31_____31-60 61_____61-120 121____121-180 181____181-240 241____241-300 301____301-365 365____365+ If this table is in AA1:AA8, then: =VLOOKUP(D2,$AA$1:$AA$8,2) Regards - Dave. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Aging Formula Help
In A2:A8 put the following:
0 31 61 121 181 241 365 In D1, put this: =IF(A2="","",SUM($A$2:A2)-A2+1&IF(A2=1,""," - "&SUM($A$2:A2))) Fill down. Regards, Ryan--- -- RyGuy "Dave" wrote: Hi, Modify your table to look like this: AA_____AB 0______0-30 31_____31-60 61_____61-120 121____121-180 181____181-240 241____241-300 301____301-365 365____365+ If this table is in AA1:AA8, then: =VLOOKUP(D2,$AA$1:$AA$8,2) Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aging Formula | Excel Discussion (Misc queries) | |||
Aging Formula | Excel Discussion (Misc queries) | |||
aging formula | Excel Discussion (Misc queries) | |||
aging | Excel Worksheet Functions | |||
WHAT FORMULA DO I USE FOR AGING A STATEMENT | Excel Worksheet Functions |