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

[TABLE="class: cms_table, width: 616"]
<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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="class: cms_table, width: 616"]
<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]
[/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]
[/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]
[/TR]
</tbody>[/TABLE]

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?