Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions of a Cell
I'm trying to use this spread sheet for purchasing fuel and to subtract the
fuel tax from the pump price when I purchase fuel so if I type in cell B2 IL I want $0.43 to show up in Cell C2 but... If I type IN in cell B2 I want $0.16 to show up in Cell C2 and so on for all the States I buy fuel in =IF(C2="IL",0.43,0) if I type this in Cell C2 that is good for IL but I can't seem to do a multiple =IF in the same Cell |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions of a Cell
Drew,
I presume your fuel prices will change from time to time so I would use a lookup table somewhere in the workbook with the State in the first column and the fuel price in the second column. This would allow you to easily update the fuel cost for each state. Then place the following formula in C2. =IF(ISNA(VLOOKUP(B2,Lookup_Table,2,0)),"",VLOOKUP( B2,Lookup_Table,2,0)) Replace "Lookup_Table" with the range that contains your table. If you don't use a named range be sure to use an absolute reference for the table. i.e. $I$2:$J$51 You can copy this formula down as needed. Hope this helps. Dave "Drew" wrote in message ... I'm trying to use this spread sheet for purchasing fuel and to subtract the fuel tax from the pump price when I purchase fuel so if I type in cell B2 IL I want $0.43 to show up in Cell C2 but... If I type IN in cell B2 I want $0.16 to show up in Cell C2 and so on for all the States I buy fuel in =IF(C2="IL",0.43,0) if I type this in Cell C2 that is good for IL but I can't seem to do a multiple =IF in the same Cell |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions of a Cell
You could use =IF(C2="1L",0.43,IF(C2="1N",0.16, and so on. However, if you
travel through all the states, you will have a problem, since you have a limitation of seven IF's. You could also set up a lookup table, having iL, iN etc in one column, and the relevant values in the next column. Say in AA you have your iL, iN etc, and in AB you have 0.43, 0.16 and so on. Name this range say States. Now, in C2 enter =IF(C2="","",VLOOKUP(C2,States,2,0)), and your problem should be solved. -- HTH Kassie Replace xxx with hotmail "Drew" wrote: I'm trying to use this spread sheet for purchasing fuel and to subtract the fuel tax from the pump price when I purchase fuel so if I type in cell B2 IL I want $0.43 to show up in Cell C2 but... If I type IN in cell B2 I want $0.16 to show up in Cell C2 and so on for all the States I buy fuel in =IF(C2="IL",0.43,0) if I type this in Cell C2 that is good for IL but I can't seem to do a multiple =IF in the same Cell |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions of a Cell
Hi Drew,
Instead of using the IF function try a combination of the IF and Vlookup functions. First, somewhere in your spreadsheet create a list of states and then in the column next to the states type in the tax rates. Then in the cell where you want the results, type =IF(ISNA(VLOOKUP(A2,F2:G5,2)),0,VLOOKUP(A2,F2:G5,2 )) In the vlookup F2:F6 (in this example) is thel ist of states and tax rates. Please press Yes if this helps. Regards, John "Drew" wrote: I'm trying to use this spread sheet for purchasing fuel and to subtract the fuel tax from the pump price when I purchase fuel so if I type in cell B2 IL I want $0.43 to show up in Cell C2 but... If I type IN in cell B2 I want $0.16 to show up in Cell C2 and so on for all the States I buy fuel in =IF(C2="IL",0.43,0) if I type this in Cell C2 that is good for IL but I can't seem to do a multiple =IF in the same Cell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple functions in one cell | Excel Worksheet Functions | |||
multiple functions on one cell ? | Excel Discussion (Misc queries) | |||
Multiple functions in a cell help | New Users to Excel | |||
MUltiple functions on a same cell | Excel Discussion (Misc queries) | |||
Multiple functions per cell? | Excel Worksheet Functions |