Rounding problem

[Greg Snidow]

Greetings all. I am having a rounding problem, I think. There are two
values to be evaluated, "Theta" and "Slack". If Theta = 1 *and* Slack = 0
then the rating should be "Efficient". If Theta = 1 *and* Slack > 0 then the
rating should be "Weak Efficient". If Theta > 1, regardless of slack, the
rating should be "Inefficient". I can't get the macro to work for the
inefficient cases. I have set up a small example of how I am doing it.
Basically I am passing a user entered level of decimal places, and rounding
Theta and Slack with the passed value as the decimal places. Naturally, the
ratings change based on the decimal sensitivity. For example, if Theta =
1.00095 and Slack = .0046, then at 0, and 1 decimal places, the rating should
be "efficient". However, at 3 decimal place rounding, Theta is still = 1,
but slack becomes .05, which is greater than 0, so rating should be "weak
efficient". At 3 or more decimal places, theta > 1, so rating should be
"inefficient" regardless of slack. The below is how I am trying to do it,
and, once again, it does not work for inefficient cases. Does anyone have
any ideas? Thank you

Greg

*****************************************************
Sub RoundEval()

Dim Theta As Double
Dim Slack As Double
Dim DS As Integer

'Set the column headings
Range("A1") = "Decimal Places"
Range("B1") = "Theta Rounded"
Range("C1") = "Slack Rounded"

Theta = 1.00095
Slack = 0.046

'Loop through from 0 to 5 decimal places, and
'populate the values.
For i = 0 To 5 Step 1
'Set decimal sensitivity(DS) = i
DS = i
'Populate the rounded values of Theta and Slack and
'the current level of decimal sensitivity
Range("A" & i + 2).Value = i
Range("B" & i + 2).Value = Abs(0 - Round(Theta, DS))
Range("C" & i + 2).Value = Abs(0 - Round(Slack, DS))
Select Case True
'If the rounded value of Theta = 1 *AND*
'the rounded value of Slack = 0
'Should be efficient for 0 and 1 decimal places
Case Abs(0 - Round(Theta, DS)) = 1 And _
Abs(0 - Round(Slack, DS)) = 0
Range("D" & i + 2).Value = "Efficient"
'If the rounded value of Theta = 1 *AND*
'the rounded value of slack > 0
'Should be WeakEf for 2 decimal places
Case Abs(0 - Round(Theta, DS)) = 1 And _
Abs(0 - Round(Slack, DS)) > 0
Range("D" & i + 2).Value = "WeakEf"
'If the rounded value of Theta > 1
'Should be Inefficient for 3 or more decimal places,
'but it is not working for these
Case Abs(0 - Round(Theta)) > 1
Range("D" & i + 2).Value = "Inefficient"
End Select
Next i
End Sub

 

[Greg Snidow]

Just realized a mistype. Rating should be "weak efficient" for *2* decimal
places, not 3 as stated above. Sorry for the confusion.

 

[Mike H]

Greg,

In your last case statement you rounding to no decimal places so Theta is
never greater than 1 for the posted data,

Try this

Case Abs(0 - Round(Theta, DS)) > 1
Range("D" & i + 2).Value = "Inefficient"

Mike

 

[Greg Snidow]

DOH! Thanks Mike for the second set of eyes. I knew it was something simple.

Greg

 

[Rick Rothstein]

Mike has given you what should be the answer to your question, but I am
curious, why are you using this construction (this comment applies to both
Theta and Slack construction)...

Abs(0 - Round(Theta, DS))

If your Theta and Slack numbers are always positive, then you can use this
directly...

Round(Theta, DS)

and if they are not always, then you could simply use this instead...

Abs(Round(Theta, DS))

 

[Mike H]

Greg,

Thanks for the feed back and glad I could help.

I have a general comment on your code. You are calculating the values of
Theta and Slack twice, once to populate the values into columns B & C and a
second time in the select case statement and this is both inefficient and led
to the error you had.

Instead of the second calculation in the Select statement you could refer
back to the previously calculated value written to the worksheet or even
better hold the value in a variable after the first calculation.

Mike

 

[Greg Snidow]

Point taken. However, in the real workbook, I am not writing the rounded
values to a cell. I only did that for this example so I could see what was
going on.

 

[Greg Snidow]

Rick, I did that because I could not get it to work consistently. However,
now that I have a working set up, I am going to try it without 1-... and see
if it still works. There were certain values of Theta where if I had it as
you suggested, it gave the wrong label, but by adding the 1- to the equation
it gave the correct label. I can't explain it, but its almost as if Excel
was doing some rogue rounding somewhere in there. Anyhow, thanks for the
response.

Greg

 

[Rick Rothstein]

You just wrote "1-" but your original code showed you using "0-"?

 

[Greg Snidow]

Rick, you are right. I took out the abs(1- and it works fine now. I'm not
sure why it was not working correctly previously, but there were other issues
with it, all of which seem to be okay now. Thanks.

Greg