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]

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.

Yes, ok. I hope I make myself understand.
A:G is what I have, H:N is what I want. I will have to repeat the logic in H:N also for ROSN and no-cong in the following 2 sets of 5 cells.
Thanks,
Amanda

	A	B	C	D	E	F	G	(H) SICI	(I) SICI	(L) SICI	(M) SICI	(N) SICI
1	hours of the day	SICI	ROSN	no congestion	SICI	ROSN	no congestion	Ccgt	Olio	Tg	Fer	no congestion
2	1	Ccgt	0	0	Ccgt	0	0	COUNT "Ccgt" in B2,E2	COUNT "Oil" in B2,E2	COUNT "Tg" in B2,E2	COUNT "Fer" in B2,E2	COUNT "no congestion" in B2,E2
3	2	Ccgt	0	0	Ccgt	0	0
4	3	Ccgt	0	0	Ccgt	0	0	2	0	0	0	0
5	4	Ccgt	0	0	Ccgt	0	0
6	5	Ccgt	0	0	Ccgt	0	0
7	6	Ccgt	0	0	Ccgt	0	0
8	7	Ccgt	0	0	Ccgt	0	0
9	8	Ccgt	0	0	Ccgt	0	0
10	9	Ccgt	0	0	Ccgt	0	0
11	10	0	0	no congestion	0	0	no congestion
12	11	0	0	no congestion	Ccgt	0	0
13	12	Ccgt	0	0	Ccgt	0	0
14	13	Ccgt	0	0	Ccgt	0	0
15	14	Ccgt	0	0	Ccgt	0	0
16	15	0	0	no congestion	Ccgt	0	0
17	16	Ccgt	0	0	Fer	0	0
18	17	Ccgt	0	0	Ccgt	0	0
19	18	Olio	0	0	Olio	0	0
20	19	Olio	0	0	Olio	0	0
21	20	Tg	0	0	Tg	0	0	0	0	2	0	0
22	21	Fer	0	0	Olio	0	0	0	1	0	1	0
23	22	Olio	0	0	Olio	0	0	0	2	0	0	0
24	23	Olio	0	0	Olio	0	0
25	24	Ccgt	0	0	Olio	0	0

<tbody>
</tbody>

 

[Aladin Akyurek]

Yes, ok. I hope I make myself understand.
A:G is what I have, H:N is what I want. I will have to repeat the logic in H:N also for ROSN and no-cong in the following 2 sets of 5 cells.
Thanks,
Amanda
...

H1 shows Ccgt if I understand the posted exhibit. If so:

H2, copied down:

=COUNTIF(B2:E2,$H$1)

Is this what you are after?

 

[AmandaSS]

H1 shows Ccgt if I understand the posted exhibit. If so:

H2, copied down:

=COUNTIF(B2:E2,$H$1)

Is this what you are after?

thanks, but this formula doesnt really work.

In H2 I want to count how many times I have Ccgt in correspondence with SICI.
In I2 I want to count how many times I have Olio in correspondance with SICI. And so on for Tg Fer and no-congestion (in correspondence with SICI).

Btw, I was considering to re-organise my data set in a way that it's easier to get the result I am looking for. And avoid to formulate too complicated calculation.
Thanks

 

[Aladin Akyurek]

thanks, but this formula doesnt really work.

In H2 I want to count how many times I have Ccgt in correspondence with SICI.
In I2 I want to count how many times I have Olio in correspondance with SICI. And so on for Tg Fer and no-congestion (in correspondence with SICI).

Btw, I was considering to re-organise my data set in a way that it's easier to get the result I am looking for. And avoid to formulate too complicated calculation.
Thanks

When you post a sample, try to post one that shows what a suggested formula has to consider, along with the expected counts. The current sample has two ccgt's in B2:E2 and that's what the suggested formula calculates. SICI doesn't affect this calculation... I don't see why you dismissed an earlier suggestion which used the SICI condition explicitly.

 

[AmandaSS]

yes, it's true I am back to this code here
=SUM(IF(ISNUMBER(SEARCH(E$1,$A$2:$D$2)),IF($A3:$D3=E$2,1)))

which has a reference to SICI.

Otherwise I will simplify the data set and then implement formulas. this is also a smart approach
thanks !
</pre>

</pre>

 

[AmandaSS]

Hi All,

I am back to this treat because I need something similar to the previous formulas,

I have a row of heading (2 headings repeated a number of times) and columns of figures.
I need to calculate how many times "text" appears for each heading.

what I have					what I want
(A1) Heading 1	(B1) heading 2	(C1) heading 1	(D1) heading 2	....	text1	text2	text3	text4
text 1	text3	text3	text4		1	0	2	4
text4	text3	text4	text4		0	0	1	3
text2	text1	text2	text4		1	2	0	1
text1	text2	text1	text4		2	1	0	1

<tbody>
</tbody>

Thanks
Amanda

 

[Aladin Akyurek]

Let K:N house what you want with K1:N1 listing text1, text2, etc.

K2, copied across and down

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

 

[AmandaSS]

what I have					what I want
					heading 1				heading 2
(A1) Heading 1	(B1) heading 2	(C1) heading 1	(D1) heading 2		text1	text2	text3	text4		text1	text2	text3	text4
text 1	text3	text3	text4		1	0	1	0		0	0	1	1
text4	text3	text4	text4		0	0	0	2		0	0	1	1
text2	text1	text2	text4		0	2	0	0		1	0	0	1
text1	text2	text1	text4		2	0	0	0		0	1	0	1

<tbody>
</tbody>

Hi Aladin,

what I wrote above is actually what I needed. Sorry I was not enough accurate in my explanation.
Amanda

 

[Aladin Akyurek]

[...]
Hi Aladin,

what I wrote above is actually what I needed. Sorry I was not enough accurate in my explanation.
Amanda

Rearranging lay-out would allow you to run...

F4, copied across and down:

=COUNTIFS($A$3:$D$3,F$2,$A4:$D4,F$3)

See: https://dl.dropboxusercontent.com/u/65698317/AmandaSS%20countifs.xlsx