Solved

Calculate a value using a IF statement

Posted on 2000-03-05
466 Views
I am working on a application and have had great difficulty with this calculation.
This has a choice of 2 seasons for rates
the first is standard. It is the second one I need a hint with.
when selected a clacualtion needs to take place.

if txtKilowatt = 0 then the cost equals a flat rate of 10.00 assigned to txtCost.text
basic flat rate of \$10.00 regardless of usage (even 0 kilowatthours)

or if txtKilowatt<= 400 then rate = .08 per kilowatt
or  if txtKilowatt>= 401 and less than 800 then rate = .06 per kilowatt
or if txtKilowatt>= 800 then rate = .04 per kilowatt

The values are calculated as follows eg.
txtKiloWatt.text = 850

8 cents per kilowatthour for the 1st 400 kilowatt-hours used

6 cents per kilowatthour for 401 to 800 (8 cents for the 1st 400)

and 4 cents per kilowatthour for 801 and above (plus the 8 and 6)

The final value is the rate * taxrate of 8%

any help would be greatly appreciated.

Regards Webgrrl
0
Question by:Webgrrl
• 10
• 5
• 4
• +1

LVL 3

Accepted Solution

mnewton022700 earned 50 total points
ID: 2586206
I think that the following should work.

dim usage as integer
dim cost as integer

usage = val(txtKiloWatt.txt)

if usage = 0 then
cost = 10
else
if usage > 400 then
usage = usage - 400
cost = 32

if usage > 400 then
usage = usage - 400
cost = cost + 24 + usage * 0.04
else
cost = cost + usage * 0.06
endif
else
cost = usage * 0.08
endif
endif

cost = cost * 1.08

txtCost.txt = str(cost)

0

Author Comment

ID: 2586356
Thanks for the response.

This is a good start but I am having a problem with the math.  I need to add the flat fee value of 10.00 to all.

eg.  if the value for kiloWatt was 2
the sum would be 10 + .16 = 10.16 * .08 = 10.97
or

the value of 450 would break down to:

400 * .08 = 32 + 10.00 base = 42
50 * .06 = 3
42 + 3 = 45 * .08 = 3.60 + 45 = 48.60

450 = 48.60
675=63.18   ect..

0

LVL 3

Expert Comment

ID: 2586387
In that case this should work:

dim usage as integer
dim cost as integer

usage = val(txtKiloWatt.txt)
cost = 10

if usage > 0 then
if usage > 400 then
usage = usage - 400
cost = cost + 32

if usage > 400 then
usage = usage - 400
cost = cost + 24 + usage * 0.04
else
cost = cost + usage * 0.06
endif
else
cost = cost + usage * 0.08
endif
endif

cost = cost * 1.08

txtCost.txt = str(cost)

0

LVL 27

Expert Comment

ID: 2586567
Hi
You don't need IF statement for this. You can do it with one string. To do this string shorter I add some variables
Dim a As Double, b As Double, c As Double
a = Val(txtKiloWatt)
b = Int((a - 1) / 400) * Sgn(a)
c = 10 + 32 * Sgn(b) + 24 * Sgn(Int(b / 2)) + (0.08 - 0.02 * b) * (a - 400 * b)
Text1 = Format(c, "Currency")
0

LVL 27

Expert Comment

ID: 2586603
Sorry, my code works only until 1200kWt
Correct code
Dim a As Double, b As Double, c As Double
a = Val(txtKiloWatt)
b = Int((a - 1) / 400) * Sgn(a)
c = 10 + 32 * Sgn(b) + 24 * Sgn(Int(b / 2)) + (0.08 - 0.02 * (1 + Sgn(b - 1))) * (a - 400 * (1 + Sgn(b - 1)))
Text1 = Format(c, "Currency")
Cheers
0

Author Comment

ID: 2589970
mnewton

This still does not do the math right for me.

It seems to round up or down the nearest value
eg. 49.00 for 450 KWh  should be 450 =48.60
63.00 for 675 should be  675=63.18

When winter is clicked it does calculate but I can't seem to get it.  but we are close.....

0

LVL 3

Expert Comment

ID: 2590003
Sorry, I should have declared the variables as doubles instead of integers:

dim usage as double
dim cost as double

0

Author Comment

ID: 2590062
oh oh that set it back to 0.00 and will not increase

using the if radio box ticked.

??

Thanks for all this help

0

Author Comment

ID: 2590073
oh oh that set it back to 0.00 and will not increase

using the if radio box ticked.

??

Thanks for all this help

0

LVL 3

Expert Comment

ID: 2590117
I just tried this in VB and it worked.

I created two text boxes "txtUsage" and "txtCost", and one button "Command1".

Private Sub Command1_Click()
Dim usage As Double
Dim cost As Double

usage = Val(txtUsage.Text)
cost = 10

If usage > 0 Then
If usage > 400 Then
usage = usage - 400
cost = cost + 32

If usage > 400 Then
usage = usage - 400
cost = cost + 24 + usage * 0.04
Else
cost = cost + usage * 0.06
End If
Else
cost = cost + usage * 0.08
End If
End If

cost = cost * 1.08
txtCost.Text = Str(cost)
End Sub
0

LVL 27

Expert Comment

ID: 2590121
Sorry, forgot again to multiple by 1.08
Correct code
Dim a As Double, b As Double, c As Double
a = Val(txtKiloWatt)
b = Int((a - 1) / 400) * Sgn(a)
c = 1.08 * (10 + 32 * Sgn(b) + 24 * Sgn(Int(b / 2)) + (0.08 - 0.02 * (1 + Sgn(b - 1))) * (a - 400 * (1 + Sgn(b - 1))))
Text1 = Format(c, "Currency")
Cheers
PS Webgrll: I don't need points for this question, mnewton is first and his code is OK, so all points (if you accept) is his. I only have some free time and show you another way

0

Author Comment

ID: 2590175
Okay now I feel bad I had the code under the change event of kwh.
When I just placed it under the click event of
Optwinter it did not work at all.

It placed the base price but not did not claculate when the number was increased by the scroll bar or manual entry.

Sorry about all this time you are taking.

ARK

Thanks for the tips as you can see I need it. I tried yours too and I couldn't get it to go.  But thanks I understood the logic of it.

Both of you are  a great help.
0

Author Comment

ID: 2590187
Okay now I feel bad I had the code under the change event of kwh.
When I just placed it under the click event of
Optwinter it did not work at all.

It placed the base price but not did not claculate when the number was increased by the scroll bar or manual entry.

Sorry about all this time you are taking.

ARK

Thanks for the tips as you can see I need it. I tried yours too and I couldn't get it to go.  But thanks I understood the logic of it.

Both of you are  a great help.
0

Author Comment

ID: 2590215
mnewton

I see where it may have got confused.

when I change the kwh and then click it updates the field.

The app would be:

click summer or winter

then if winter then do the calc you have provided.

if summer clicked all  kwh are calculated by .01 and then the tax rate is added in.

but the season would be clicked first.

then the rate is calculated when the kwh is changed.
I hope this makes more sense.

Thanks

Webgrrl
0

LVL 3

Expert Comment

ID: 2590287
It sounds like you need to add this calculation in the Change method of the kwh text box.

You should probably create a separate function to do the calculation and then just call it from wherever you need to.
0

LVL 27

Expert Comment

ID: 2590312
Hi
Start project, add two textboxes (txtKiloWatt and txtPrice) and two option buttons (optSummer and optWinter) to your form. Paste this code to your form code. Press F5. Enjoy.

txtKiloWatt = "0"
End Sub

Private Sub optSummer_Click()
CalcPrice
End Sub

Private Sub optWinter_Click()
CalcPrice
End Sub

Private Sub txtKiloWatt_Change()
CalcPrice
End Sub

Private Sub CalcPrice()
Dim a As Double, b As Double, c As Double
a = (Abs(optSummer) * 0.01 + 1) * Val(txtKiloWatt)
b = Int((a - 1) / 400) * Sgn(a)
c = 1.08 * (10 + 32 * Sgn(b) + 24 * Sgn(Int(b / 2)) + (0.08 - 0.02 * (1 + Sgn(b - 1))) * (a - 400 * (1 + Sgn(b - 1))))
txtPrice = Format(c, "Currency")
End Sub
'PS. the same way you can change mnewton code
Cheers
0

LVL 14

Expert Comment

ID: 2590468
Another Approach...

Dim dblBreak as Double
Dim dblCost as Double
Dim dblPrice as Double
Dim dblUsage as Double
Dim dblWork as Double

dblCost = 10.00
dblPrice = .04
dblUsage = Value(txtKiloWatt)

For dblBreak = 800 To 0 Step -400
dblWork = dblUsage - dblBreak
If dblWork > 0 _
Then
dblCost = dblCost _
+ (dblWork * dblPrice)
dblKiloWatt = dblUsage - dblWork
End If
dblPrice = dblPrice + .02
next dblBreak

dblCost = dblCost * 1.08

-------------------------

Gawd.. to be point rich like Ark.. <wanton sigh>.. Anyone want to marry a millionaire?.. LOL
0

Author Comment

ID: 2594244
The knowledge I have learned hear the last two days has made more sence than a 6 month course I took.    I was trying one of the aditional lessons that we never did.

I wish there was a way to earn points so I can continue to learn now that I think i am starting to understand it better.

Thanks a million for the A++ help.

And also to Ark thanks for the extra hints.  I did use you calc idea and also a calc2  it worked well.

I hope to have the oportunity to use this again.

Webgrrl

0

Author Comment

ID: 2594378
Hi Sorry to ask this again but I thought I had it but it still does not work.

Here is what I have

Private Sub CalcPrice1()    ' Summer'
Dim subcost As Double
Dim subtax As Double
Dim usage1 As Double

usage1 = Val(txtKiloWatt.Text)
subcost = usage1 * 0.1
subtax = subcost * 0.08
costs = subtax + subcost
txtPrice = costs
End Sub

Private Sub Clear_Click()
txtCustName.Text = ""
txtPrice.Text = ""
txtKiloWatt.Text = ""
End Sub

Private Sub cmdClear_Click()

txtCustName.Text = ""
txtPrice.Text = ""
txtKiloWatt.Text = ""

End Sub

Private Sub cmdExit_Click()
End
End Sub

Private Sub Exit_Click()
End
End Sub

txtKiloWatt = "0"
With HScroll1
.Min = 0
.Max = 5000
.LargeChange = 500
.SmallChange = 50
End With

End Sub

Private Sub Help_Click()
frmHelp.Show
End Sub

Private Sub HScroll1_Change()
txtKiloWatt.Text = CStr(HScroll1.Value)
End Sub

Private Sub OptSummer_Click()
CalcPrice1
End Sub

Private Sub OptWinter_Click()

CalcPrice

End Sub

Private Sub Summary_Click()

frmSummary.Show

End Sub

Private Sub txtKiloWatt_Change()
txtPrice.Text = Format(txtPrice, "Currency")

If OptWinter.Enabled = True Then
CalcPrice
End If
If OptWinter.Enabled = True Then
CalcPrice1
End If

'scroll bar values
If Val(txtKiloWatt.Text) > HScroll1.Max Then
MsgBox "Over the Scroll bar maximum"
HScroll1.Value = HScroll1.Max
txtKiloWatt.Text = HScroll1.Max

ElseIf Val(txtKiloWatt.Text) < HScroll1.Min Then
MsgBox "Under the Scroll bar minimum"
HScroll1.Value = HScroll1.Min
txtKiloWatt.Text = HScroll1.Min

Else
HScroll1.Value = Val(txtKiloWatt.Text)
End If
End Sub

Private Sub CalcPrice()   'Winter'

Dim usage As Integer
Dim cost As Integer

usage = Val(txtKiloWatt.Text)
cost = 10
If usage > 0 Then

If usage > 400 Then
usage = usage - 400
cost = cost + 32

If usage > 400 Then
usage = usage - 400
cost = cost + 24 + usage * 0.04
Else
cost = cost + usage * 0.06
End If
Else
cost = cost + usage * 0.08
End If

cost = cost * 1.08
txtPrice.Text = Str(cost)

End If

End Sub

Private Sub txtPrice_Change()
txtPrice.Text = Format(txtPrice.Text, "currency")
End Sub

I am going crazy with this.
thanks

webgrrl
0

Author Comment

ID: 2594474
Blush

I figured it out.  for now thanks ...   I think it is spooked : )
0

Featured Post

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were câ€¦
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most pâ€¦
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can launâ€¦
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process fromâ€¦