Help with 'if' formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this formula using 'IF' but the problem is that excel 2003 does not
let me use more than 8 'IF'. how can I change my formula that I can add other
values on it. for example what I am trying to do is, if I enter a number from
1 to 8 or from 1.1 to 8.1 I will have a cell that provides me with the
correct dollar amount. For example i can enter 5 I want my cell to provide me
with the number of $50,000.00 If I enter 5.1 I want my cell to say $45,000.
but using if I can only go so far. I need to double the size of this
information or use another formula.



=IF(AA8=1.1,"$27,500.00",IF(AA8=1,"$33,000.00",IF(AA8=2.1,"$31,400.00",IF(AA8=2,"37,680.00",IF(AA8=3.1,"$35,350.00",IF(AA8=3,"$42,420.00",IF(AA8=4.1,"$39,250.00",IF(AA8=4,"$47,100.00",))))))))
 
Look at using VLOOKUP: see Excel HELP

Set up table in Columns A & B (on say Sheet2) with column A="code" (1, 1.1
etc) and B the corresponding values.

=VLOOKUP(AA8,Sheet2!$A$1:$B$50,2,0)

HTH
 
Any other posible way that i can create this formula with out using a table,
the form that I am working is only one sheet, I can not insert a table or use
any other sheet
 
Any other posible way that i can create this formula with out using a table,
the form that I am working is only one sheet, I can not insert a table or use
any other sheet
Click to expand...


You can place the values in an array constant (look at HELP for this) instead
of using a cell_reference. Don't mix up the commas and semicolons.

For example, for the formula that you posted in your original post:

=VLOOKUP(AA8,{1.1,27500;1,33000;2.1,31400;2,37680;3.1,35350;3,42420;4.1,39250;4,41700},2,0)

and to add on the 5 and 5.1:

=VLOOKUP(AA8,{1.1,27500;1,33000;2.1,31400;2,37680;3.1,35350;3,42420;4.1,39250;4,41700;5,50000;5.1,45000},2,0)

I am surprised though that you can't add a table in a hidden part of the
worksheet.
--ron
 
Back
Top