averaging

ndello

Active Member
I have 12 cells (say a1 to a12) all with values in them. I want to get the average of the cells but only from jan to sep as that is where we are now. Each month the range changes to where we are.
If I use =AVERAGE and range it through the 12 cells, it will give me an average on all twelve, even though oct to dec has zero.
I cannot remove the zero as there is a cell reference in it.
Any ideas
Thanks
Neil
ps. hope this makes sense.

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

IML

MrExcel MVP
maybe something along the lines of
=AVERAGE(OFFSET(A1,0,0,MONTH(NOW())))

or
=AVERAGE(OFFSET(A1,0,0,b2))
where b2 hold the number of months you want

MrExcel MVP
On 2002-10-18 13:43, ndello wrote:
I have 12 cells (say a1 to a12) all with values in them. I want to get the average of the cells but only from jan to sep as that is where we are now. Each month the range changes to where we are.
If I use =AVERAGE and range it through the 12 cells, it will give me an average on all twelve, even though oct to dec has zero.
I cannot remove the zero as there is a cell reference in it.
Any ideas
Thanks
Neil
ps. hope this makes sense.

See...
Book5
ABCDEFGHIJKLM
1Oct
2JanFebMarAprMayJunJulAugSepOctNovDec
35754798987006.9
4
Sheet1

The essential formula is:

=AVERAGE(INDEX(A3:L3,1,1):INDEX(A3:L3,1,MATCH(M1,A2:L2,0)))

where A3:L3 is monthly value range and A2:L2 is the range housing the month names. M1 holds the name of the current month.

Replies
6
Views
533
Replies
2
Views
399
Replies
3
Views
415
Replies
2
Views
286
Replies
16
Views
614

1,181,929
Messages
5,932,843
Members
436,865
Latest member
JHusk

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?

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

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