nested if statements

[Guest]

i use the following if statement in my sheet:

=IF(D7=3,0.376,IF(D7=4,0.668,IF(D7=5,1.043,IF(D7=6,1.502)))))

the problem is that 7 IF statements is the max, and i have 11 that i need to
use. do i need to switch to a macro? here is how the list goes. the first
column is the size of the bar, the second column is the weight(pounds per
foot)

3 .376
4 .668
5 1.043
6 1.502
7 2.044
8 2.67
9 3.4
10 4.303
11 5.313
14 7.650
18 13.6

 

[Bob Phillips]

=VLOOKUP(D7,{3,0.376;4,0.668;5,1.043;6,1.502},2,FALSE)

if you will use this formula in many cells, store the values in a table and
lookup that table

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I would consider using the VLOOKUP funcion and storing the values you have in
a lookup table. You already have that based on your message.

Your formula would look something like this:
=VLOOKUP(D7,<table_array>,2)

Where "<table_array>" should be a named range that includes just your table
data.

This way you could easily add, remove or change values without having to
modify your Worksheet function(s)

 

[STEVE BELL]

Steve,

If you can determine a mathmatical relationship - than you can just build a
formula based on the value of D7. I build a scatter plot and found that
most of the values matched a powered trend line.

But the easiest would be to use a Lookup Table. (table = $BV$1:$BW$11)
=LOOKUP($D$7,BV1:BW11)

Note that it will return #N/A if D7 isn't found.

You can get around that with
=IF(ISNA(MATCH($D$7,BV1:BV11,0))=TRUE,"",LOOKUP($D$7,BV1:BW11))

Otherwise you can use a Case Select in code.

 

[Guest]

thanks guys, how do i use vlookup if i put my table in a different sheet?

 

[Bob Phillips]

=VLOOKUP(value,Sheet2!table,2,False)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken B via OfficeKB.com]

Using an lookup statement is clearly the way to go in your situation.
However, although a little cumbersome, there is a way around for the
limitation of 7 IF statements. Specifically, assuming you want the results
to appear in Cell A-1 you list the 1st 6 arguements in the cell...the last
arguement in Cell A-1 refers [i.e., +B1] to the results appearing in Cell B-1.
In Cell B-1 you continue listing the remaining arguements.

 

[Tom Ogilvy]

=IF(ISNA(MATCH($D$7,Sheet3!BV1:BV11,0))=TRUE,"",LOOKUP($D$7,Sheet3!BV1:BW11)
)

or


=IF(ISNA(MATCH($D$7,Sheet3!BV1:BV11,0))=TRUE,"",VLOOKUP($D$7,Sheet3!BV1:BW11
,2,False))