Looking for a udf to calculate numerator

[michaelsmith559]

I have a sheet setup to calculate a naive bayes estimate. I am looking for a udf that can calculate the numerator for me as I have a lot of values to check and this part is manually too time consuming. The udf needs to ignore cells that contain a zero. What I want to be able to do is in cell BL18 and cell BM19 enter something like =numerator(select my range of cells) and then get an output like the current formulas give me. See sheet below:

Excel 2007
	BE	BF	BG	BH	BI	BJ	BK	BL	BM
1	Num Group							Value	5
2	Total	414	Value	5				Num Group	1
3	Success	118	Success	0.152542				Low High	2
4	Failure	296	Failure	0.060811				Odd Even	1
5								% Match	0.986
6	Low High Range							Diff	0.02
7	Total	414						Decimal	-0.000003
8	Success Low	277	Success Low	0				Prob of Number	0.043478261
9	Success High	137	Success High	0.131387
10								Success	Failure
11	Odd Even Range							0.152542373	0.060810811
12	Total	414						0	0.131386861
13	Success Odd	215	Success Low	0.083721				0.08372093	0
14	Success Even	199	Success High	0				0	0.044226044
15								0	0.049586777
16	Percent Match							0.038834951	0.048387097
17	Total	414						0.043478261	0.956521739
18	Success	7	Success	0			Numerator	3.46098E-08	1.19034E-07
19	Failure	407	Failure	0.044226
20							Nave Bayes Estimate	0.225260262	0.774739738
21	Difference Value
22	Total	413
23	Success	50	Success	0
24	Failure	363	Failure	0.049587
25
26	Decimal Number
27	Total	413
28	Success	103	Success	0.038835
29	Failure	310	Failure	0.048387
30
31	Probability of Number
32	Total	414
33	Success	18	Success	0.043478
34	Failure	396	Failure	0.956522
Sheet1

Cell Formulas
Range	Formula
BL11	=BH3
BL12	=BH8
BL13	=BH13
BL14	=BH18
BL15	=BH23
BL16	=BH28
BL17	=BH33
BL18	=(PRODUCT(BL11:BL11)*BF3/BF2)*(PRODUCT(BL13:BL13)*BF13/BF12)*(PRODUCT(BL16:BL16)*BF28/BF27)*(PRODUCT(BL17:BL17)*BF33/BF32)
BL20	=BL18/SUM(BL18:BM18)
BM11	=BH4
BM12	=BH9
BM13	=BH14
BM14	=BH19
BM15	=BH24
BM16	=BH29
BM17	=BH34
BM18	=(PRODUCT(BM11:BM11)*BF4/BF2)*(PRODUCT(BM12:BM12)*BF9/BF7)*(PRODUCT(BM14:BM14)*BF19/BF17)*(PRODUCT(BM15:BM15)*BF24/BF22)*(PRODUCT(BM16:BM16)*BF29/BF27)*(PRODUCT(BM17:BM17)*BF34/BF32)
BM20	=BM18/SUM(BL18:BM18)

 

[pgc01]

Hi

You don't need a udf, you can use a formula.
Test the values in BL11:BL17 and discard the products where they are 0, like in B18:

=PRODUCT(IF(BL11:BL17,BL11:BL17^{1,0}*N(OFFSET(BF2,(ROW(B11:B17)-ROW(B11))*5+{0,1},))^{-1,1}))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER

 

[michaelsmith559]

The formula works for the values shown in BM1:BM8. When I change the values, to the next value to evaluate, the value returned using the formula in BL18 returns: 3.18385E-08 and your formula returns: 2.49388E-09.

<colgroup><col width="107"></colgroup><tbody>
[TD="width: 107, align: right"][/TD]

</tbody>

 

[pgc01]

The formula works for the values shown in BM1:BM8. When I change the values, to the next value to evaluate, the value returned using the formula in BL18 returns: 3.18385E-08 and your formula returns: 2.49388E-09.

Sorry, I don't understand: "When I change the values, to the next value to evaluate"

You mean you have new values in BL11:BL17? which are they?

 

[michaelsmith559]

Also, it looks like when I put the formula into BL19, it works for calculating the numerator for BL18. When I move the formula to BM19 and calculate BM18, it gives different results.

 

[michaelsmith559]

I am going to post another sheet.

 

[michaelsmith559]

Excel 2007
	BE	BF	BG	BH	BI	BJ	BK	BL	BM
1	Num Group							Value	5
2	Total	414	Value	5				Num Group	1
3	Success	118	Success	0.152542				Low High	2
4	Failure	296	Failure	0.060811				Odd Even	1
5								% Match	0.986
6	Low High Range							Diff	0.02
7	Total	414						Decimal	-0.000003
8	Success Low	277	Success Low	0				Prob of Number	0.043478261
9	Success High	137	Success High	0.131387
10								Success	Failure
11	Odd Even Range							0.152542373	0.060810811
12	Total	414						0	0.131386861
13	Success Odd	215	Success Low	0.083721				0.08372093	0
14	Success Even	199	Success High	0				0	0.044226044
15								0	0.049586777
16	Percent Match							0.038834951	0.048387097
17	Total	414						0.043478261	0.956521739
18	Success	7	Success	0			Numerator	3.46098E-08	1.19034E-07
19	Failure	407	Failure	0.044226				3.46098E-08	3.43273E-12
20							Nave Bayes Estimate	0.225260262	0.774739738
21	Difference Value
22	Total	413
23	Success	50	Success	0
24	Failure	363	Failure	0.049587
25
26	Decimal Number
27	Total	413
28	Success	103	Success	0.038835
29	Failure	310	Failure	0.048387
30
31	Probability of Number
32	Total	414
33	Success	18	Success	0.043478
34	Failure	396	Failure	0.956522
Sheet1

Cell Formulas
Range	Formula
BL11	=BH3
BL12	=BH8
BL13	=BH13
BL14	=BH18
BL15	=BH23
BL16	=BH28
BL17	=BH33
BL18	=(PRODUCT(BL11:BL11)*BF3/BF2)*(PRODUCT(BL13:BL13)*BF13/BF12)*(PRODUCT(BL16:BL16)*BF28/BF27)*(PRODUCT(BL17:BL17)*BF33/BF32)
BL20	=BL18/SUM(BL18:BM18)
BL19	{=PRODUCT(IF(BL11:BL17,BL11:BL17^{1,0}*N(OFFSET(BF2,(ROW(B11:B17)-ROW(B11))*5+{0,1},))^{-1,1}))}
BM11	=BH4
BM12	=BH9
BM13	=BH14
BM14	=BH19
BM15	=BH24
BM16	=BH29
BM17	=BH34
BM18	=(PRODUCT(BM11:BM11)*BF4/BF2)*(PRODUCT(BM12:BM12)*BF9/BF7)*(PRODUCT(BM14:BM14)*BF19/BF17)*(PRODUCT(BM15:BM15)*BF24/BF22)*(PRODUCT(BM16:BM16)*BF29/BF27)*(PRODUCT(BM17:BM17)*BF34/BF32)
BM20	=BM18/SUM(BL18:BM18)
BM19	{=PRODUCT(IF(BM11:BM17,BM11:BM17^{1,0}*N(OFFSET(BF2,(ROW(B11:B17)-ROW(B11))*5+{0,1},))^{-1,1}))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pgc01]

I see.

Well, the formulas you are using in BL18 and BM18 are not doing the same thing.

In BL18 you choose in each group of 3 values in column BF the second and the first values and you divide them and multiply the result by the corresponding value in column BL.
In BM18 you choose in each group of 3 values in column BF the third and the first values and you divide them and multiply the result by the corresponding value in column BM.

This means that in the formula in BM you have to use an offset of 2 instead of 1

{=PRODUCT(IF(BM11:BM17,BM11:BM17^{1,0}*N(OFFSET(BF2,(ROW(B11:B17)-ROW(B11))*5+{0,2},))^{-1,1}))}

{

 

[michaelsmith559]

Thank you so much. Solved the problem great.

 

[pgc01]

You're welcome. I'm glad it helped.