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
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD]C1
[/TD]
[TD]D1
[/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[TD]G1
[/TD]
[TD]H1
[/TD]
[TD]I1
[/TD]
[TD]L1
[/TD]
[TD]M1
[/TD]
[TD]N1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]prod
[/TD]
[TD]level[/TD]
[TD]price[/TD]
[TD]prod[/TD]
[TD]level[/TD]
[TD]price
[/TD]
[TD]prod[/TD]
[TD]level
[/TD]
[TD]price
[/TD]
[TD]avarage prod
[/TD]
[TD]avarage level
[/TD]
[TD]avarage price
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]p1
[/TD]
[TD]l1[/TD]
[TD]pr1
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]...
[/TD]
[TD]p365
[/TD]
[TD]l365
[/TD]
[TD]pr 365
[/TD]
[TD]avarage(price1, ..., price365)
[/TD]
[TD]avarage(level1, ..., level365)
[/TD]
[TD]avarage(pr1, ..., pr365)
[/TD]
[/TR]
</tbody>[/TABLE]

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

Amanda

 

[AmandaSS]

Hi I am back to this forumla becasue I have to modify it slightly.
I have to count how many times one "text" repeats in the same row, every 4 cells.


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E(fruit)
[/TD]
[TD]F(apple)
[/TD]
[TD]G(grape)
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD]fruit
[/TD]
[TD]frui
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]grape
[/TD]
[TD]apple
[/TD]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Amanda

 

[Aladin Akyurek]

E2, copied across and down:

=COUNTIF($A2:$D2,E$1)

where E1 = fruit, F1 = apple, G1 = grape.

 

[AmandaSS]

E2, copied across and down:

=COUNTIF($A2:$D2,E$1)

where E1 = fruit, F1 = apple, G1 = grape.

Thanks Aladin!
It works perfectly

I forgot to menton an additional feature. I need the formula to count how many apple/grape/fruit are in each row EVERY 4 CELLS.
This is because I need to distinguish by coloumn as well.

Amanda

 

[Aladin Akyurek]

Thanks Aladin!
It works perfectly

I forgot to menton an additional feature. I need the formula to count how many apple/grape/fruit are in each row EVERY 4 CELLS.
This is because I need to distinguish by coloumn as well.

Amanda

Would you post a 2 row by 9 column sample along with the expected results?

 

[AmandaSS]

Would you post a 2 row by 9 column sample along with the expected results?

yes, of course, my text was not clear at all..
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]
I have A1, B1, A2, B2. I have to fill in the coloums apple(A), (B),grape(A),(B), frui(A), (B) with how many times i get the corresponding fruits.
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD]A2
[/TD]
[TD]B2
[/TD]
[TD]fruit(A)
[/TD]
[TD]apple(A)
[/TD]
[TD]grape(A)
[/TD]
[TD]fruit(B)
[/TD]
[TD]apple(B)
[/TD]
[TD]grape(B)
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD]fruit
[/TD]
[TD]fruit
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]grape
[/TD]
[TD]apple
[/TD]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

yes, of course, my text was not clear at all..
[TABLE="class: cms_table, width: 500"]
<TBODY>[TR]
[/TR]
[TR]
[TD]I have A1, B1, A2, B2. I have to fill in the coloums apple(A), (B),grape(A),(B), frui(A), (B) with how many times i get the corresponding fruits.
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 500"]
<TBODY>[TR]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD]A2
[/TD]
[TD]B2
[/TD]
[TD]fruit(A)
[/TD]
[TD]apple(A)
[/TD]
[TD]grape(A)
[/TD]
[TD]fruit(B)
[/TD]
[TD]apple(B)
[/TD]
[TD]grape(B)
[/TD]
[/TR]
[TR]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD]fruit
[/TD]
[TD]fruit
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grape
[/TD]
[TD]apple
[/TD]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Change the lay-out a bit as follows:

[TABLE="width: 505"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2304" width=65><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2673" width=75><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2076" width=58><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2474" width=70><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><TBODY>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 65, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]B[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]A2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 65, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]grape[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]grape[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 65, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]grape[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 65, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]0[/TD]
[/TR]
</TBODY>[/TABLE]

E3, control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(SEARCH(E$1,$A$2:$D$2)),IF($A3:$D3=E$2,1)))

 

[AmandaSS]

Change the lay-out a bit as follows:

[TABLE="width: 505"]
<tbody>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 65, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]B[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]B[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]A2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]B2[/TD]
[TD="class: xl63, width: 65, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]grape[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]grape[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 65, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]grape[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]apple[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]fruit[/TD]
[TD="class: xl63, width: 65, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 75, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 71, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 58, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 70, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 79, bgcolor: white"]0[/TD]
[/TR]
</tbody>[/TABLE]

E3, control+shift+enter, not just enter, copy across, and down:

=SUM(IF(ISNUMBER(SEARCH(E$1,$A$2:$D$2)),IF($A3:$D3=E$2,1)))

Hi, thanks for this!
I implemented the code and I managed to activate it through control+shift+enter but the result I get is 0 in every cell...

 

[Aladin Akyurek]

Hi, thanks for this!
I implemented the code and I managed to activate it through control+shift+enter but the result I get is 0 in every cell...

Control+shift+enter means press down the control and the shift keys at the same time, while you hit the enter key.

See the workbook that implements the set up:
https://dl.dropboxusercontent.com/u/65698317/AmandaSS%20conditional%20counting.xlsx

 

[AmandaSS]

Hi, I think I still havent understood..

Here is the file, hope you dont mind to have a look at what I do wrong (cell API3).

https://www.dropbox.com/s/7iq4m9v276yf95v/TEST1.xlsm?dl=0

 

[Aladin Akyurek]

Hi, I think I still havent understood..

Here is the file, hope you dont mind to have a look at what I do wrong (cell API3).

https://www.dropbox.com/s/7iq4m9v276yf95v/TEST1.xlsm?dl=0

If you are unable to adapt the solution, designed for a sample you posted, to your real data, you should perhaps reconsider posting another more convenient sample directly on the board, which is representative of your data.