Macro Using Squared Not Functioning

[honkin]

I have a macro I am trying to develop, but at the moment, it simply sits there still as a macro

What I am after in English is the following:-

Number of Runners = F2
Expected Winners = F3
Winners = F4

So the formula in English is runners x (winners – expectedwinners)² / expectedwinners x (runners – expectedwinners)

I tried the following to achieve this, but nothing happens at all

=F2*(F4-F3)^2/F3*(F2-F3)

I even tried it not using the squared symbol, but instead tried using power =

=POWER(F4-F3,2)*F2/F3*(F2-F3)

Any thoughts on the best way to write this to have it function correctly?

cheers

 

[Michael M]

If you are trying to use the line in a macro you need to specifically identify the ranges
something like

Dim ans As Integer
ans = Cells(2, 6) * (Cells(4, 6) - Cells(3, 6)) ^ 2 / Cells(3, 6) * (Cells(2, 6) - Cells(3, 6))
MsgBox ans

 

[Michael M]

OR something more relatable

Sub MM1()
Dim ans As Integer
ans = [F2] * ([F4] - [F3]) ^ 2 / [F3] * ([F2] - [F3])
MsgBox ans
End Sub

 

[honkin]

OR something more relatable

Sub MM1()
Dim ans As Integer
ans = [F2] * ([F4] - [F3]) ^ 2 / [F3] * ([F2] - [F3])
MsgBox ans
End Sub

Thanks so much for your reply, Michael. I was hoping not to have to do it as a VBA macro and define ranges, as have too many sheets to do it in and it is a single cell the formula will go in. I was hoping just for an inline formula which would calculate the result. The I can simply adjust the cell references as I add it to each sheet.

Do you believe it is possible at all to have it as a single cell macro, or is it your feeling going the VBA route will work best?

 

[Michael M]

Sorry, when you said a macro being developed, I thought you wanted code !!
Your formula works fine for me....but I'm not on a Mac, if that makes any difference

F2,F3 or F4 aren't zeros are they ??
Are you getting any result at all OR an error ?

Book1
	F	G	H
2	10
3	4		60
4	2
Sheet1

Cell Formulas
Range	Formula
H3	H3	= F2 * (F4 - F3) ^ 2 / F3 * (F2 - F3)

 

[honkin]

Sorry, when you said a macro being developed, I thought you wanted code !!
Your formula works fine for me....but I'm not on a Mac, if that makes any difference

Book1
	F	G	H
2	10
3	4		60
4	2
Sheet1

Cell Formulas
Range	Formula
H3	H3	= F2 * (F4 - F3) ^ 2 / F3 * (F2 - F3)

I suspected it may be something about using Excel for Mac. It is a pain at times.

No worries..thanks so much for your help

 

[Dave Patton]

Can you provide an example with numbers and your expected results.
You started talking about VBA and then stated you didn't want to use VBA.

What do you actually require?

 

[honkin]

Can you provide an example with numbers and your expected results.
You started talking about VBA and then stated you didn't want to use VBA.

What do you actually require?

Hi Dave

Apologies there, I didn't actually mention VBA, just clicked the wrong button to post the code, so instead of it showing the code as simply Code, it labelled it VBA Code.

I did just realise the cell I posted this formula into was formatted as Custom and hh:mm, so changed it to General formatting and it gave a very odd answer of 4.4575E+12

I also realised I was dividing the numerator into the denominator, rather than the other way around

Here is what I'm after as a fraction using words

Runners = F2
Expected Winners = F3
Winners = F4

So the new code looks like this

=F3*(F2-F3)/F2*(F4-F3)^2

I am still not finding it generates the same answer I get when manually calculating it all.

Here is what I mean:-

Runners = 122327
Expected Winners = 15112
Winners = 12846

The numerator equation would be =122327*(12846-15112)^2 The answer here is 628119297212
The denominator equation would be =15112*(122327-15112) The answer here is 1620233080

If I divide the denominator into the numerator , I get 387.67 when doing it manually

If I use the formula above in excel, I get 68010345459

=15112*(122327-15112)/122327*(12846-15112)^2

Any thoughts on the correct way to write this formula so it does what the fraction indicates?

Thanks so much

 

[Dave Patton]

T202101b.xlsm
	E	F	G
1
2	Runner	122327
3	Expected	15112
4	Winners	12846
5		387.67	387.67
6
5a

Cell Formulas
Range	Formula
F5	F5	=(F2*(F4-F3)^2)/(F3*(F2-F3))
G5	G5	=ROUND((F2*(F4-F3)^2)/(F3*(F2-F3)),2)

 

[Dave Patton]

search for
What is the hierarchy of mathematical operators in Excel
Excel precedence order