Vlookup formula

[alexaa]

I need help on how to do a vlookup formula.. The question is to add a column named Water Bill to the table, and then enter a formula to calculate the water bill based on the following rules: The billing rate varies based on the type of customer (Cust Type column). The billing rate is $3, $2, or $1.50 per thousand gallons billed, depending on the customer type (see the lookup table in cells B2:C5 of the Billing Rate worksheet). For example, a commercial customer using 75,000 gallons has a water bill of $225 (75 x $3), whereas a government customer using 100,000 gallons pays $150 (100x $1.50). A commercial customer using 15,000 gallons has a water bill of $0n (see Gal Used rule in question 3).

 

[BenMiller]

Could you email me please? A lot easier than jumping back and forth. Thanks!

 

[mmodroger]

I need help on how to do a vlookup formula.. The question is to add a column named Water Bill to the table, and then enter a formula to calculate the water bill based on the following rules: The billing rate varies based on the type of customer (Cust Type column). The billing rate is $3, $2, or $1.50 per thousand gallons billed, depending on the customer type (see the lookup table in cells B2:C5 of the Billing Rate worksheet). For example, a commercial customer using 75,000 gallons has a water bill of $225 (75 x $3), whereas a government customer using 100,000 gallons pays $150 (100x $1.50). A commercial customer using 15,000 gallons has a water bill of $0n (see Gal Used rule in question 3).

Excel Workbook
	A	B	C	D	E	F	G
1	Customer	Type	Water Used	Water bill		Sheet 2
2	A	commercial	75000	225		Type	Rate
3	B	government	100000	150		government	$1.50
4	C	commercial	15000	45		commercial	$3.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=VLOOKUP(B2,$F$3:$G$4,2,0)*C2/1000
D3	=VLOOKUP(B3,$F$3:$G$4,2,0)*C3/1000
D4	=VLOOKUP(B4,$F$3:$G$4,2,0)*C4/1000

 

[BenMiller]

yes, thank you so much; also how do i highlight only the top 15 % of the Total bill amounts with a yellow background color. Filter the table so that only the top 15 percent are displayed. Sort the filtered table by largest to smallest

Put an auto filter across the top row, and sort descending on whichever column you'd like. For the highlighting, use conditional formatting. Highlight that column, then go to Formatting>Conditional formatting.

For more details look at this thread.

 

[vfern015]

Hi,
I need help with this same problem?
Did you ever figure out the correct formula??
thanks

Hello,

You can try this formula:

=VLOOKUP(C2,$H$2:$I$4,2,FALSE)*B2

Where C2 contains the CUST TYPE, range H2:I4 contain the legend (Commercial = 3, Government = 1.5, etc) 2 = the value to return, and B2 the water used (in thousand gallons).

Good luck!

 

[T. Valko]

Hi,
I need help with this same problem?
Did you ever figure out the correct formula??
thanks

Can you provide us with some details?

 

[vfern015]

Hello,

You can try this formula:

=VLOOKUP(C2,$H$2:$I$4,2,FALSE)*B2

Where C2 contains the CUST TYPE, range H2:I4 contain the legend (Commercial = 3, Government = 1.5, etc) 2 = the value to return, and B2 the water used (in thousand gallons).
There is another speadsheet with a vertical cololum.
I just dont know how to divide by 1000.

Can you provide us with some details?

 

[T. Valko]

Hello,

You can try this formula:

=VLOOKUP(C2,$H$2:$I$4,2,FALSE)*B2

Where C2 contains the CUST TYPE, range H2:I4 contain the legend (Commercial = 3, Government = 1.5, etc) 2 = the value to return, and B2 the water used (in thousand gallons).
There is another speadsheet with a vertical cololum.
I just dont know how to divide by 1000.

Like this...

=VLOOKUP(C2,$H$2:$I$4,2,0)*B2/1000