Quiz#3 NOT(Rubik's Cube)

[BiocideJ]

Formulas look good to me with one exception that you may or may not care about. If you enter Number of Cuts = 0 (i.e. the original cube) the results are incorrect. These formulas correct that (if you care about case 0)

Excel 2007
	A	B
1	CUBE QUIT
2	No of cuts	0
3	No of segments	1
4	Total resulting cubes	1
5
6	Atleast 2 different colours	1
7	Exactly 2 different colours	0
8	Exactly 2 specific colours	0
9	Atleast 3 different colours	1
10	Only one face painted	0
11	No paint at all	0
12	Total Nos of Squares	6
Sheet1

Cell Formulas
Range	Formula
B3	=B2+1
B4	=B3^3
B6	=B4-B10-B11
B7	=(B3-MIN(B4,2))*8
B8	=(B3-MIN(B4,2))*4
B9	=MIN(B4,8)
B10	=(B3-MIN(B4,2))^2*6
B11	=(B3-MIN(B4,2))^3
B12	=SUMPRODUCT(ROW(INDIRECT("$A$1:$A$" & B3))^2)*6

Editted cells are B7:B11 (using MIN) which basically just makes sure that your resultant counts for each query cannot be larger than the total number of cubes you have.

 

[Fazza]

Always best to avoid array formulas, IMHO. Suggest instead some variation of

=A1*(A1+1)*(2*A1+1)/6

to count the squares: base count in A1

 

[BiocideJ]

Always best to avoid array formulas, IMHO

I'm curious why feel that way. SUMPRODUCT isn't any more an array formula than SUM(A1:Z700) is it? I'm not saying you are wrong, I would just like to understand your reasoning.

 

[Fazza]

From Excel 2003 help,

SUM

Adds all the numbers in a range of cells.

SUMPRODUCT

Multiplies corresponding components in the given arrays, and returns the sum of those products.

SUMPRODUCT is an array formula. SUM is not.

 

[BiocideJ]

Getting into semantics now, but what is a range, but an array of cells.

I.E. given
Excel 2007

	A	B	C

<COLGROUP><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet3​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]C1[/TH]
[TD="align: left"]=SUM(A1:A5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]C2[/TH]
[TD="align: left"]=SUMPRODUCT(A1:A5)[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]



SUM and SUMPRODUCT are fed the same information and give the same output. I am assuming your claim is that Excel handles the two differently, which is probably true since you shouldn't use SUMPRODUCT on an entire column of 1M+ rows. I'll concede that all things being equal if you can use a non-array formula you are probably better off which I think is what you meant.
In this example, however, I don't think there is a way to dynamically calculate the factorial without using an array formula.
Thanks for the reply.

 

[taurean]

Not totally correct interpretation.

In your example, the formulas are "giving" the same result but that doesn't mean they are "behaving" the same way. Since the array supplied is only one, SUMPRODUCT is giving a result which SUM of ARRAY calculated by multiplying each range by 1 which is a lot different than mere adding which SUM does.

So SUM acts as : 1 + 2 + 3 + 4 + 5 = 15
while SUMPRODUCT acts as:1*1 + 2*1 + 3*1 + 4*1 + 5*1 = 15

Instead try it like this and the results will differ:
Sheet1

	A	B	C

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]55[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]55[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C1 =SUM(A1:A5,B1:B5) C2 =SUMPRODUCT(A1:A5,B1:B5) C3 =A5*(A5+1)*(2*A5+1)/6 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Instead what Fazza has suggested is simple mathematical formula which is used for summing a series of squares of natural numbers and the results match!

 

[BiocideJ]

I understand what you are saying, but I was not attempting to say that SUM and SUMPRODUCT were the same, but rather that both can be given a RANGE and that that range is converted to an array of cells in both formulas.

At any rate, I am beginning to derail the thread, so really, the issue at hand then is the question (Is there a way to determine the number without using an 'array' formula where N can be any number.)


It is easy to say that
=A1*(A1+1)*(2*A1+1)/6 is better, but I don't see how that helps the original question when what really needs to be calculating is...
=A1*(A1+1)*(2*A1+1)*...*(n*A1+1)/6

 

[taurean]

Wouldn't it become simpler when we need to multiply the result by 6 (as the original series formula divides by 6)?
=A5*(A5+1)*(2*A5+1)
shall give you the same result.

 

[drsarao]

I understand what you are saying, but I was not attempting to say that SUM and SUMPRODUCT were the same, but rather that both can be given a RANGE and that that range is converted to an array of cells in both formulas.

At any rate, I am beginning to derail the thread, so really, the issue at hand then is the question (Is there a way to determine the number without using an 'array' formula where N can be any number.)


It is easy to say that
=A1*(A1+1)*(2*A1+1)/6 is better, but I don't see how that helps the original question when what really needs to be calculating is...
=A1*(A1+1)*(2*A1+1)*...*(n*A1+1)/6

Is there a general formula for n without using arrays?

I ended up using array formula because I could NOT find a way.

One array formula in a sheet is not going to crash Excel!

But I would still be interested to know if a NON array solution is possible.

 

[taurean]

hi drsarao,

See the formula based on Fazza's suggestion works as you want:

Formula in B2 and C2 to be copied down.

Sheet2

	A	B	C

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 73px"> <col style="WIDTH: 64px"> <col style="WIDTH: 71px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]No. of cuts[/TD]
[TD="bgcolor: #c0c0c0"]Array[/TD]
[TD="bgcolor: #c0c0c0"]Non-Array[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]84[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]330[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =SUMPRODUCT(ROW(INDIRECT("$A$1:$A$" & A2))^2)*6 C2 =A2*(A2+1)*(A2*2+1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4