Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
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. -- Biff Microsoft Excel MVP "Chris Cullen" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
THANK YOU!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
Chris Cullen wrote:
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
You're welcome!
-- Biff Microsoft Excel MVP "Chris Cullen" wrote in message ... THANK YOU!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can IF and VLOOKUP functions be inserted together?
Chris Cullen wrote:
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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
$ for VLOOKUP functions | Excel Worksheet Functions | |||
Inserted Columns into VLOOKUP Tables | Excel Discussion (Misc queries) | |||
VLookup Functions | Excel Worksheet Functions | |||
Automatically copy formulas/functions to inserted rows. | Excel Worksheet Functions | |||
VLOOKUP and IF Functions | Excel Worksheet Functions |