Excel formula for average daily profit

Question

I have a table that has just two columns, Date and Profit:

Date            Profit
24 - March      25.00
24 - March      19.85
25 - March      5.25
26 - March      6.36
26 - March      10.18
26 - March      35.63

I want to calculate and display the average daily profit in one cell.

Please keep in mind that when I enter data for new dates such as 27 - March and it's profit, it should update the average daily profit cell.

Some days I do not make any sale; for example, if I make sales and profit for march 24, march 25 and march 27, I did not make a sale on March 26, I want it to calculate it regardless of me making a sale on a date or not. Meaning in a month that has 31 days, it should divide by 31 regardless on whether I made a sale on a specific date or not. Would that be possible?

Solution

For dates in cells A1:A25 and profit values in cells B1:B25 you can use the following formula:

=SUM(B2:B25)/SUMPRODUCT(--(FREQUENCY(A2:A25,A2:A25)>0))

Result as shown:

When you have more than 25 rows of data you'll need to adjust the formula ranges appropriately.

The formula calculates as follows:

(Total profit)/ (number of different dates)

EDIT

As per request, to include dates in between (not specified) you might use something like this:

=SUM($B$2:$B$25)/(MAX($A$2:$A$25)-MIN($A$2:$A$25)+1)

(Total profit)/ (days between smallest and largest date in range)

Comments

[Jason Aller]

To clarify, are you looking for a formula that will handle the input from the first day entered to the last day entered, or to the current day? Do you want to be able to have a cumulative calculation, or ones for each month?

[CallumDA]

possible duplicate of Excel Daily Average Profit

[fixer1234]

It looks like you posted the same question twice on the same day. There is more explanation here, but there is an upvoted answer on the other and two substantive answers here. The questions should be merged if they are the same. If an existing answer solves the problem, please accept it. If none do, please clarify why so you can get a better answer. If the two questions are different, please edit one or both to better differentiate them. In that case, it would be helpful to mention the other question and describe what is different.

[Yavaar Nosimohomed]

Thank you so much for your help..it works...however i found a little problem...some days i do not make any sale....for example, it i make sales and profit for march 24, march 25 and march 27...the formula will only divide the profit by 3 because i did not make any sale on March 26.....i want it to calculate it regardless of me making a sale on a date or not..meaning a month that has 31 days, it should divide by 31 regardless on wether i made a sale on a specific date or not...would that be possible? thanks

[CallumDA]

I've updated my answer. Let me know how that works. If this solves your issue please mark as accepted answer to close the question.

[CallumDA]

Would you say that TODAY()-MIN(A2:A7 gives one too few days? consider the first two days of the month. 2/4/2015-1/4/2015 = 1.