JCIrish said:
Thanks for the help, Joe User. I'm using Excel 2007 (I don't know
how to determine the VBA version. How can I find that out?).
I cannot say for sure, since I know that Excel 2007 changed navigation in
many cases. In Excel 2003 VBA, I click on Help > About Microsoft Visual
Basic.
Your solution works
better for me if I omit the INT and just use the ratio (x/y)
Then you did not want Quotient in the first place. Alternatively, you are
misleading yourself by formatting G40 to have zero decimal places or because
you happen to choose integers in E20 and E38 such that it does not matter
(e.g. 16 and 8).
I suggest that you try your macro with E20 set to 16, E38 set to 10, and be
sure that the result in G40 is displayed as you wish, be it 1 (Quotient),
1.6, or 2 (1.6 rounded).
Also note that in the latter two cases, the true value in the cell is 1.6,
even if it displays as 2. So =2*G40 will display as 3.2 or 3 (formatted
with zero decimal places) instead of 4.
The point is: QUOTIENT returns the truncated integer part of the division
of two numbers, whereas x/y returns the exact result of the division. Int
also returns the truncated integer of its argument.
QUOTIENT(16,10) and Int(16/10) result in 1, whereas 16/10 is about 1.6.
Heads-up: Someone might suggest the following as an alternative solution:
Dim Ratio as Long
Although that does convert x/y to an integer, it is the __rounded__ result
of the division, not the truncated integer that QUOTIENT returns. So,
again, Ratio=16/10 results in the integer 2 instead of the integer 1.
The correctness of any solution depends on what you want as a result. I
ass-u-me-d you wanted the truncated integer since you wrote
WorksheetFunction.Quotient in the first place.
----- original message -----
JCIrish said:
Thanks for the help, Joe User. I'm using Excel 2007 (I don't know how to
determine the VBA version. How can I find that out?). Your solution works
better for me if I omit the INT and just use the ratio (x/y) as suggested
by
FloMM2. Thanks.
jcirish
Joe User said:
JCIrish said:
This seems like it should be easy, but i'm baffled!!
[....]
The only value I get is zero.
So am I, but not for the reason you might think.
What version of Excel/VBA are you using?
Ratio = Application.WorksheetFunction.Quotient(x, y)
WorksheetFunction.Quotient is not valid in my revision of Excel 2003 /
VBA
6.3. So I get a VBA error, not zero.
The simple fix, indeed the better implementation unless you are trying to
test the VBA implementation of WorksheetFunction.Quotient, assuming that
works in your version of Excel/VBA:
Ratio = Int(x / y)
I want to put the quotient of Cells E20 and E38, both of which
contain positive integers, into Cell G40. The only value I get is zero.
What values have you tried in E20 and E38?
Zero is the correct answer when E20 < E38.
When E20 is 12 and E38 is 5, G40 is 2 with "Ratio=Int" fix above.
----- original message -----
JCIrish said:
This seems like it should be easy, but i'm baffled!! In the code
below,I
want
to put the quotient of Cells E20 and E38, both of which contain
positive
integers, into Cell G40. The only value I get is zero. The code runs ok
but
obviously produces the wrong result. Can anyone tell me why this is?
What
am
I missing? Thanks.
Sub ComputeAssetRatios()
Application.Worksheets("AssetAllocation").Activate
Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z
Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print
Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print
Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio
Debug.Print
End Sub
.
