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>

 

[mvptomlinson]

Hi Haj,

For #1 try:

=IF(OR(C2="Yes",F2<25000),0,G2)

This assumes "Bill Waived" is column C, "Gal Used" is column F, "Gal Billed" is column G, and your data starts in row 2. Adjust accordingly if your data layout is different.

For #2, perhaps:

=LOOKUP(B2,{"COM","GOV","NPROFIT"},{3,1.5,2})*(INT(G2/1000))

This divides G2 ("Gal Billed") by 1000 then cuts off the decimal portion (no rounding), then multiplies that amount by 3, 1.5 or 2, depending on the value in B2. The lookup array is in ascending alphabetical order, so the result array must be in the corresponding order.

 

[haj284]

Those values are correcct, but when I input the formula G2 is returning 0...which according to the rules I don't think it should? or am I doing something wrong?

 

[mvptomlinson]

You could be doing something wrong, or your values might not be exactly the same (extra spaces?) I can't really tell without seeing your workbook.

 

[haj284]

I'm also using Excel 2010

 

[mvptomlinson]

Edit the first formula to:

=IF(OR(C2="Yes",F2<25000),0,F2)

With G2 as the last argument, and the formula being in cell G2, a circular reference would be created. We don't want that.

 

[haj284]

Is there a way to attach my workbook? or I can give you my email and send it to you as an attachment? my email is haj284@email.vccs.edu

 

[mvptomlinson]

Also, when I copied your data from this page into Excel there were extra spaces after the Yes's and No's in the Bill Waived column. If those exist in your actual data, remove them. (Use the Find/Replace dialog after selecting those cells. Replace a space character with nothing.)

 

[haj284]

oh my gosh, that worked you are amazing!!!! so would the formula for #2 be =LOOKUP(B2,{"COM","GOV","NPROFIT"},{3,1.5,2})*(INT(G2/1000))

like you initially said?

 

[mvptomlinson]

Yeah, the second formula shouldn't need to change.