Webgrrl
asked on
Calculate a value using a IF statement
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
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")
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")
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
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
ASKER
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.....
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.....
Sorry, I should have declared the variables as doubles instead of integers:
dim usage as double
dim cost as double
dim usage as double
dim cost as double
ASKER
oh oh that set it back to 0.00 and will not increase
using the if radio box ticked.
??
Thanks for all this help
using the if radio box ticked.
??
Thanks for all this help
ASKER
oh oh that set it back to 0.00 and will not increase
using the if radio box ticked.
??
Thanks for all this help
using the if radio box ticked.
??
Thanks for all this help
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
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
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
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
ASKER
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.
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.
ASKER
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.
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.
ASKER
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
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
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.
You should probably create a separate function to do the calculation and then just call it from wherever you need to.
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.
Private Sub Form_Load()
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
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.
Private Sub Form_Load()
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
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
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
ASKER
Thanks for all your help.
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
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
ASKER
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
Private Sub Form_Load()
txtKiloWatt = "0"
With HScroll1
.Min = 0
.Max = 5000
.LargeChange = 500
.SmallChange = 50
End With
End Sub
Private Sub Help_Click()
frmHelp.Show
Unload frmElect
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
Unload frmElect
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
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
Private Sub Form_Load()
txtKiloWatt = "0"
With HScroll1
.Min = 0
.Max = 5000
.LargeChange = 500
.SmallChange = 50
End With
End Sub
Private Sub Help_Click()
frmHelp.Show
Unload frmElect
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
Unload frmElect
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
ASKER
Blush
I figured it out. for now thanks ... I think it is spooked : )
I figured it out. for now thanks ... I think it is spooked : )
ASKER
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..