Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
-- I have a question involving exceeding the amount of IF statements. Cell A1 has drop down list of 100 choices, the user selects one Cell B1 has another drop down list of 100 choices, of which the user selects one Cell C1, once a selection is made in both A1and B1, give an answer from 4 possible values in C1. i.e if a1 = dog and b1 = chair then c1 = yes I know that I can only use 7 nested IF statement in a cell. What would be the best way to display the values. Insert criteria in cell, use a VBA macro? If so what would the cell or VBA look like? With a 100 possible selections in both a1 or b1, the simplest way would be the best. thanks to anyone who can help. Mike mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
One approach ..
Assume your 100 x 100 x 100 response/answer table is in a sheet: X, within A1:C100 as below, where col C = answers dog chair yes dog table no dog food maybe cat chair no cat table maybe cat food yes etc Then in another sheet, where you have the corresponding DVs running in cols A and B, from row1 down, you could place this array formula in C1, and confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$100,MATCH(1,( X!A$1:A$100=A1)*(X!B$1:B$100=B1),0))) Copy C1 down as far as required. Col C will return the answers for the DV selections made in cols A and B. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mike" wrote: -- I have a question involving exceeding the amount of IF statements. Cell A1 has drop down list of 100 choices, the user selects one Cell B1 has another drop down list of 100 choices, of which the user selects one Cell C1, once a selection is made in both A1and B1, give an answer from 4 possible values in C1. i.e if a1 = dog and b1 = chair then c1 = yes I know that I can only use 7 nested IF statement in a cell. What would be the best way to display the values. Insert criteria in cell, use a VBA macro? If so what would the cell or VBA look like? With a 100 possible selections in both a1 or b1, the simplest way would be the best. thanks to anyone who can help. Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
THAT WAS GREAT!!!!
Max u didn't only solve Mike's but one of my very own problem as well. However, I have a little different one i.e. if the two columns do not match can I have a function to lookup in corresponding column D or E and return a value randomly from any instead of returning an #N/A? By the way sure am anxious to know why did you place "1" as lookup value in Match function? Thanx again buddy! "Max" wrote: One approach .. Assume your 100 x 100 x 100 response/answer table is in a sheet: X, within A1:C100 as below, where col C = answers dog chair yes dog table no dog food maybe cat chair no cat table maybe cat food yes etc Then in another sheet, where you have the corresponding DVs running in cols A and B, from row1 down, you could place this array formula in C1, and confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$100,MATCH(1,( X!A$1:A$100=A1)*(X!B$1:B$100=B1),0))) Copy C1 down as far as required. Col C will return the answers for the DV selections made in cols A and B. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mike" wrote: -- I have a question involving exceeding the amount of IF statements. Cell A1 has drop down list of 100 choices, the user selects one Cell B1 has another drop down list of 100 choices, of which the user selects one Cell C1, once a selection is made in both A1and B1, give an answer from 4 possible values in C1. i.e if a1 = dog and b1 = chair then c1 = yes I know that I can only use 7 nested IF statement in a cell. What would be the best way to display the values. Insert criteria in cell, use a VBA macro? If so what would the cell or VBA look like? With a 100 possible selections in both a1 or b1, the simplest way would be the best. thanks to anyone who can help. Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
welcome, Faraz. glad it was of help to you, too.
.. if the two columns do not match can I have a function to lookup in corresponding column D or E and return a value randomly from any instead of returning an #N/A? Assuming col D in X contains the values to be returned if there's no match, you could try this, array-entered as befo =IF(OR(A1="",B1=""),"",IF(ISNA(MATCH(1,(X!A$1:A$10 0=A1)*(X!B$1:B$100=B1),0)),INDEX(X!D$1:D$100,randb etween(1,100)),INDEX(X!C$1:C$100,MATCH(1,(X!A$1:A$ 100=A1)*(X!B$1:B$100=B1),0)))) Note that RANDBETWEEN requires the Analysis Toolpak* be installed and activated. Check the "Analysis Toolpak" box via Tools Add-Ins. Chip Pearson's page has details on the ATP at: http://www.cpearson.com/excel/ATP.htm .. why did you place "1" as lookup value in Match function? Because the lookup array in the MATCH, this part: (X!A$1:A$100=A1)*(X!B$1:B$100=B1) will resolve to an array of ones/zeros, eg: {0;0;1;0;0;0;0;...} depending on where the dual criteria is satisfied (1's) or not (0's) Using lookup value: 1 in MATCH would hence give us the (1st) matching position within the array where the dual criteria is satisfied. It's presumed of course, that there should be only a single matching position to be returned within the array. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "FARAZ QURESHI" wrote: THAT WAS GREAT!!!! Max u didn't only solve Mike's but one of my very own problem as well. However, I have a little different one i.e. if the two columns do not match can I have a function to lookup in corresponding column D or E and return a value randomly from any instead of returning an #N/A? By the way sure am anxious to know why did you place "1" as lookup value in Match function? Thanx again buddy! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
Max
I had to change the number of rows, in columns a,b & c to a number exceeding 100. For future use, these 3 columns may extend to over 4000 rows. Tried to modify index statement but could not get it to work. Any suggestions? Is there a limit to number of rows in these 3 columns? Thanks for your help. -- mike "Max" wrote: One approach .. Assume your 100 x 100 x 100 response/answer table is in a sheet: X, within A1:C100 as below, where col C = answers dog chair yes dog table no dog food maybe cat chair no cat table maybe cat food yes etc Then in another sheet, where you have the corresponding DVs running in cols A and B, from row1 down, you could place this array formula in C1, and confirm the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$100,MATCH(1,( X!A$1:A$100=A1)*(X!B$1:B$100=B1),0))) Copy C1 down as far as required. Col C will return the answers for the DV selections made in cols A and B. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mike" wrote: -- I have a question involving exceeding the amount of IF statements. Cell A1 has drop down list of 100 choices, the user selects one Cell B1 has another drop down list of 100 choices, of which the user selects one Cell C1, once a selection is made in both A1and B1, give an answer from 4 possible values in C1. i.e if a1 = dog and b1 = chair then c1 = yes I know that I can only use 7 nested IF statement in a cell. What would be the best way to display the values. Insert criteria in cell, use a VBA macro? If so what would the cell or VBA look like? With a 100 possible selections in both a1 or b1, the simplest way would be the best. thanks to anyone who can help. Mike |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
It should work ok, eg as per below.
=IF(OR(A1="",B1=""),"",INDEX(X!C$1:C$4000,MATCH(1, (X!A$1:A$4000=A1)*(X!B$1:B$4000=B1),0))) Ensure all the ranges within the INDEX/MATCH are identically sized. Look out for possible typos. Entire col references cannot be used. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mike" wrote in message ... Max I had to change the number of rows, in columns a,b & c to a number exceeding 100. For future use, these 3 columns may extend to over 4000 rows. Tried to modify index statement but could not get it to work. Any suggestions? Is there a limit to number of rows in these 3 columns? Thanks for your help. -- mike |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IFstatement OR VBA
One other important thing, as expressed in my response to your other thread
.... Do remember to press CTRL+SHIFT+ENTER each time that you edit the top cell array formula/before recopying down the col. Confirm it's correctly array-entered by looking at the formula bar. You should see curly braces { } wrapped around the formula by Excel. If you see no curlies, you gotta re-do the array-entering again (click inside the formula bar, re-press CTRL+SHIFT+ENTER). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|