Forum Discussion
Autofill formula in filtered column
Hi everyone! So I have a list of transactions for a business; in column A is the date of the transaction, there is various (unrelated) info in subsequent columns (B - K), and column L is the type...
Remove the filter, if any.
In M2, enter the formula
=IFERROR(A2-XLOOKUP(L2,L$1:L1,A$1:A1,"",0,-1),"")
Format M2 as General.
Fill down to the end of the data. The formula returns the difference in days between the date in the current row and the previous date for the same category.
You can then use AVERAGEIFS (or AVERAGEIF) to calculate the average:
Hi Hans!
To the rescue as always! Had to tweak a little of the numbers to fit (I wasn’t accurate on my columns in my post, just gave a rough idea!) but have now sorted the problem!
Thank you 😊 I just would have thought Excel would allow you to auto fill a formula down a filtered column?! Surely it must be needed everyday?
Thanks again.
To the rescue as always! Had to tweak a little of the numbers to fit (I wasn’t accurate on my columns in my post, just gave a rough idea!) but have now sorted the problem!
Thank you 😊 I just would have thought Excel would allow you to auto fill a formula down a filtered column?! Surely it must be needed everyday?
Thanks again.