Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
=VLOOKUP(D7,{3,0.376;4,0.668;5,1.043;6,1.502},2,FA LSE)
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
=VLOOKUP(value,Sheet2!table,2,False)
-- HTH RP (remove nothere from the email address if mailing direct) "steve" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
nested if statements
=IF(ISNA(MATCH($D$7,Sheet3!BV1:BV11,0))=TRUE,"",LO OKUP($D$7,Sheet3!BV1:BW11) ) or =IF(ISNA(MATCH($D$7,Sheet3!BV1:BV11,0))=TRUE,"",VL OOKUP($D$7,Sheet3!BV1:BW11 ,2,False)) -- Regards, Tom Ogilvy "steve" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested If/And/Or statements | Excel Worksheet Functions | |||
NESTED IF STATEMENTS | New Users to Excel | |||
Nested IF Statements | Excel Discussion (Misc queries) | |||
Help with Nested If Statements | Excel Discussion (Misc queries) | |||
I want to use more than 7 nested if then statements | Excel Worksheet Functions |