Looking for a udf to calculate numerator

michaelsmith559

Well-known Member
Joined
Oct 6, 2013
Messages
881
Office Version
  1. 2013
  2. 2007
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
BEBFBGBHBIBJBKBLBM
1Num GroupValue5
2Total414Value5Num Group1
3Success118Success0.152542Low High2
4Failure296Failure0.060811Odd Even1
5% Match0.986
6Low High RangeDiff0.02
7Total414Decimal-0.000003
8Success Low277Success Low0Prob of Number0.043478261
9Success High137Success High0.131387
10SuccessFailure
11Odd Even Range0.1525423730.060810811
12Total41400.131386861
13Success Odd215Success Low0.0837210.083720930
14Success Even199Success High000.044226044
1500.049586777
16Percent Match0.0388349510.048387097
17Total4140.0434782610.956521739
18Success7Success0Numerator3.46098E-081.19034E-07
19Failure407Failure0.044226
20Nave Bayes Estimate0.2252602620.774739738
21Difference Value
22Total413
23Success50Success0
24Failure363Failure0.049587
25
26Decimal Number
27Total413
28Success103Success0.038835
29Failure310Failure0.048387
30
31Probability of Number
32Total414
33Success18Success0.043478
34Failure396Failure0.956522
Sheet1
Cell Formulas
RangeFormula
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)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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>
</tbody>
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Excel 2007
BEBFBGBHBIBJBKBLBM
1Num GroupValue5
2Total414Value5Num Group1
3Success118Success0.152542Low High2
4Failure296Failure0.060811Odd Even1
5% Match0.986
6Low High RangeDiff0.02
7Total414Decimal-0.000003
8Success Low277Success Low0Prob of Number0.043478261
9Success High137Success High0.131387
10SuccessFailure
11Odd Even Range0.1525423730.060810811
12Total41400.131386861
13Success Odd215Success Low0.0837210.083720930
14Success Even199Success High000.044226044
1500.049586777
16Percent Match0.0388349510.048387097
17Total4140.0434782610.956521739
18Success7Success0Numerator3.46098E-081.19034E-07
19Failure407Failure0.0442263.46098E-083.43273E-12
20Nave Bayes Estimate0.2252602620.774739738
21Difference Value
22Total413
23Success50Success0
24Failure363Failure0.049587
25
26Decimal Number
27Total413
28Success103Success0.038835
29Failure310Failure0.048387
30
31Probability of Number
32Total414
33Success18Success0.043478
34Failure396Failure0.956522
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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}))}

{
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top