averaging

Derrell

New Member
Joined
Dec 12, 2005
Messages
2
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.

Thanks for your help,
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
Joined
May 15, 2003
Messages
8,638
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
Joined
Dec 12, 2005
Messages
2
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.

date---reading----amount
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

Thanks for your help again,
Derrell
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
1datereadingamount
24/30100
35/112323
45/2074
55/3074
65/434574
75/5077.5
85/650077.5
Sheet3
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top