Nested IF function

[haj284]

Someone please help me with these formulas! I am so upset I can not figure these out. PLEASE HELP

1. Enter a formula to calculate the number of gallons billed, based on the following rules: If a customer's bill is waived (Bill Waived column) or the number of gallons used is less than 25,000 (Gal Used column), display 0 in Gal Billed column; otherwise display the value from the gallon used column in the gal billed column

2. Enter a Formula to calculate the water bill based on the following rules: The billing rate varies based on the type pf 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)

**billing rate COM-$3 NPROFIT-$2 Gov-$1.50**

heres what my worksheet looks like. please help me with what each of these formulas are.

Customer Name	Cust Type	Bill Waived	Taxable	Billing Year	Gal Used	Gal Billed	Water bill
Red Lake Chapter	NPROFIT	No	Yes	2013	108,300
Amity Church	NPROFIT	Yes	No	2013	55,160
Church of Christ	NPROFIT	Yes	No	2013	32,430
NM State Highway	GOV	No	No	2013	95,660
Spot Free Power Wash	COM	No	Yes	2013	281,200
CIT Apartment Complex	COM	No	Yes	2013	257,000
Housing Agency	GOV	No	No	2013	23,300
Pinon Pizza Edge	COM	No	Yes	2013	81,110
Department of Agriculture	GOV	No	No	2013	2,783,000
Chevron Station	COM	No	Yes	2013	1,131,600

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=2></COLGROUP>

 

[haj284]

Great thanks ! I have a few more if you have time and don't mind.
4. add a column named tax (I1) to the table then enter a formula to calculate the tax based on the following rule: If a customer pays tax (taxable column), then multiply the waterbill by 3.5%;otherwise the tax is 0. the tax rate is stored in cell T1

Customer Name	Cust Type	Bill Waived	Taxable	Billing Year	Gal Used	Gal Billed	Water bill	Tax
Red Lake Chapter	NPROFIT	No	Yes	2013	108,300	108300	216
Amity Church	NPROFIT	Yes	No	2013	55,160	0	0
Church of Christ	NPROFIT	Yes	No	2013	32,430	0	0
NM State Highway	GOV	No	No	2013	95,660	95660	142.5
Spot Free Power Wash	COM	No	Yes	2013	281,200	281200	843
CIT Apartment Complex	COM	No	Yes	2013	257,000	257000	771
Housing Agency	GOV	No	No	2013	23,300	0	0
Pinon Pizza Edge	COM	No	Yes	2013	81,110	81110	243
Department of Agriculture	GOV	No	No	2013	2,783,000	2783000	4174.5
Chevron Station	COM	No	Yes	2013	1,131,600	1131600	3393
O B Trailer Service	COM	No	Yes	2013	360,800	360800	1080
Red Mesa Express	COM	No	Yes	2013	392,600	392600	1176
Inscription House Irrigation	GOV	No	No	2013	895,300	895300	1342.5
Pure Lake Building	COM	No	Yes	2013	59,240	59240	177
Assembly Of God Church	NPROFIT	Yes	No	2013	41,690	0	0

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=3></COLGROUP>

 

[mvptomlinson]

Possibly:

=IF(D2="Yes",H2*$T$1,0)

 

[haj284]

Thanks so much! you have no idea how much this helped! Have a good night.

 

[mvptomlinson]

You're welcome, glad I could help.