Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Large formulas
Do you know of any of creating formulas using the "if" function more than
seven times. It is normally limited to seven, I need as many as 20 |
#2
|
|||
|
|||
Large formulas
It's possible to use IF function 20 times given that you concatenate each
part, however it is hard to audit and if you indeed need many conditions you might be better off using something else like a lookup table or index, here's an example of how to bypass the 7 nested limits =IF( A1=1,"a","")&IF(A1=3,"c","")&IF(A1=5,"e","")&and so on however you might as well use a vlookup formula =VLOOKUP(A1,{1,"a";3,"c";5,"e"},2,0) -- Regards, Peo Sjoblom (No private emails please) "JimB" wrote in message ... Do you know of any of creating formulas using the "if" function more than seven times. It is normally limited to seven, I need as many as 20 |
#3
|
|||
|
|||
Large formulas
Thank you. What I am working on is a document we use at work to tally
production and in this case there are 20 items. An example would be if item 301 were completed, it would return a production value of 1.5. Here is the basic idea =IF(B3=301,1.25,IF(B3=302,1.5,IF(B3=303,1.5,IF(B3= 304,1.75,IF(B3=305,1.25,IF(B3=306,1.5,IF(B3=307,0. 75,IF(B3=308,0.75,)))))))) "Peo Sjoblom" wrote: It's possible to use IF function 20 times given that you concatenate each part, however it is hard to audit and if you indeed need many conditions you might be better off using something else like a lookup table or index, here's an example of how to bypass the 7 nested limits =IF( A1=1,"a","")&IF(A1=3,"c","")&IF(A1=5,"e","")&and so on however you might as well use a vlookup formula =VLOOKUP(A1,{1,"a";3,"c";5,"e"},2,0) -- Regards, Peo Sjoblom (No private emails please) "JimB" wrote in message ... Do you know of any of creating formulas using the "if" function more than seven times. It is normally limited to seven, I need as many as 20 |
#4
|
|||
|
|||
Large formulas
Using vlookup you could use
=IF(B3="","",VLOOKUP(B3,{301,1.25;302,1.5;303,1.5; 304,1.75;305,1.25;306,1.5;307,0.75;308,0.75},2,0)) and there are no problems expanding that for 20 values, assume you put 301 in E1 and 1.25 in F1 302 in E2 and 1.5 in F2 and so on down to row 20, then you could use =IF(B3="","",VLOOKUP(B3,E1:F20,2,0)) or hardcoded (I faked some more values using existing ones) =IF(B3="","",VLOOKUP(B3,{301,1.25;302,1.5;303,1.5; 304,1.75;305,1.25;306,1.5;307,0.75;308,0.75;309,1. 25;310,1.5;311,1.5;312,1.75;313,1.25;314,1.5;315,0 .75;316,0.75;317,1.5;318,1.75;319,1.5;320,1.75},2, 0)) getting a tad long but it works, I would recommend using either a cell reference like E1:F20 or named range for the table, using concatenated IFs =IF(OR(B3=301,B3=305),1.5,"")&IF(OR(B3=302,B3=303, B3=306),1.5,"")&IF(B3=304,1.75,"")&IF(OR(B3=307,B3 =308),0.75,"") since some values are the same you can use OR and it might be possible that you can use a regular if or formula without concatenating them, also you could probably skip a bit using vlookup since some values are the same -- Regards, Peo Sjoblom (No private emails please) "JimB" wrote in message ... Thank you. What I am working on is a document we use at work to tally production and in this case there are 20 items. An example would be if item 301 were completed, it would return a production value of 1.5. Here is the basic idea =IF(B3=301,1.25,IF(B3=302,1.5,IF(B3=303,1.5,IF(B3= 304,1.75,IF(B3=305,1.25,IF(B3=306,1.5,IF(B3=307,0. 75,IF(B3=308,0.75,)))))))) "Peo Sjoblom" wrote: It's possible to use IF function 20 times given that you concatenate each part, however it is hard to audit and if you indeed need many conditions you might be better off using something else like a lookup table or index, here's an example of how to bypass the 7 nested limits =IF( A1=1,"a","")&IF(A1=3,"c","")&IF(A1=5,"e","")&and so on however you might as well use a vlookup formula =VLOOKUP(A1,{1,"a";3,"c";5,"e"},2,0) -- Regards, Peo Sjoblom (No private emails please) "JimB" wrote in message ... Do you know of any of creating formulas using the "if" function more than seven times. It is normally limited to seven, I need as many as 20 |
#5
|
|||
|
|||
Large formulas
I'd create another worksheet.
Put this in A1:Bxx 301 1.25 302 1.5 303 1.5 304 1.75 305 1.25 306 1.5 307 .75 308 .75 Then use a change to Peo's =vlookup() formula: =if(b3="","",vlookup(b3,sheet2!a:b,2,false)) JimB wrote: Thank you. What I am working on is a document we use at work to tally production and in this case there are 20 items. An example would be if item 301 were completed, it would return a production value of 1.5. Here is the basic idea =IF(B3=301,1.25,IF(B3=302,1.5,IF(B3=303,1.5,IF(B3= 304,1.75,IF(B3=305,1.25,IF(B3=306,1.5,IF(B3=307,0. 75,IF(B3=308,0.75,)))))))) "Peo Sjoblom" wrote: It's possible to use IF function 20 times given that you concatenate each part, however it is hard to audit and if you indeed need many conditions you might be better off using something else like a lookup table or index, here's an example of how to bypass the 7 nested limits =IF( A1=1,"a","")&IF(A1=3,"c","")&IF(A1=5,"e","")&and so on however you might as well use a vlookup formula =VLOOKUP(A1,{1,"a";3,"c";5,"e"},2,0) -- Regards, Peo Sjoblom (No private emails please) "JimB" wrote in message ... Do you know of any of creating formulas using the "if" function more than seven times. It is normally limited to seven, I need as many as 20 -- Dave Peterson |
#6
|
|||
|
|||
Large formulas
On Mon, 17 Oct 2005 17:40:05 -0700, "JimB"
wrote: Do you know of any of creating formulas using the "if" function more than seven times. It is normally limited to seven, I need as many as 20 99/100 when this question is asked, the best solution is a lookup table. Look at HELP for the VLOOKUP function, and/or post back with more details. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Excel formulas to create large blocks of text | Excel Discussion (Misc queries) | |||
Help with large amounts of data and formulas | Excel Worksheet Functions | |||
How can I combine IF, COLUMN, and LARGE formulas in a single cell? | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |