Multiple Functions of a Cell

[Drew]

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

 

[Bassman62]

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

 

[Kassie]

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

 

[JCS]

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