0
votes

I need to perform an excel task of doing [(cash + card + check) - part cost] and then finding the average between a certain set of dates to receive the average profit given a certain amount of time. This should not be too difficult, as the on paper formula is easy and I believe dates in excel are numerical in value, but I cannot seem to figure it out. Here's an image to better aid you in understanding:

enter image description here

As you can see on the left is a "Date" column located at column A. And a bit to the right is this:

enter image description here

All I need to do is, within a certain date range, add cash + card + check and subtract that total by the part cost column. The issue is not being able to add all of the sums of the columns and subtract them by part cost, the issue is coming up with a formula that finds the average based on the number of rows between a certain set of dates, decided by the Week Start and Week End input box on column Q.

I know I need to do something along the lines of =IF(A:A >= Q1 and A:A <= Q2, AVERAGE((K:K + M:M + N:N)-H:H),0). If you understand what I'm trying to get at, please assist me.

Recap: All columns K, L, and M within an input date range need to be added together and subtracted by all column H values that are within the date range, and then I need the average of them. So the average of (K + L + M) - H within a specified date range.

1
are you looking for average per day worked, or average per day, regardless of if there were receipts on that day or not?SeanC
No, an average of the whole selected date range's numbers.user3059686

1 Answers

0
votes

For the example provided, because 7/14/2015 is the only selected date, it will return the average based on 1 day.

I'm sure there is a more elegant way of doing this. Adjust the ranges as needed and the formula needs to be entered with Control + Shift + Enter.

(SUM(IF($A$5:$A$9>=$Q$1,IF($A$5:$A$9<=$Q$4,$L$5:$N$9)))-SUM(IF($A$5:$A$9>=$Q$1,IF($A$5:$A$9<=$Q$4,$H$5:$H$9))))/(MAX(IF($K$5:$K$9<>"",IF($A$5:$A$9<=$Q$4,$A$5:$A$9)))-MIN(IF($K$5:$K$9<>"",IF($A$5:$A$9>=$Q$1,$A$5:$A$9)))+1)

Let me know if this is your desired result.