Help with If/And Nesting Problem

[mdavis31]

Hello, I am so fortunate to have found your site!
I am sooo lost on this Excel problem:
Calculate the water bill on the following rules:
1.If a customer's bill is waived,place a 0 in the water bill column.
2.Gallons Used must be greater than 25,000 gallons during the quarter,otherwise the water bill will be 0.
3.For all other accounts ,the billing rate varies based on type of customer. The billing rate is $3,$2,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(75X$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 0.
The types of customers and rates are:
COM-$3
NPROFIT-$2
GOV-$1.50
The way it is set up I cannot just click a cell and a letter and # show up, I have to put the brackets and click on Customer Type, Gallons Used,Bill Waived(which has a Yes or No in the columns), and I am trying to calculate all this in the Water Bill Column. I am so lost and frustrated any help would be appreciated! Thanks Bunches! Melissa
Ps I am using Microsoft Office 2007

 

[mdavis31]

Hello,
For some reason that made it worse Sorry I sent a copy to show what came up when I put the second formula in, some of them came up 0 when they shouldn't, Like H:2? Thanks Again for all your help! Melissa

 

[jasonb75]

I can't see the formula in H2 to see where you're going wrong, hopefully this should clarify it for you.

Excel Workbook
	A	B	C	D	E	F	G	H
1	CustomerName	Cust Type	Bill Waived	Taxable	Billing Year	Billing Quarter	Gal Used	Water Bill
2	Red Lake Chapter	NPROFIT	No	Yes	2010	1	108,300	216.6
3	Amity Church	NPROFIT	Yes	No	2010	1	55,160	0
4	Church of Christ	NPROFIT	Yes	No	2010	1	32,430	0
5	NM State Highway	GOV	No	No	2010	1	95,660	143.49
6	Spot Free Power Wash	COM	No	Yes	2010	1	281,200	843.6
7	CIT Apartment Complex	COM	No	Yes	2010	1	257,000	771
8	Housing Agency	GOV	No	No	2010	1	23,300	0
9	Pinon Pizza Edge	COM	No	Yes	2010	1	81,110	243.33
10	Department of Agriculture	GOV	No	No	2010	1	2,783,000	4174.5
11	Chevron Station	COM	No	Yes	2010	1	1,131,600	3394.8
12	O B Trailer Service	COM	No	Yes	2010	1	360,800	1082.4
13	Red Mesa Express	COM	No	Yes	2010	1	392,600	1177.8
14	Inscription House Irrigation	GOV	No	No	2010	1	895,300	1342.95
15	Pure Lake Building	COM	No	Yes	2010	1	59,240	177.72
16	Assembly Of God Church	NPROFIT	Yes	No	2010	1	41,690	0
17	SD Natural Gas Company	COM	No	Yes	2010	1	60,870	182.61
18	Speedy's	COM	No	Yes	2010	1	34,690	104.07
19	Window Pebble Apartments	GOV	No	Yes	2010	1	51,790	77.685
20	Press City PHS	GOV	No	No	2010	1	3,031,700	4547.55
21	Crown High School	GOV	No	No	2010	1	990,000	1485
22	Watsons Hall	COM	No	Yes	2010	1	82,000	246
23	Agnew Avenue Church	NPROFIT	Yes	No	2010	1	88,840	0
24	Accent Landscaping	COM	No	Yes	2010	1	319,090	957.27
25	American Painting	COM	No	Yes	2010	1	332,000	996
26	Sandia Oil Company Inc.	COM	No	Yes	2010	1	148,590	445.77
Sheet

 

[mdavis31]

YOU ARE A WONDERFUL MAN!!! That worked I only have to put the first formula in right and the way it is set up it automatically does all the other cells. How do you do that so darn fast! I just cannot figure out how to put these formulas together. I have to figure out the taxes next, hopefully I can figure that out Thank-you soo much!! Melissa

Ps I would have never even thought to use the LOOKUP

 

[mdavis31]

Thank-you everybody for your advice, I am so Excel Illiterate and I really appreciate all your help!! Melissa

 

[jasonb75]

Thats what the forum is here for, next time you get stuck you know where to look