Excel Formula

[Momofthree]

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.

 

[Cymae]

where the formula in C1 is:

=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.

 

[AtWork]

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

 

[Aladin Akyurek]

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)

 

[mvptomlinson]

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.

 

[AtWork]

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))

 

[Momofthree]

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.

 

[Kaps_mr2]

=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

<table style="border-collapse: collapse; width: 96pt;" width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">COM</td> <td style="width: 48pt;" width="64" align="right">3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">GOV</td> <td align="right">1.5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">NPROFIT</td> <td align="right">2</td> </tr> </tbody></table>

 

[Aladin Akyurek]

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:

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

 

[Momofthree]

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