Get a daily use average?

[tl7612]

I have a monthly spreadsheet that numbers are keyed in from readings that are done on different days of the month. The spreadsheet has the dates in across the top in row 1 and the locations in the rows below. For example, I would have a reading on April 8th for 83,055,000 and then on April 15th for 83,203,000. I of course could do the the math and get the daily average between those two numbers by substracting the two and divide by 7. That would get me a daily average use of 21,143. However, I will have more dates keyed in and would like a formula to figure out what the daily average use would be no matter what dates entered.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[tl7612]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

365 is the version.

 

[Fluff]

In that case, assuming only two values per row, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		08/04/2021	09/04/2021	10/04/2021	11/04/2021	12/04/2021	13/04/2021	14/04/2021	15/04/2021	16/04/2021	17/04/2021	18/04/2021	19/04/2021
2		83055000							83203000					21142.86
3				21000		33000								6000
Main

Cell Formulas
Range	Formula
N2:N3	N2	=(MAX(B2:M2)-MIN(B2:M2))/(MAXIFS($B$1:$M$1,B2:M2,"<>")-MINIFS($B$1:$M$1,B2:M2,"<>"))

 

[tl7612]

No there will be multiple amounts on each row. However, each number should increase in each reading.

 

[Fluff]

Do you just want the average between the largest & smallest based on their dates?

 

[tl7612]

Yes that should work

 

[Fluff]

In that case the formula I suggested should still work.

 

[tl7612]

The formula works but the problem is at the end of the month it divides by the number of days before the last day of the month. For example in April there are 30 days and the formula divides by 29 days instead of 30 days. There would always be a reading on the first day of the month and the last day.

 

[Fluff]

That is not what you showed in your op, but you can just add 1 to the formula like

=(MAX(B2:M2)-MIN(B2:M2))/(MAXIFS($B$1:$M$1,B2:M2,"<>")-MINIFS($B$1:$M$1,B2:M2,"<>")+1)