Aging Report

Options
Alexandra Prescott
Alexandra Prescott ✭✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    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.

    · Share on FacebookShare on Twitter

Answers

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

    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


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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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, "")

    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


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

    · Share on FacebookShare on Twitter
  • Alexandra Prescott
    Alexandra Prescott ✭✭✭✭
    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?

    Aging Error.jpg

    Thanks!

    Ali

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!