Calculating the average difference between a series of numbers

[wynandbecker]

Hi, I am trying to calculate the average mileage done per company vehicle. Since there will be an initial reading, I thought it would be best to try and calculate the average difference between odometer readings, and ignoring the blank cells from future readings.

I apologize XL2BB won't paste, this is what it looks like:

 

[GraH]

Try using the aggregate function.

=AGGREGATE(1,4,J2:O2)

 

[wynandbecker]

Try using the aggregate function.

=AGGREGATE(1,4,J2:O2)

Hi, that comes back with 10942.5, which is just the average between the 4 existing readings..

 

[GraH]

Seems I missed the point here.
Can you add a correct calculation you do manually , so we have a reference?

 

[wynandbecker]

Seems I missed the point here.
Can you add a correct calculation you do manually , so we have a reference?

WEEK1(INITIAL): 10540
WEEK2: 10870
WEEK3: 11010
WEEK4: 11350

WEEK1:10870-10540: 330
WEEK2: 11010-10870: 140
WEEK3:11350-11010: 340

AVERAGE DIFFERENCE: (330+140+340)/3=270 per week

And there are 20 weeks in total, so it must ignore those blank future ones until they are put in.

 

[GraH]

You may try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2							270			10540	10870	11010	11350
3
4
5
Sheet2

Cell Formulas
Range	Formula
G2	G2	=AVERAGE(IFERROR(K2:INDEX(K2:AC2,COUNT(K2:AC2))-J2:INDEX(J2:AC2,COUNT(J2:AC2)),""))

 

[wynandbecker]

You may try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2							270			10540	10870	11010	11350
3
4
5
Sheet2

Cell Formulas
Range	Formula
G2	G2	=AVERAGE(IFERROR(K2:INDEX(K2:AC2,COUNT(K2:AC2))-J2:INDEX(J2:AC2,COUNT(J2:AC2)),""))

Thank you very much!

 

[GraH]

Most welcome. Thanks for the mark.