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.

[TABLE="width: 777"]
<TBODY>[TR]
[TD]Customer Name[/TD]
[TD]Cust Type[/TD]
[TD]Bill Waived[/TD]
[TD]Taxable[/TD]
[TD]Billing Year[/TD]
[TD]Gal Used[/TD]
[TD]Gal Billed[/TD]
[TD]Water bill[/TD]
[/TR]
[TR]
[TD]Red Lake Chapter[/TD]
[TD]NPROFIT[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]108,300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amity Church[/TD]
[TD]NPROFIT[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]55,160[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Church of Christ[/TD]
[TD]NPROFIT[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]32,430[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NM State Highway[/TD]
[TD]GOV[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]95,660[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spot Free Power Wash[/TD]
[TD]COM[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]281,200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CIT Apartment Complex[/TD]
[TD]COM[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]257,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Housing Agency[/TD]
[TD]GOV[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]23,300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pinon Pizza Edge[/TD]
[TD]COM[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]81,110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Department of Agriculture[/TD]
[TD]GOV[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2,783,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chevron Station[/TD]
[TD]COM[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]1,131,600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=2></COLGROUP>[/TABLE]

 

[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


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

 

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