# extraordinary average formula !!

#### gaftalik

##### Well-known Member
Hello everybody!

1)Is there any formula to put in K6 which may calculates the average of the last 3 months ! should it be a macro or a formula ?
I need also a macro to calculate it automatically each time i add a month data column!

2) what is the right formula to average only the months of the year 2004 ?

Thank you very much .
Book1
BCDEFGHIJKLM
3
4
5nov.03dec.03jan.04feb.04mar.04apr.04mai.04jun.04Averagein2004Averagelast3months
63021384011262414#VALUE!??
786372326372710
8115383132273119
96341012295214
10189742881924
11
12
13
Sheet1

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Yeah the dates are true dates ,Jan 04, feb 04 etc...

I think this works for both instances, or at least until the end of 2004. It relies on always keeping one blank column of data. So as you add a month, insert a new column to keep the blank.

You can seem to see the formula in K3 which was

=AVERAGE(OFFSET(J3,0,-4,1,3)).

However, probably better sticking with Aladdins anyway.
Book1
ABCDEFGHIJK
2nov.03dec.03jan.04feb.04mar.04apr.04mai.04jun.04Averagein2004Averagelast3months
363021384011262426.6666666720.3333333
4786372326372710
58115383132273119
696341012295214
710189742881924
Sheet1

Dear Mr.Juan, Thank you very much for your guidance , Dear Mr.Aladin! my good Genie !you always knock me down from the first 5 seconds
your formulas worked excelently, milions of thanks.
Mr.Gord i couldnt find your formulas ?!

Dear Gord,

I am very sorry for omitting something in your formula, i found that it is workable too !

One more time thanks all of you for your time

Hello Again,
I encountered a problem which i thought i could solve myself but that was impossible

What is the formula in Z8 to return the average of the "total"columns of the last 3 months , even if each time i add a new data column .

The answer in this case is 41

Thank you a lot
question abt average.xls
BCDEFGHIJKLMNOPQRST
5averagelast3
6mar.04apr.04mai.04iun.04iul.04aug.04
7mar.04mar.04mar.04apr.04apr.04apr.04mai.04mai.04mai.04iun.04iun.04iun.04iul.04iul.04iul.04aug.04aug.04aug.04
8TUtotalTUtotalTUtotalTUtotalTUtotalTUtotal??
9981719284745145914317
1048105837377429938231942
11215071124254163652114960
12323971443074123850491867
13471966154358282351224
14
2nd

Sorry i made a mistake the formula should be in T8 not Z8 !

I appreciate anyone's help , thank you.

gaftalik said:
Sorry i made a mistake the formula should be in T8 not Z8 !

I appreciate anyone's help , thank you.

I'm putting them all in T. You can drag and drop them in Z...
aaAverages gaftalik.xls
BCDEFGHIJKLMNOPQRST
4averagelast3
54
6mar.04apr.04mai.04iun.04iul.04aug.0412
7mar.04mar.04mar.04apr.04apr.04apr.04mai.04mai.04mai.04iun.04iun.04iun.04iul.04iul.04iul.04aug.04aug.04aug.04
8TUtotalTUtotalTUtotalTUtotalTUtotalTUtotal
9981928451414341
1048103737299231951.33333
11215012421636114955.33333
12323944301238491863.66667
134719154328232237.66667
14
Sheet2

T4 houses the last N param, which is here 3.

Formulas...

T5:

=(T6-3*T4)+1

Computes the pos of the start cell of the first block of 3.

T6:

=MATCH(9.99999999999999E+307,B9:S9)

Computes the pos of the last numeric value.

T9, which is copied down...

=AVERAGE(IF((MOD(COLUMN(INDEX(\$B9:\$S9,\$T\$5):INDEX(\$B9:\$S9,\$T\$6))-CELL("Col",INDEX(\$B9:\$S9,\$T\$5))+1,3)=0),INDEX(\$B9:\$S9,\$T\$5):INDEX(\$B9:\$S9,\$T\$6)))

which must be confirmed with control+shift+enter instead of just enter.

Replies
0
Views
72
Replies
3
Views
288
Replies
12
Views
570
Replies
0
Views
321
Replies
5
Views
247

1,203,069
Messages
6,053,347
Members
444,654
Latest member
Rich Cohen

### 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