averaging

ndello

Active Member
Joined
Oct 16, 2002
Messages
382
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,449
Messages
5,724,414
Members
422,552
Latest member
Ajit Kumar_1982

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