Results 1 to 10 of 10

Excel Formula

This is a discussion on Excel Formula within the Excel Questions forums, part of the Question Forums category; I need help with a formula for a billing rate. The rate varies based on the type of customer. The ...

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    13

    Default Excel Formula

    I need help with a formula for a billing rate.

    The rate varies based on the type of customer. The rate is $3, $2, $1.50 per thousand gallons of water used. Customers are COM-$3, NPROFIT-$2, GOV-$1.50. I am not sure which formula to use. Also, since I need to figure it per thousand and I have a gal. usage of 108,300 it is confusing. Thank you.

  2. #2
    Board Regular
    Join Date
    Oct 2008
    Location
    Brisbane, Australia
    Posts
    69

    Default Re: Excel Formula



    where the formula in C1 is:

    Code:
     
    =IF(A2=$F$2,(B2/$I$2)*$G$2,IF(A2=$F$3,(B2/$I$2)*$G$3,IF(A2=$F$4,(B2/$I$2)*$G$4,"error")))
    It's a little messy, but it works and your values are dynamic.

  3. #3
    Board Regular
    Join Date
    Dec 2006
    Location
    Chicago
    Posts
    128

    Default Re: Excel Formula

    What type of format, positioning on your workbook, is the data starting?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,681

    Default Re: Excel Formula

    Quote Originally Posted by Momofthree View Post
    I need help with a formula for a billing rate.

    The rate varies based on the type of customer. The rate is $3, $2, $1.50 per thousand gallons of water used. Customers are COM-$3, NPROFIT-$2, GOV-$1.50. I am not sure which formula to use. Also, since I need to figure it per thousand and I have a gal. usage of 108,300 it is confusing. Thank you.
    A2: 108,300

    B2: NPROFIT

    In C2 enter:

    =(A2/1000)*VLOOKUP(B2,{"COM",3;"NPROFIT",2;"GOV",1.50},2,0)

  5. #5
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638

    Default Re: Excel Formula

    Hi momofthree,

    Not sure how your data is laid out, however, let's assume that your usage (108,300) is in cell A1, and your customer type is in B1.

    In C1, use the formula:

    =INT(A1/1000)*LOOKUP(B1,{"COM","GOV","NPROFIT"},{3,1.5,2})

    This will divide the value in A1 by 1000 (108.3) then retrieve just the integer portion (108). It then multiplies it by either 3, 1.5 or 2 based on the customer type found in B1. In the lookup array, "COM", "GOV" and "NPROFIT" are in alphabetical order, and the corresponding result array values are in their respective order.

    Hope that helps.

  6. #6
    Board Regular
    Join Date
    Dec 2006
    Location
    Chicago
    Posts
    128

    Default Re: Excel Formula

    another alternate would be the following

    you have a list of customers in column A

    your customer type codes are in column B

    your total water usage is in column C

    what you want is in column D

    in the range E1:F3 put a grid showing the relation between type codes and rates ie e1=COM and F1=3 and so on for e2:f3

    in D put the following formula and auto fill it down as long as you need.

    =(vlookup(B1,$E$1:$F$3,2)*INT(C1/1000))

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    13

    Default Re: Excel Formula

    Thank you for the help but the forumlas did not work. I will retype to see if I missed something:

    Calculate the water bill based on the following rules:

    If a customer's bill is waived, place 0 in the water bill column

    Gal Used must be greater than 25,000 gallons otherwise, the bill is 0

    For all other accounts the billing rate varies based on the type of customer. The billing rates/customers are COM $3, GOV $1.50, NPROFIT $2 per THOUSAND gallons used depending on the type of customer. Example a COM customer using 75,000 gallons has a bill of $225(75x$3). A COM customer using 15,000 gallons has a water bill of 0. I have tried the IFAND formula, VLOOKUP, setting a Criteria but can't seem to get it right. My worksheet is set up as follows:

    Customer Name A1
    Customer Type B1
    Bill Waived C1
    Taxable D1
    Billing Year E1
    Billing Qtr F1
    Gal Used G1
    Water Bill H1
    Tax I1
    Total Bill J1

    Any help would be greatly appreciated Thanks.

  8. #8
    Board Regular
    Join Date
    Jul 2008
    Location
    Surrey, UK
    Posts
    1,475

    Default Re: Excel Formula

    =IF(C2<25000,0,VLOOKUP(C1,$H$1:$I$3,2,FALSE)*C2/1000)

    Where C2 is the number of gallons of water.
    C1 is the customer type (COM,GOV,NPROFIT)

    and the lookup table is in the range H1:I3 as below. thanks Kaps

    COM 3
    GOV 1.5
    NPROFIT 2
    Read my Excel blog on

    http://simplyspreadsheets.wordpress.com/

    For more ways I can help you with Excel :-

    www.simplyspreadsheets.co.uk

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,681

    Default Re: Excel Formula

    Quote Originally Posted by Momofthree View Post
    Thank you for the help but the forumlas did not work. I will retype to see if I missed something:

    Calculate the water bill based on the following rules:

    If a customer's bill is waived, place 0 in the water bill column

    Gal Used must be greater than 25,000 gallons otherwise, the bill is 0

    For all other accounts the billing rate varies based on the type of customer. The billing rates/customers are COM $3, GOV $1.50, NPROFIT $2 per THOUSAND gallons used depending on the type of customer. Example a COM customer using 75,000 gallons has a bill of $225(75x$3). A COM customer using 15,000 gallons has a water bill of 0. I have tried the IFAND formula, VLOOKUP, setting a Criteria but can't seem to get it right. My worksheet is set up as follows:

    Customer Name A1
    Customer Type B1
    Bill Waived C1
    Taxable D1
    Billing Year E1
    Billing Qtr F1
    Gal Used G1
    Water Bill H1
    Tax I1
    Total Bill J1

    Any help would be greatly appreciated Thanks.
    I think you had already a bunch of working formulas... Applying my earlier suggestion to your setup, we get:

    H1:
    Code:
    =IF(C1="Yes",0,
        IF(G1<=25000,0,
         (G1/1000)*VLOOKUP(B1,{"COM",3;"NPROFIT",2;"GOV",1.50},2,0)))

  10. #10
    New Member
    Join Date
    Nov 2008
    Posts
    13

    Default Re: Excel Formula

    Thank you very much I really appreciate it. It is now working.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com