Formula to ave. last 3 months

[amitbarkan73]

Hi,

I need to ave. the last 4 months of the year.
I have this formula to do it:
=AVERAGE(OFFSET(B2,,COUNTA(B2:L2),,-(MIN(COUNTA(B2:L2),4))))

The twist is that I need it to ave. every 3rd cell only

example:
a b c d
e f g h
i
Volume Expense unit cost Volume Expense unit cost Volume
Expense unit
124,664 $38,197.15 0.3064 149,972 $46,902.63 0.3127 163,161
$50,837.560.3116

I would like to ave the volume (meaning column a,d,g)

Thank you,
Amit.

 

[Bernie Deitrick]

Amit,

You could do this by inserting a new row 1, and using a formula like this in cell A1: Your subject
line says 3 months, your message says 4 month, so I used 4 - change the 4 to whatever is correct:

=IF(A2="Volume",IF(COUNTIF(A2:$IV$2,"Volume")<=4,"Sum",""),"")

Copy that to fill row 1, then use the formula

=SUMIF(B1:L1,"Sum",B3:L3)/4

to get the average....

HTH,
Bernie
MS Excel MVP

 

[Domenic]

Assuming that A1:L1 contains the header (Volume, Expense, Unit Cost,
Volume, Expense, Unit cost, etc.), and A2:L2 contains the corresponding
value, try the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER...

=AVERAGE(IF(A1:L1="Volume",IF(COLUMN(A2:L2)>=LARGE(IF(A1:L1="Volume",COLU
MN(A2:L2)),MIN(4,COUNTIF(A1:L1,"Volume"))),A2:L2)))

Hope this helps!