a question on data arrangement (general) and chi-sq test (specific)

[seraphin]

a general question about data arrangement in Excel for calculation -

I have a huge list that I need to do a chi-square analysis on the data in the list (see attachment or below)
As chi-sq test runs as chi-sq (actual data, expected data), data is required (?) to be arranged in a certain way.
example 1
actual range expected range
row1 10 150 55.65217391 104.3478261
row2 150 150 104.3478261 195.6521739

chi square is calculated as chi-sq test(b2:c3, f2:g3)

However, my list is arranged as follows

example 2
actual expected
10 150 150 150 55.65217391 104.3478261 104.3478261 195.6521739
48 112 33 267 28.17391304 131.826087 52.82608696 247.173913
130 30 78 222 72.34782609 87.65217391 135.6521739 164.3478261
3 157 234 66 82.43478261 77.56521739 154.5652174 145.4347826




where I need to do chi-sq test for each row (data in the first row of example 2 is identical to the data in example 1)

While I was able to generate a list of the "expected ranges" for all rows (see example 2), chi-sq does not seem to support calculated data to be arranged this way (#VALUE!)

Is there a way to (a) re-arrange the data to conform to chi-square data arrangement or (b) to calculate chi-square without re-arranging the data?

Thank you

[shg]

An example workbook might help sort that spaghetti out.

[seraphin]

An example had been attached in the original posting. Not sure how to put up a workbook inside the question itself. please advise. thank you.

[shg]

You could do

=CHISQ.TEST(CHOOSE({1;2}, B10:C10, E10:F10), CHOOSE({1;2}, H10:I10, K10:L10))

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	row1	10	150	160		55.7	104.3
3	row2	150	150	300		104.3	195.7
4		160	300	460
5
6		6.39E-21												B6: =CHISQ.TEST(B2:C3,F2:G3)
7
8		actual						expected
9	actual list	col1	col2		col4	col5
10		10	150		150	150		55.7	104.3		104.3	195.7
11		48	112		33	267		28.2	131.8		52.8	247.2
12		130	30		78	222		72.3	87.7		135.7	164.3
13		3	157		234	66		82.4	77.6		154.6	145.4
14
15
16		6.39E-21												B16: =CHISQ.TEST(CHOOSE({1;2}, B10:C10, E10:F10), CHOOSE({1;2}, H10:I10, K10:L10))

[seraphin]

Thanks a lot !!

[shg]

You're welcome.