# averaging

#### Derrell

##### New Member
Help averaging data between dates, problem is that the data is never on the same dates each month.
example:
1st 123
2nd
3rd
4th 345
5th
6th 500

and so forth for the month.

I need to know how to subtract the 4th from the 1st and average that data.

Derrell

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### fairwinds

##### MrExcel MVP
Welcome to the board!

Assuming your values are always ascending, try:

=AVERAGE(SMALL(B2:B7,ROW(INDIRECT("2:"&COUNT(B2:B7))))-SMALL(B2:B7,ROW(INDIRECT("1:"&COUNT(B2:B7)-1))),MIN(B2:B7))

Confirmed with Ctrl + Shift + Enter.
Book1
ABCDE
1
21st123
32nd166.6667
43rd
54th345
65th
76th500
8
Sheet3

#### Derrell

##### New Member
Back in Dec 2005 you were kind and really helped with the averaging, but now I have another averaging problem thats almost the same.
I need to know how to calculate the reading in the amount column and as before the readings are never on the same days month to month.

4/30----100--------11.5
5/1------123---------11.5
5/2------000---------74
5/3------000---------74
5/4------345---------74
5/5------000---------77.5
5/6------500---------77.5

Derrell

#### fairwinds

##### MrExcel MVP
Hi,

Even though I do not get the same result as you in the beginning (there is only one day between 30/4 and 1/5), my guess is that this is what you want:

=IF(B3,(B3-LOOKUP(2,1/(\$B\$2:B2>0),\$B\$2:B2))/(A3-LOOKUP(2,1/(\$B\$2:B2>0),\$A\$2:A2)),C4)

in C3 dragged down.
Book1
ABCD
24/30100
35/112323
45/2074
55/3074
65/434574
75/5077.5
85/650077.5
Sheet3

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,018
Messages
5,834,976
Members
430,331
Latest member
Syed Yasir Hannan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back