Aging Report

Alexandra Prescott

Hi there,

I am developing an invoice aging report and created an Aging column using =TODAY() - [Column Name]@row. I would now like to display the Payment Balance amounts in their appropriate Aging category (<30 Days, 31-60 Days, 61-90 Days, 91-120 Days, 121+ Days) according to the Aging column value. I’ve figured it out for the <30 Day and 121+ Day columns but the three “range” columns are giving me trouble. My last attempt below gave me an #INCORRECT ARGUMENT SET error. 

=IF(AND(Aging@row, >=31, <=60, [Payment Balance - FINANCE]@row))

Any suggestions?

Thanks,

Ali

Mark Cronk

Hi Alexandra,

Again, my fault. Found another error in my formulas. Sorry. Try these:

=IF(AND(aging@row >=31, aging@row <=60), [Payment Balance - FINANCE]@row, "")

=IF(AND(aging@row >=61, aging@row <=120), [Payment Balance - FINANCE]@row, "")

Work?

Mark

Mark Cronk

Hi @Alexandra Prescott ,

Without seeing your sheet this is a bit of a guess. Try:

=IF(AND(Aging@row, >=31, aging@row <=60, [Payment Balance - FINANCE]@row), "")

=IF(AND(Aging@row, >=61, aging@row <=120, [Payment Balance - FINANCE]@row), "")

Your <30 formula would be:

=IF(Aging@row, <=30, [Payment Balance - FINANCE]@row), "")

And, over 120:

=IF(Aging@row, >120, [Payment Balance - FINANCE]@row), "")

Help?

Mark

Mark Cronk

I misplaced a paren in each formula. Try:

=IF(AND(aging@row, >=31, aging@row <=60), [Payment Balance - FINANCE]@row, "")

=IF(AND(aging@row, >=61, aging@row <=120), [Payment Balance - FINANCE]@row, "")

Your <30 formula would be:

=IF(Aging@row <=30, [Payment Balance - FINANCE]@row, "")

And, over 120:

=IF(Aging@row >120, [Payment Balance - FINANCE]@row, "")

Mark

Alexandra Prescott

Hi @Mark Cronk ,

Thanks for getting back to me so quickly. The formulas in the <30 Days and 121+ Days columns work. I entered the ones you suggested above in the other columns and got an #INVALID DATA TYPE error. See a screenshot of my sheet below. Any other suggestions?

Thanks!

Ali

Mark Cronk

Hi Alexandra,

Again, my fault. Found another error in my formulas. Sorry. Try these:

=IF(AND(aging@row >=31, aging@row <=60), [Payment Balance - FINANCE]@row, "")

=IF(AND(aging@row >=61, aging@row <=120), [Payment Balance - FINANCE]@row, "")

Work?

Mark