Tags: age, aging, balances, column, credit, current, excel, following, formula, formulas, microsoft, msdn, software
Aging Formula
On Microsoft » Microsoft Excel
4,130 words with 5 Comments; publish: Thu, 22 May 2008 05:47:00 GMT; (30678.13, « »)
Have the following formula for aging, would like for it to age credit
balances in the current column. Can I add something to these formulas to
make it work
Cell Q1 is date to age from, Cell G2 is invoice date, cell H2 is invoice
balance.
formula for Current column
=IF($Q$1>=($G2+1),IF($Q$1<=($G2+30),$H2,""),"")
formula for 1-30 Days Past Due
=IF($Q$1>=($G2+31),IF($Q$1<=($G2+60),$H2,""),"")
http://excel.itags.org/q_microsoft-excel_17554.html
All Comments
Leave a comment...
- 5 Comments
- What's wrong with what you have? It looks like a decent aging formula to me.
Regards,
Fred.
"Sue" <Sue.excel.itags.org.discussions.microsoft.com> wrote in message
news:1687D235-1820-4F01-B2A8-9E6649F360D3.excel.itags.org.microsoft.com...
> Have the following formula for aging, would like for it to age credit
> balances in the current column. Can I add something to these formulas
> to
> make it work
> Cell Q1 is date to age from, Cell G2 is invoice date, cell H2 is invoice
> balance.
> formula for Current column
> =IF($Q$1>=($G2+1),IF($Q$1<=($G2+30),$H2,""),"")
> formula for 1-30 Days Past Due
> =IF($Q$1>=($G2+31),IF($Q$1<=($G2+60),$H2,""),"")
>
#1; Thu, 22 May 2008 05:49:00 GMT
- I need to be able to tell if it the amount in cell H2 is less than 0(and
greater than 0) it goes in current column, in all the other aging columns the
amount has to be greater than 0
"Fred Smith" wrote:
> What's wrong with what you have? It looks like a decent aging formula to me.
> Regards,
> Fred.
> "Sue" <Sue.excel.itags.org.discussions.microsoft.com> wrote in message
> news:1687D235-1820-4F01-B2A8-9E6649F360D3.excel.itags.org.microsoft.com...
> > Have the following formula for aging, would like for it to age credit
> > balances in the current column. Can I add something to these formulas
> > to
> > make it work
> >
> > Cell Q1 is date to age from, Cell G2 is invoice date, cell H2 is invoice
> > balance.
> >
> > formula for Current column
> > =IF($Q$1>=($G2+1),IF($Q$1<=($G2+30),$H2,""),"")
> >
> > formula for 1-30 Days Past Due
> > =IF($Q$1>=($G2+31),IF($Q$1<=($G2+60),$H2,""),"")
> >
>
#2; Thu, 22 May 2008 05:50:00 GMT
- So if H2 is less than zero, or the age is less than 31 days, it's current,
right? If so, try:
=if(or($h2<=0,$q$1<=$g2+30),$h2,"")
In the aging columns, try:
=if(and($h2>0,$q$1>$g2+30,$q$1<=$g2+60),$h2,"")
Regards,
Fred.
"Sue" <Sue.excel.itags.org.discussions.microsoft.com> wrote in message
news:2BC6EF88-A0DE-4C63-8283-7A1B63D263A1.excel.itags.org.microsoft.com...
>I need to be able to tell if it the amount in cell H2 is less than 0(and
> greater than 0) it goes in current column, in all the other aging columns
> the
> amount has to be greater than 0
> "Fred Smith" wrote:
>> What's wrong with what you have? It looks like a decent aging formula to
>> me.
>> Regards,
>> Fred.
>> "Sue" <Sue.excel.itags.org.discussions.microsoft.com> wrote in message
>> news:1687D235-1820-4F01-B2A8-9E6649F360D3.excel.itags.org.microsoft.com...
>> > Have the following formula for aging, would like for it to age credit
>> > balances in the current column. Can I add something to these
>> > formulas
>> > to
>> > make it work
>> >
>> > Cell Q1 is date to age from, Cell G2 is invoice date, cell H2 is
>> > invoice
>> > balance.
>> >
>> > formula for Current column
>> > =IF($Q$1>=($G2+1),IF($Q$1<=($G2+30),$H2,""),"")
>> >
>> > formula for 1-30 Days Past Due
>> > =IF($Q$1>=($G2+31),IF($Q$1<=($G2+60),$H2,""),"")
>> >
>>
#3; Thu, 22 May 2008 05:51:00 GMT
- Thanks that worked wonderfully#4; Thu, 22 May 2008 05:52:00 GMT
- You're welcome. Glad I could help.
Fred.
"Sue" <Sue.excel.itags.org.discussions.microsoft.com> wrote in message
news:5F7E8CC8-4587-4D20-821A-22ED6860FCF6.excel.itags.org.microsoft.com...
> Thanks that worked wonderfully
>
#5; Thu, 22 May 2008 05:53:00 GMT