Decimal Odds Calculator excluding blanks

[ronie85]

Hi, i require a formula to calculate decimal betting odds but disregarding the blanks.

As the rows can be 8 cells long, i need the 2nd row showing 1.07 & 1.39 to calculate =SUM(1*A2*B2*C2*D2*E2*F2*G2*H2) and return a value of 1.4873 however as columns C to H are blank, this will return 0.
I need the forumal only to calculate the actual cells with text, disregarding the blanks.
I will then copy this forumla down each row.

Odds 1	Odds 2	Odds 3	Odds 4	Odds 5	Odds 6	Odds 7	Odds 8
1.07​	1.39​
1.34​	1.18​	1.29​	1.27​	1.16​	1.28​
1.67​	1.87​	1.71​
1.96​	1.2​	1.43​
1.27​	1.28​	1.21​	1.36​	1.28​	1.06​
1.41​	1.18​	1.2​
1.42​
1.95​	1.67​	1.3​	1.62​	1.64​	1.57​	1.54​	1.24​
1.45​	1.61​	1.69​
1.166667​	1.37​	1.27​	1.21​
1.54​	1.65​	1.58​	1.39​	1.45​	1.39​	1.6​	1.38​
1.61​	1.33​	1.5​	1.44​
1.615385​	1.333333​	1.5​	1.5​	1.45​	1.181818​
1.666667​	1.55​

 

[awoohaw]

Try this:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I
1	Odds 1	Odds 2	Odds 3	Odds 4	Odds 5	Odds 6	Odds 7	Odds 8	PRODUCT
2	1.07	1.39							1.4873
3	1.34	1.18	1.29	1.27	1.16	1.28			3.846345
4	1.67	1.87	1.71						5.340159
5	1.96	1.2	1.43						3.36336
6	1.27	1.28	1.21	1.36	1.28	1.06			3.629559
7	1.41	1.18	1.2						1.99656
8	1.42								1.42
9	1.95	1.67	1.3	1.62	1.64	1.57	1.54	1.24	33.7206
10	1.45	1.61	1.69						3.945305
11	1.166667	1.37	1.27	1.21					2.45616
12	1.54	1.65	1.58	1.39	1.45	1.39	1.6	1.38	24.83467
13	1.61	1.33	1.5	1.44					4.625208
14	1.615385	1.333333	1.5	1.5	1.45	1.181818			8.304544
15	1.666667	1.55							2.583334
ronie85

Cell Formulas
Range	Formula
I2:I15	I2	=PRODUCT(FILTER(A2:H2,ISBLANK(A2:H2)=FALSE))

 

[ronie85]

That works great thanks alot

 

[awoohaw]

Happy you found a solution.

Best Wishes.

 

[Peter_SSs]

No need to FILTER

23 11 05.xlsm
	A	B	C	D	E	F	G	H	I
1	Odds 1	Odds 2	Odds 3	Odds 4	Odds 5	Odds 6	Odds 7	Odds 8	PRODUCT
2	1.07	1.39							1.4873
3	1.34	1.18	1.29	1.27	1.16	1.28			3.846344645
4	1.67	1.87	1.71						5.340159
5	1.96	1.2	1.43						3.36336
6	1.27	1.28	1.21	1.36	1.28	1.06			3.62955853
7	1.41	1.18	1.2						1.99656
8	1.42								1.42
9	1.95	1.67	1.3	1.62	1.64	1.57	1.54	1.24	33.72060484
10	1.45	1.61	1.69						3.945305
11	1.166667	1.37	1.27	1.21					2.456159535
12	1.54	1.65	1.58	1.39	1.45	1.39	1.6	1.38	24.83467173
13	1.61	1.33	1.5	1.44					4.625208
14	1.615385	1.333333	1.5	1.5	1.45	1.181818			8.304544078
15	1.666667	1.55							2.58333385
Product

Cell Formulas
Range	Formula
I2:I15	I2	=PRODUCT(A2:H2)

.. or you could do all the rows at once with this single formula.

23 11 05.xlsm
	A	B	C	D	E	F	G	H	I
1	Odds 1	Odds 2	Odds 3	Odds 4	Odds 5	Odds 6	Odds 7	Odds 8	PRODUCT
2	1.07	1.39							1.4873
3	1.34	1.18	1.29	1.27	1.16	1.28			3.846344645
4	1.67	1.87	1.71						5.340159
5	1.96	1.2	1.43						3.36336
6	1.27	1.28	1.21	1.36	1.28	1.06			3.62955853
7	1.41	1.18	1.2						1.99656
8	1.42								1.42
9	1.95	1.67	1.3	1.62	1.64	1.57	1.54	1.24	33.72060484
10	1.45	1.61	1.69						3.945305
11	1.166667	1.37	1.27	1.21					2.456159535
12	1.54	1.65	1.58	1.39	1.45	1.39	1.6	1.38	24.83467173
13	1.61	1.33	1.5	1.44					4.625208
14	1.615385	1.333333	1.5	1.5	1.45	1.181818			8.304544078
15	1.666667	1.55							2.58333385
Product (2)

Cell Formulas
Range	Formula
I2:I15	I2	=BYROW(A2:H15,LAMBDA(rw,PRODUCT(rw)))
Dynamic array formulas.