Solved

Calculate a value using a IF statement

Posted on 2000-03-05
20
466 Views
Last Modified: 2010-05-02
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
Comment
Question by:Webgrrl
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 3

Accepted Solution

by:
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

by:Webgrrl
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

by:mnewton022700
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

by:Ark
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

by:Ark
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

by:Webgrrl
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

by:mnewton022700
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

by:Webgrrl
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

by:Webgrrl
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

by:mnewton022700
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Expert Comment

by:Ark
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

by:Webgrrl
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

by:Webgrrl
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

by:Webgrrl
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

by:mnewton022700
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

by:Ark
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.

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
0
 
LVL 14

Expert Comment

by:wsh2
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

by:Webgrrl
ID: 2594244
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

0
 

Author Comment

by:Webgrrl
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

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
0
 

Author Comment

by:Webgrrl
ID: 2594474
Blush

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now