Forum Discussion
Excel formula for Ageing
Hi,
I am trying to come up with a formula to net down aged data but I am struggling!
This is a sample from my pivot, I want to run the formula alongside it and what I am trying to do is:
For customer 1 to net off the -735 from 4 Years+ with the 735 in 90-180 so it only shows 735 left in 60-90 days. Then when I refresh the pivot each month these will adjust and then the ageing formula will adjust alongside it.
Thanks,
James
2 Replies
Ensure you adjust the formula to match your actual cell references. Change Customer 1 to whatever customer you have in mind, or adapt the formula to use cell references for this. Using this summary method allows your formula to adjust as your pivot table data refreshes.
When you refresh the pivot table, make sure your ranges for age categories and amounts remain consistent, as the formula refers to those exact values through VLOOKUP. Adjust the ranges as needed if you change the structure or add more rows or columns.
