Autofill formula in filtered column

Copper Contributor

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 of expense that it was (stationary, fuel etc). 

 

I have filtered the L column to show just fuel, and I want to work out the number of days between the dates. I've got no issues with how to do the formula, but when I try to copy the formula down it is all thrown out by all the hidden cells. I want the formula to take only visible cells into account. This isn't a permanent thing, I just want to see all the days between, workout the average (again I know what formula to use) and then delete these formulas. I just want to know the average days quickly so I can plan that data into the next business year for the company!

 

I've tried all sorts of ways I have found online, pasting with only visible cells etc etc, but cannot seem to get it right.

 

Can anyone help?

 

Thank you! :)

 

 

4 Replies

@Andrew_Hinson 

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.

HansVogelaar_0-1695148108108.png

You can then use AVERAGEIFS (or AVERAGEIF) to calculate the average:

HansVogelaar_1-1695148226381.png

Hi, hope you are doing great!
I have tried your problem, but no issue found on my side so, if possible, kindly share your or display your file so I can guide you accordingly.
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 :smiling_face_with_smiling_eyes: 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.
Hi Arslan,
I have sorted now thanks to Hans as above! Thank you for your input though :smiling_face_with_smiling_eyes: