1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel

Discussion in 'Business Applications' started by Gingerd10, Oct 7, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Gingerd10

    Gingerd10 Thread Starter

    Joined:
    Oct 7, 2008
    Messages:
    8
    I am working on a formula for a water bill based on rules, Gallons used must be greater than 25,000 otherwise the bill is 0
    the billing rate varies based on COM, NPROFIT,GOV
    COM is $3.00, NPROFIT is $2.00 and GOV is $1.50 per thousand gallons used. I have figured a formula for
    I =IF(AND([Cust Type]="NPROFIT",[Gal Used]>25000),G2/1000*'Billing Rate'!C4,0)
    =IF(B5="GOV",G5/1000*'Billing Rate'!C5,0)
    =IF(B73="COM",G73/1000*'Billing Rate'!C3,0)

    I dont know how to combine these and I also dont know how to get the bill to be 0 if it is under 25000 gallons.

    Any help?

    This is way confusing
    Thanks
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Not sure what your formulas are, the references don't look right. Did you copy/paste from another location? With the information you've given, you may want to try something like this...

    =IF(A5<25000,0,A5/1000*IF(B5="COM",'Billing Rate'!C3,IF(B5="NPROFIT",'Billing Rate'!C4,IF(B5="GOV",'Billing Rate'!C5,1))))

    If this doesn't work or you need more please post back and let us know more details. A sample file might help as well.

    HTH
     
  3. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi Gingerd10,

    Welcome to the forums.

    Down to business, the basics of the IF statement is "IF Condition", then TRUE, else FALSE. Now what you need to do is called a NESTED IF statement, as in nest other if statements between others.

    So start with:
    =IF([Gal Use]<25000, 0, ...)

    So if Gal Used is under 25000 then value is 0 else do something else.

    For the ... put in your other if statements, so firstly the NPROFITs:
    IF([Cust Type]="NPROFIT",G2/1000*'Billing Rate'!C4,...)

    So you have:
    =IF([Gal Use]<25000, 0,IF([Cust Type]="NPROFIT",G2/1000*'Billing Rate'!C4,...)

    If less then 2500, value is 0, else (over 25000) if cust type = NPROFIT, then calculate value, else do something else.

    For the last example I'll add both of your existing formula
    =IF([Gal Use]<25000, 0,IF([Cust Type]="NPROFIT",G2/1000*'Billing Rate'!C4,IF([Cust Type]="GOV",G5/1000*'Billing Rate'!C5,IF([Cust Type]="COM",G73/1000*'Billing Rate'!C3,"ERROR")

    In Pseudo programming code you would write it:
    Code:
    IF Gal Use < 25000 Then
    [INDENT]Value = 0[/INDENT]
    Else
    [INDENT]IF Cust Type = NPROFIT Then
    [INDENT]Value = G2/1000*'Billing Rate!C4[/INDENT]
    ElseIF Cust Type = GOV Then
    [INDENT]Value = G5/1000*'Billing Rate!C5[/INDENT]
    ElseIF Cust Type = COM Then
    [INDENT]Value = G73/1000*'Billing Rate!C3[/INDENT]
    Else
    [INDENT]Error[/INDENT]
    [/INDENT]
    End IF
    
    When converting this into an Excel formula, we don't write then and else, we use a comma, and in stead of elseif, we nest an if statement after the else part of the formula.

    If statement, (TRUE) then, (FALSE) else.

    I hope this is a good explanation of how this works. Excel if statements were probably how I initially got into programming.
     
  4. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Zack Barresse is a bit more refined, but essential does the same thing.
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    lol!!! :d
     
  6. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Oops meant to put: Zack Barresse's a bit more refined...

    LOL. ;)
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hey slurp, you should listen to MRdNk, I think they're onto something here.. ;)

    Refined. Just rolls right off the tongue, doesn't it? :D
     
  8. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    OH NO! What have I done? You'll be adding it, to your Signature next.
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Probably "refined" like sugar, flour or this....
    :rolleyes:
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    MRdNK, have you ever checked out his signature at the VBA Express Forum?
    Zack's a self-proclaimed Urban Myth! Now a Refined Urban Myth!!!!
     
  11. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    "The Refined Feline - The Refined Litter Box" - is amazing! lol. :D - gonna email that to my work email address.

    "Urban Myth" - :rolleyes:
     
  12. Gingerd10

    Gingerd10 Thread Starter

    Joined:
    Oct 7, 2008
    Messages:
    8
    Ok that didn't work..Maybe I need to explain it differently.
    Ok I am calculating a water bill based on rules if a customers bill is waived place 0 in water bill column
    gal used (gallons used) must be greater than 25,000 gallons during the quarter otherwise its o
    for all other accounts the billing rate varies based on the type of customer
    The billing rates are $3,[email protected], or $1.50 per thousand gallons used depending on the type of customer for example a commercial customer using 75,000 gallons has a water bill of $225(75*3), whereas a government customer using 100,000 gallons pays $150(100*$1.50) A commercial customer using 15,000 gallons has a water bill of 0

    Maybe this helps some.I will be enetering a formula in Water Bill
    CustomerName Cust Type Bill Waived Taxable Billing Year Billing Quarter Gal Used Water Bill Tax Total Bill Red Lake Chapter NPROFIT No Yes 2010 1 108,300 $

    $ $ Amity Church NPROFIT Yes No 2010 1 55,160 $ $ $
     
  13. Gingerd10

    Gingerd10 Thread Starter

    Joined:
    Oct 7, 2008
    Messages:
    8
    I don't think the bottom of my info. looks right
    that is how my spreadsheet looks somewhat.
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Ginger, if you can upload a copy of your file (click Go Advanced, then go to Manage Attachments and a pop-up will open that will allow you to browse for the file and upload it.) If there is sensitive data in it, replace it with nonsense data of the correct type, so we can see the layout. Also, please write instructions into the file including what the results you want are for several scenarios.
    If you find you can't upload a file, please click on my name and go to my profile and send me an email with your return address. I will respond and then you can send me the file and I will upload it for you.
    If you have to do that, please also include the URL of this thread:
    http://forums.techguy.org/business-applications/757065-excel.html#post6183662
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Doesn't look entirely right. As far as I can tell you're working with 10 columns.

    Sometimes the best way to construct a complex formula is to do it in stages and in order of "applicability" -- which Zack's formula does by identifying the factor that applies to all cases (a "constant") first.

    Zack's formula simplified says:

    =IF(GalUsed<25000,0,x)

    I'd switch that around (to better reflect what you said first) to:

    =IF(GalUsed>25000,x,0) -- see H2 of the attached.

    So that's your first constant. Your second is, where the bill is not waived, GalUsed must be divided by 1000. Which is:

    =IF(GalUsed>25000,GalUsed/1000,0)

    See H3 of the attached.

    Then you have the third and final component of the formula, which is actually your only variable; where the bill is not waived, GalUsed must be divided by 1000 and multiplied by the rate according to CustType. Which is:

    =IF(GalUsed>25000,GalUsed/1000*CustType,0)

    You can derive rate according to CustType by using a small lookup table (yellow cells of the attached). Bolt those components together to get:

    =IF(GalUsed>25000,GalUsed/1000*VLOOKUP(CustType,value from column 2 of lookup table,FALSE),0)

    See H4 of the attached, which is the "final version". Go through the various CustTypes in B4 to see it "in action".

    The lookup table can be wherever you want, just have it somewhere "out of the way".

    HTH :)
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757065

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice