nested if statements

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

=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)


"steve" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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)

--
WhyIsDoug?


"steve" wrote:

> 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
>

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.

--
steveB

Remove "AYN" from email to respond
"steve" <[email protected]> wrote in message
news:[email protected]...
>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
>

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

"STEVE BELL" wrote:

> 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.
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "steve" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
>
>
>

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


--

HTH

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


"steve" <[email protected]> wrote in message
news:[email protected]...
> thanks guys, how do i use vlookup if i put my table in a different sheet?
>
> "STEVE BELL" wrote:
>
> > 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.
> >
> > --
> > steveB
> >
> > Remove "AYN" from email to respond
> > "steve" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> > >
> >
> >
> >

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.

steve wrote:
>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

=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))
--
Regards,
Tom Ogilvy

"steve" <[email protected]> wrote in message
news:[email protected]...
> thanks guys, how do i use vlookup if i put my table in a different sheet?
>
> "STEVE BELL" wrote:
>
> > 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.
> >
> > --
> > steveB
> >
> > Remove "AYN" from email to respond
> > "steve" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> > >
> >
> >
> >