?
Solved

Calculate a value using a IF statement

Posted on 2000-03-05
20
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 4
  • +1
20 Comments
 
LVL 3

Accepted Solution

by:
mnewton022700 earned 200 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

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 28

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
 
LVL 28

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 28

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

770 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