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

 

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

A	B	C	D	E(fruit)	F(apple)	G(grape)
apple	fruit	fruit	frui	3	1	0
grape	apple	apple	fruit	1	2	1

<tbody>
</tbody>

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

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.

<tbody>
</tbody>

A1	B1	A2	B2	fruit(A)	apple(A)	grape(A)	fruit(B)	apple(B)	grape(B)
apple	fruit	fruit	fruit	1	1	0	2	0	0
grape	apple	apple	fruit	0	1	1	1	1	0

<tbody>
</tbody>

 

[Aladin Akyurek]

yes, of course, my text was not clear at all..

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.

<TBODY>
</TBODY>

A1	B1	A2	B2	fruit(A)	apple(A)	grape(A)	fruit(B)	apple(B)	grape(B)
apple	fruit	fruit	fruit	1	1		2
grape	apple	apple	fruit		1	1	1	1

<TBODY>
</TBODY>

Change the lay-out a bit as follows:

				A	A	A	B	B	B
A1	B1	A2	B2	fruit	apple	grape	fruit	apple	grape
apple	fruit	fruit	fruit	1	1	0	2	0	0
grape	apple	apple	fruit	0	1	1	1	1	0

<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>
</TBODY>

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:

				A	A	A	B	B	B
A1	B1	A2	B2	fruit	apple	grape	fruit	apple	grape
apple	fruit	fruit	fruit	1	1	0	2	0	0
grape	apple	apple	fruit	0	1	1	1	1	0

<tbody>
</tbody>

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.