# calcualte the avarage every 9 cell

#### AmandaSS

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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
The following formulas need to be confirmed with CONTROL+SHIFT+ENTER...

Code:
``````For your first variable:

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

Code:
``````For your second variable:

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

Code:
``````For your third variable:

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

...etc.

Hope this helps!

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

Code:
``````For your first variable:

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

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>

=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.

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.

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

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

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)

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.

Last edited:
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.

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

Replies
3
Views
1K

1,221,522
Messages
6,160,311
Members
451,637
Latest member
hvp2262

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