Aging Report

Options

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
edited 01/13/21
Options

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), "")

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

And, over 120:

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

Help?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
edited 01/13/21
Options

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, "")

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

And, over 120:

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

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Options

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

• ✭✭✭✭✭✭
Options

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!