Dear All,



I hope everyone is doing well and good. It is so nice to join the forum.

However, I am in need of your excel expertise to solve my problem.

Problem:

I want to calculate the daily average sales for each product from the first cell with date to TODAY().

Attached is the file containing formulas done manually.

Regards.

Attached Files

Excel Book.xlsx (12.8 KB, 1 views)

Hope you have Office 365!:
In the attached a long formula in cell T6 which can be copied down. The results match your expected results.
It requires the dates in row 4 to be real dates, not strings (which is what you have)

Be aware, that from your instructions, that for each row, we only start taking an average from the leftmost non-blank entry in the Qty columns. Thereafter, each blank Qty column is assumed to be 0, for averaging purposes. So this assumes that the first date a product became on sale it sold more than zero, and that it wasn't on sale prior to that first sale; but will that always be the case? Will a product always sell on its first day?
It would be more robust (and end up with an easier formula) if there was an actual zero in the Qty column if the item was on sale and sold none; it would make determining when a product was first avilable easier and would make the averages correct if the item sold 0 on its first day.

You can use that long formula, but I've also converted it into a lambda function which is much easier to use, see cell U6. Here you get a simple formula:
=DailyAverageSales(D6:Q6,$D$5:$Q$5,$D$4:$Q$4)
with hints as to what goes where in the formula as you enter it:


2023-07-14_214839.jpg

Attached Files

msofficeforums51105Excel Book.xlsx (14.8 KB, 1 views)