Developing Aging report-problem with 'if' statement

[Bsting]

I am trying to come up a formula that will look across a row to
1.) see if the cell in N for that row is empty and
2.) if it is empty to look at the date in colum g and
3.)if the date is more than 30, 31-60, etc days past today return the amount
in H to the corresponding cell in J.
Currently, if I enter the formula on each row I am able to display the
correct information but I am hoping to be able to come up with a way to just
have to enter one formula for the entire column. There will be information
added to the worksheet and as cells are populated in column N it will change
the later aging.

Any help is greatly appreciated

Becky


G H I J K
L N
Due Date Amt Current >30 31-60 61-90
Paid Date
2/8/2009 154.00 154.00
2/9/2009 1427.95 1427.95
3/16/2009
2/18/2009 100.00 100.00
5/1/2009

 

[smartin]

I am trying to come up a formula that will look across a row to
1.) see if the cell in N for that row is empty and
2.) if it is empty to look at the date in colum g and
3.)if the date is more than 30, 31-60, etc days past today return the amount
in H to the corresponding cell in J.
Currently, if I enter the formula on each row I am able to display the
correct information but I am hoping to be able to come up with a way to just
have to enter one formula for the entire column. There will be information
added to the worksheet and as cells are populated in column N it will change
the later aging.

Any help is greatly appreciated

Becky


G H I J K
L N
Due Date Amt Current >30 31-60 61-90
Paid Date
2/8/2009 154.00 154.00
2/9/2009 1427.95 1427.95
3/16/2009
2/18/2009 100.00 100.00
5/1/2009

I'm not certain I interpreted your example correctly due to line
wrapping. Hopefully this gives you some ideas.

The solution is made simpler by placing a row of values somewhere that
correspond to the aging buckets. E.g., insert in row 2:

J K L
insert row of values --> 0 31 61
<30 31-60 61-90

....where the values are the lowest numbers that qualify for the range in
question.

Also, for ease of example, calculate the age of the claim in some
convenient column. I used Q:

Q4 = TODAY()-G4

Let's say you want a formula to fill the aging buckets in J4:L6. In J4,
fill down and right:

=MATCH($Q4,$J$2:$L$2,1)

This says which bucket the age (in Q) belongs in because MATCH returns
the placement of the largest item in row 2 not exceeding the age.

Expand on that with this new formula in J4 (fill right and down):

=IF(COLUMN()-10=MATCH($Q4,$J$2:$L$2,1),$H4,0)

Now if the column number less 10 (assuming you are starting in column J)
equals the bucket assignment, grab column H, else put 0.