Why are many of my formula coming out at the same wrong value (power to and divide formula)

moonpig23

New Member
Joined
Oct 1, 2022
Messages
13
Office Version
  1. 365
Please help me wise ones :)

I am trying to do the following calculations but for some reason one formula which is =(Mean^2)/(SE^2) is only working for two of the calculations. There's no absolute references being used, the numbers are filling down okay, the values all come out the same wrong number. I have tried to copy the formatting of the formula that worked into those that aren't and that didn't work either :(

Unfortunately the L2BB won't install on my PC as it belongs to work so here's hoping this paste works

Thank you



Alpha=(Mean^2)/(SE^2)
Beta=(SE^2)/Mean
Probabilistic value=GAMMAINV(Rand(),alpha,beta)
Standard error=(upper value-lower value)/(2*NORMINV(0.975,0,1))
Confidence Interval
MeanUpperLowerS.E.AlphaBetaProb
£336.55403.86269.2434.396.043.50£342.71
£228.99274.79183.1923.496.042.38£260.99
£242.63136.7991.1911.6435.100.56£248.19
£128.64154.37102.9113.196.041.34£116.56
£210.75252.90189.6816.1170.761.23£208.45
£246.10295.32196.8825.196.042.56£264.45
£300.00360.00240.0030.696.043.12£355.17
£5,617.856741.424494.28573.396.0458.50£6,907.92
£245.64294.77196.5125.196.042.56£242.50
£59.2871.1447.426.096.040.62£64.98
£245.64294.77196.5125.196.042.56£212.84
£61.6974.0349.356.396.040.64£61.37
£121.08145.3096.8612.496.041.26£127.61
15.0018.0012.001.596.040.16£18.34

336.55403.86269.24=(D10-E10)/(2*NORM.INV(0.975,0,1))=(C10^2)/(F10^2)=(F10^2)/C10=GAMMAINV(RAND(),H10,I10)
228.99274.788183.192=(D11-E11)/(2*NORM.INV(0.975,0,1))=(C11^2)/(F11^2)=(F11^2)/C11=GAMMAINV(RAND(),H11,I11)
242.63136.78891.192=(D12-E12)/(2*NORM.INV(0.975,0,1))=(C12^2)/(F12^2)=(F12^2)/C12=GAMMAINV(RAND(),H12,I12)
128.64154.368102.912=(D13-E13)/(2*NORM.INV(0.975,0,1))=(C13^2)/(F13^2)=(F13^2)/C13=GAMMAINV(RAND(),H13,I13)
210.75252.9189.68=(D14-E14)/(2*NORM.INV(0.975,0,1))=(C14^2)/(F14^2)=(F14^2)/C14=GAMMAINV(RAND(),H14,I14)
246.1295.32196.88=(D15-E15)/(2*NORM.INV(0.975,0,1))=(C15^2)/(F15^2)=(F15^2)/C15=GAMMAINV(RAND(),H15,I15)
300360240=(D16-E16)/(2*NORM.INV(0.975,0,1))=(C16^2)/(F16^2)=(F16^2)/C16=GAMMAINV(RAND(),H16,I16)
5617.856741.424494.28=(D17-E17)/(2*NORM.INV(0.975,0,1))=(C17^2)/(F17^2)=(F17^2)/C17=GAMMAINV(RAND(),H17,I17)
245.64294.768196.512=(D18-E18)/(2*NORM.INV(0.975,0,1))=(C18^2)/(F18^2)=(F18^2)/C18=GAMMAINV(RAND(),H18,I18)
59.2871.13647.424=(D19-E19)/(2*NORM.INV(0.975,0,1))=(C19^2)/(F19^2)=(F19^2)/C19=GAMMAINV(RAND(),H19,I19)
245.64294.768196.512=(D20-E20)/(2*NORM.INV(0.975,0,1))=(C20^2)/(F20^2)=(F20^2)/C20=GAMMAINV(RAND(),H20,I20)
61.6974.02849.352=(D21-E21)/(2*NORM.INV(0.975,0,1))=(C21^2)/(F21^2)=(F21^2)/C21=GAMMAINV(RAND(),H21,I21)
121.08145.29696.864=(D22-E22)/(2*NORM.INV(0.975,0,1))=(C22^2)/(F22^2)=(F22^2)/C22=GAMMAINV(RAND(),H22,I22)
151812=(D23-E23)/(2*NORM.INV(0.975,0,1))=(C23^2)/(F23^2)=(F23^2)/C23=GAMMAINV(RAND(),H23,I23)
 
Last edited by a moderator:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I have also checked the formula to make sure the formula are not entered as text and calculation options is set to Automatic
 
Upvote 0
Upvote 0
hi thanks Hmm not sure if it looks like that on here but can confirm there's nothing wrong with the variables in the sheet
 
Upvote 0
@Dave Patton can i ask you to please have a look at the xlsx file to see if you still think there's some text in the first column. Please see 7th post :D
 
Upvote 0
Did you select a range of data to extract with XL2BB? Your post is blank.
There is an area where you can test the XL2BB.

Did you type the currency symbol or is it part of the formatting? Try formatting some of the currency cells as general, the currency symbol should not show.
 
Upvote 0
Parameter NameBase case valueTornadoValue UsedLowerUpperDistributionSEnAlphaBetaProbabilistic
Discount rate of costs 0.035000.035000.036140.028000.04200Beta0.003572646.8626992.640192554.222490.03614
Discount rate of utilities 0.035000.035000.035440.028000.04200Beta0.003572646.8626992.640192554.222490.03544
Utility PFS 0.730000.730000.854580.584000.87600Beta0.0744934.5203425.199859.320490.85458
Utility PD0.650000.650000.707110.520000.78000Beta0.0663350.7119532.9627617.749180.70711
Complex chemo336.55000113.99000314.05199269.24000403.86000Gamma34.3424796.036473.50440314.05199
Simple chemo228.99000128.64000243.25164183.19200274.78800Gamma23.3667696.036472.38441243.25164
Adverse event management (a)113.99000210.75000121.4884791.19200136.78800Gamma11.6318596.036471.18694121.48847
Adverse event management (b) 128.64000246.10000136.98450102.91200154.36800Gamma13.1267796.036471.33949136.98450
Adverse event management (c) 210.75000300.00000215.31616189.67500252.90000Gamma16.12912170.731501.23439215.31616
Adverse event management (d) 246.10000336.55000255.98714196.88000295.32000Gamma25.1127196.036472.56257255.98714
Cost of subsequent therapies 300.00000228.99000275.61823240.00000360.00000Gamma30.6128196.036473.12381275.61823
End of life cost (£) 5617.850005617.850004850.258484494.280006741.42000Gamma573.2605396.0364758.497054850.25848
PFS - one-off cost for first cycle 245.64000245.64000255.35217196.51200294.76800Gamma25.0657796.036472.55778255.35217
PFS - follow up cost 59.2800059.2800055.4768447.4240071.13600Gamma6.0490996.036470.6172755.47684
PD - one-off cost 245.64000245.64000219.75263196.51200294.76800Gamma25.0657796.036472.55778219.75263
PD - follow up cost 61.6900061.6900065.8207749.3520074.02800Gamma6.2950196.036470.6423665.82077
PDL1 test 121.08000121.08000129.7426896.86400145.29600Gamma12.3553396.036471.26077129.74268
Time Horizon15.0000015.0000013.7834012.0000018.00000Gamma1.5306496.036470.1561913.78340
 
Upvote 0
All of the formula are in General, @Dave Patton Thanks for the tip about checking that.

Costs discount rate0.035Costs discount rate0.03150.0385PFS Utility 312787.8602265906.234946881.63
Utility discount rate0.035Utility discount rate0.03150.0385Cost of subsequent therapies 279036.5075295859.576216823.07
Utility PFS 0.73PFS Utility 0.6570.803PD Utility 293011.5118282091.904610919.61
Utility PD0.65PD Utility 0.5850.715Complex chemo283183.6049291712.47878528.87
Adverse event management (a)113.99Adverse event management (a)91.65136.33Utility discount rate284761.0051290141.00155380
Adverse event management (b) 128.64Adverse event management (b) 103.42153.85Costs discount rate288206.3571286696.21191510.15
Adverse event management (c) 210.75Adverse event management (c) 169.44252.05PD - follow up cost 286733.6885288162.39511428.71
Adverse event management (d) 246.1Adverse event management (d) 197.86294.33PFS - follow up cost 286761.4754288134.60831373.13
Cost of subsequent therapies 300Cost of subsequent therapies 241.2358.8Simple chemo286922.5462287973.53741050.99
Complex chemo336.55Complex chemo270.59402.51Adverse event management (d) 287597.3169287298.7977298.52
Simple chemo228.99Simple chemo184.11273.87Adverse event management (b) 287369.7578287526.2947156.54
End of life cost (£) 5617.85End of life cost (£) 4516.776718.93PDL1 test 287374.3829287521.7008147.32
PFS - one-off cost for first cycle 245.64PFS - one-off cost for first cycle 197.49293.78Adverse event management (a)287378.6975287517.3861138.69
PFS - follow up cost 59.28PFS - follow up cost 47.42471.136Time Horizon287557.1584287441.6542115.5
PD - one-off cost 245.64PD - one-off cost 197.49293.78End of life cost (£) 287412.2871287483.796671.51
PD - follow up cost 61.69PD - follow up cost 49.35274.028PFS - one-off cost for first cycle 287448.1588287447.92480.23
PDL1 test 121.08PDL1 test 97.35144.81PD - one-off cost 287448.1588287447.92480.23
Time Horizon15Time Horizon12.0617.94Adverse event management (c) 287448.0418287448.04180
 
Upvote 0
The yelllow only sheet links through to the multi coloured sheet.

The text I've highlighted in red is working.
I even tried cutting and pasting the data and then typing over the results but the same problem happens.
Any further insight would be really helpful.

Thanks again
 
Upvote 0
Managed to solve my own problem by cutting and pasting formulas and effectively new calculations from the cells on the second page that were referenced in the formula and duplicated on the multi coloured page. This shouldn't have worked because the formula all looked fine before but it did work so.... :)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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