Need help with an IF formula

[susiesc]

I am to Add a column named Gal Billed to the table; wich I did.
And the 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 Gal Used Column in the Gal Billed Column. I am confused....Help please

Customer Name	Cust Type	Bill Waived	Taxable	Billing Year	Gal Used	Gal Billed
Red Lake Chapter	NPROFIT	No	Yes	2013	108,300
Amity Church	NPROFIT	Yes	No	2013	55,160

<tbody>
</tbody>

 

[faugrad]

I am to Add a column named Gal Billed to the table; wich I did.
And the 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 Gal Used Column in the Gal Billed Column. I am confused....Help please

Customer Name	Cust Type	Bill Waived	Taxable	Billing Year	Gal Used	Gal Billed
Red Lake Chapter	NPROFIT	No	Yes	2013	108,300
Amity Church	NPROFIT	Yes	No	2013	55,160

<tbody>
</tbody>

This should do it:
=IF(AND(C2="No", F2>=25000),F2,"0")

Let me know if that took care of it or else I'll try again.

 

[Akashwani]

Hi and welcome to MrExcel.

Does this work for you?....

Excel Workbook
	A	B	C	D	E	F	G	H
1	Customer Name	Cust Type	Bill Waived	Taxable	Billing Year	Gal Used	Gal Billed
2	Red Lake Chapter	NPROFIT	No	Yes	2013	108,300	108300
3	Amity Church	NPROFIT	Yes	No	2013	55,160	0
4
Sheet15

I hope that helps.

Ak

 

[Jim885]

I believe this should do it.
Place this formula in the Gal Billed cell, then drag down as needed.
=IF(F2<25000,0,IF(C2="Yes",0,F2))

 

[susiesc]

This should do it:
=IF(AND(C2="No", F2>=25000),F2,"0")

Let me know if that took care of it or else I'll try again.

Yes that did it; thank you. Do you think you can help me with another?

This is so confusing...
In the column water bill, the formula rule is based on:
The billing rate varies based on the type of customer (Cust type column). The billing rate is $3, $2, or $1.50 per thousand gallonds billed, depending on the customer type (In cells B2:C5 of the billing rate worksheet). For example, a commercial customer using 75,000 gallons pays $150 (100 x$1.50).
A commercial customer using 15,000 gallons has a water bill of $0.

Can you assist?