calcualte the avarage every 9 cell

[AmandaSS]

Hallo everybody,

I have a row with 9 (different) variables repeating 365 times. I have to calculate the avarage of each variable, over 365 times.

the example is for an hypotetical case of a row with 3 different variables repeating 365 times.
I have

A1

B1

C1

D1

E1

F1

G1

H1

I1

L1

M1

N1

1

prod

level

price

prod

level

price

prod

level

price

avarage prod

avarage level

avarage price

2

p1

l1

pr1

...

...

...

p365

l365

pr 365

avarage(price1, ..., price365)

avarage(level1, ..., level365)

avarage(pr1, ..., pr365)

<tbody>
</tbody>

I hope it's clear enough.
Thanks a lot as usual

Amanda

 

[Domenic]

The following formulas need to be confirmed with CONTROL+SHIFT+ENTER...

For your first variable:

=AVERAGE(IF(MOD(COLUMN(A2:APC2)-COLUMN(A2),9)+1=[COLOR=#ff0000]1[/COLOR],A2:APC2))

For your second variable:

=AVERAGE(IF(MOD(COLUMN(A2:APC2)-COLUMN(A2),9)+1=[COLOR=#ff0000]2[/COLOR],A2:APC2))

For your third variable:

=AVERAGE(IF(MOD(COLUMN(A2:APC2)-COLUMN(A2),9)+1=[COLOR=#ff0000]3[/COLOR],A2:APC2))

...etc.

Hope this helps!

 

[AmandaSS]

The following formulas need to be confirmed with CONTROL+SHIFT+ENTER...

For your first variable:

=AVERAGE(IF(MOD(COLUMN(A2:APC2)-COLUMN(A2),9)+1=[COLOR=#ff0000]1[/COLOR],A2:APC2))

Hi Dominic, thanks for your answer.
I have implemented the formulas, but it doesnt give the resutls, but null.
I guess the intervall

A2:APC2

is the row where all the variables are?

Then I havent it clear what it means that the formulas need to be confirmed with CONTROL+SHIFT+ENTER...
Amanda


</pre>

 

[Domenic]

=AVERAGE(IF(MOD(COLUMN(A2:APC2)-COLUMN(A2),9)+1=1,A2:APC2))

The above formula will return an average for every 9th cell within the range A2:APC2. So it returns the average for A2, J2, S2, etc. Isn't this what you're looking for?

Then I havent it clear what it means that the formulas need to be confirmed with CONTROL+SHIFT+ENTER...

Enter the formula in the desired cell, press the Ctrl key, then while the Ctrl key is pressed, press both the SHIFT and ENTER keys. If done correctly, Excel will automatically place curly braces {...} around the formula.

 

[AmandaSS]

The above formula will return an average for every 9th cell within the range A2:APC2. So it returns the average for A2, J2, S2, etc. Isn't this what you're looking for?

yes, this is exaclty what I want. I tried again and it works only for the 9th varaible. The previous 8 are=0... I dont know where I do it wrong..

Enter the formula in the desired cell, press the Ctrl key, then while the Ctrl key is pressed, press both the SHIFT and ENTER keys. If done correctly, Excel will automatically place curly braces {...} around the formula.

For soem reasons, I got the resut for the 9th variable without need of this process.

 

[Aladin Akyurek]

Try to post a shorter sample along with the headers and figures (numbers) and state clearly the calculations you need.

 

[AmandaSS]

Try to post a shorter sample along with the headers and figures (numbers) and state clearly the calculations you need.

Hi, sure, here there is another example:
I have 2 prices (cell A2, C2) and 2 prod (cell B2, D2); I need to calculate the avarage of the prices (avarage(A2,C2) and the avarage of prod (avarage(B2,D2).

priceA	prodA	priceB	prodB	avarage price	avarage prod
2	10	40	20	avarage(2,40)=21	avarage(10,20)=15

<tbody>
</tbody>

Thanks.
Amanda

 

[Aladin Akyurek]

Hi, sure, here there is another example:
I have 2 prices (cell A2, C2) and 2 prod (cell B2, D2); I need to calculate the avarage of the prices (avarage(A2,C2) and the avarage of prod (avarage(B2,D2).

priceA	prodA	priceB	prodB	avarage price	avarage prod
2	10	40	20	avarage(2,40)=21	avarage(10,20)=15

<tbody>
</tbody>

Thanks.
Amanda

Because of the presence of systematic headers...

1.

=AVERAGEIF($B$1:$D$1,"prod*",$B2:$D2)

2.

=AVERAGEIF($B$1:$D$1,"prod*",$A1:$C1)

 

[Domenic]

yes, this is exaclty what I want. I tried again and it works only for the 9th varaible. The previous 8 are=0... I dont know where I do it wrong..

You'll need to change the formula for each variable, as I've described in Post #2.

For soem reasons, I got the resut for the 9th variable without need of this process.

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, otherwise it won't return the correct result.

In any case, Aladin has offered you a more efficient formula, based on your actual headers, and one that doesn't require you to confirm the formula with CONTROL+SHIFT+ENTER.

 

[AmandaSS]

You'll need to change the formula for each variable, as I've described in Post #2.



The formula needs to be confirmed with CONTROL+SHIFT+ENTER, otherwise it won't return the correct result.

In any case, Aladin has offered you a more efficient formula, based on your actual headers, and one that doesn't require you to confirm the formula with CONTROL+SHIFT+ENTER.

Hi Dmenic and Aladin,

thanks a lot for your helpful advices! I have done the job and it worked well! In the end I used Aladin's formula which I found easier but thanks a lot to both, I have really appreciated your help!
Best regardas,
Amanda