Can IF and VLOOKUP functions be inserted together?

[Chris Cullen]

I am finishing an online tutorial section. I need to write a function to
determine a water bill. I was able to write a VLOOKUP function (using a
provided table in the workbook) for three water rate types but I need to
insert a function for inserting $0 as the water bill if a) their bill is
waived (determined by a yes or no answer in a column) or b) their water usage
is <25,000 gallons (determined by the Gallons Used Column info).

When I try to mix the formulas the results are wrong so I know I am making a
big and dumb error. This is my last attempted formula:

=IF([Bill Waived]="yes",0,IF[Gal
Used]<25000,VLOOKUPVLOOKUP(B2,Rates,2,FALSE)*G2/1000

Thanks for any help.

 

[T. Valko]

Let's assume:

A1 = Bill Waived
A2 = Gal Used

=IF(OR(A1="yes",A2<25000),0,VLOOKUP(B2,Rates,2,0)*G2/1000)

Note that if A2 is an empty cell it will evaluate to be <25000.

 

[Chris Cullen]

THANK YOU!!

 

[smartin]

I am finishing an online tutorial section. I need to write a function to
determine a water bill. I was able to write a VLOOKUP function (using a
provided table in the workbook) for three water rate types but I need to
insert a function for inserting $0 as the water bill if a) their bill is
waived (determined by a yes or no answer in a column) or b) their water usage
is <25,000 gallons (determined by the Gallons Used Column info).

When I try to mix the formulas the results are wrong so I know I am making a
big and dumb error. This is my last attempted formula:

=IF([Bill Waived]="yes",0,IF[Gal
Used]<25000,VLOOKUPVLOOKUP(B2,Rates,2,FALSE)*G2/1000

Thanks for any help.

You seem to have a lot of typos in your example (missing parens,
"VLOOKUPVLOOKUP") but I'm assuming that is not the cause of your trouble.

Here's a suggestion. Instead of using IF to check [Bill Waived],
consider using a logical expression. This will simplify your formula and
avoid an unnecessary nested level of IF (which can be a PITA to debug).

([Bill Waived]<>"yes")

will return true or false (try it somewhere). If you include this in a
mathematical expression, like

=([Bill Waived]<>"yes") * RATE * AMOUNT

then the true/false values will be coerced to numerical values of 1 or
0. I.e., if [Bill Waived] is yes, the first expression will be 0
(false), thus rendering the entire result 0.

I think you already have the expressions for RATE and AMOUNT figured out
using IF < 25000 and VLOOKUP. Do you see how the rest fits in?

 

[T. Valko]

You're welcome!

 

[Chris Cullen]

smartin: Thanks for replying. I think your reply might be easier but there
are a lot of concepts you are using that I do not understand (ie <>). The
first reply was simpler and it worked so I will use that for now. You are
right - the nested functions are (to me) long and a PITA but I do not have
any skills to bypass them yet.

Again, thanks for replying.

 

[smartin]

smartin: Thanks for replying. I think your reply might be easier but there
are a lot of concepts you are using that I do not understand (ie <>). The
first reply was simpler and it worked so I will use that for now. You are
right - the nested functions are (to me) long and a PITA but I do not have
any skills to bypass them yet.

Again, thanks for replying.

No worries, glad you got it sorted out.

For future reference, "<>" means "not equal".