Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I am not knowledgable in Excel and I am trying to figure out how to do
something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. |
#2
![]() |
|||
|
|||
![]()
Here's one possibiliy. You could use a formula like this
one in each cell (X9, AC9, etc.): =CHOOSE(MATCH(F9,{"A","B","C"},0),SUM(A:A),SUM(B:B ),SUM (C:C)) In this case, if F9 = "A", then col. A is summed. If F9 = "B", sum col. B, and if F9 = "C", sum col. C. HTH Jason Atlanta, GA -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . |
#3
![]() |
|||
|
|||
![]()
Hi!
Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5),IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . |
#4
![]() |
|||
|
|||
![]()
Thanks for the responses.
Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9="A",( AND(G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5), IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . |
#5
![]() |
|||
|
|||
![]()
Hi!
Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND(G9239,G9<481),4 80,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9="A", (AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5),I F (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . |
#6
![]() |
|||
|
|||
![]()
Thanks for all your help on this. I couldn't figure it out so I slept
on it and now I have it. Here is the formula that worked. =IF(AND(F9="A",G9119,G9<241),240,IF(AND(F9="B",G9 239,G9<481),480,"")) It changes throughout the sheet, but used this formula as the example to just change numbers around. The numbers used in A and B are the same except B goes up to 480.The numbers go like this. A B 15 15 30 30 60 60 120 120 240 240 480 I have it all figured out except for one part. Here are the 2 formulas to combine like the previous one. =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AND(F9="B",G9<48 0),"",480) On Tue, 8 Mar 2005 21:45:37 -0800, "Biff" wrote: Hi! Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND(G9239,G9<481), 480,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9="A" ,(AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5), IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . |
#7
![]() |
|||
|
|||
![]()
Hi!
..... =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AN(F9="B",G9<480 ),"",480) ..... The formula is not properly constructed but that doesn't matter because you have a logic mis-match: =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Can't figure out what you mean with this one but the problem is that you have 2 different return values for only one condition. Biff -----Original Message----- Thanks for all your help on this. I couldn't figure it out so I slept on it and now I have it. Here is the formula that worked. =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) It changes throughout the sheet, but used this formula as the example to just change numbers around. The numbers used in A and B are the same except B goes up to 480.The numbers go like this. A B 15 15 30 30 60 60 120 120 240 240 480 I have it all figured out except for one part. Here are the 2 formulas to combine like the previous one. =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) On Tue, 8 Mar 2005 21:45:37 -0800, "Biff" wrote: Hi! Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND(G9239,G9<481) ,480,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9="A ",(AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5) ,IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . . |
#8
![]() |
|||
|
|||
![]()
Wouldn't it be along the same lines as the previous formula
constructed based on the value of F9? =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Those are the 2 formulas I combined to make this one based on F9: =IF(AND(F9="A",G9<240),"",240),IF(AND(F9="B",G9<48 0),"",480) It is based on the value of F9 just like the previous one I combined that you helped me with. Is there a way to fix the formula above? Can something like the formula above accomplish what I want? A B 15 15 30 30 60 60 120 120 240 240 480 B goes up to 480 and A only goes up to 240 On Fri, 11 Mar 2005 00:32:26 -0800, "Biff" wrote: Hi! .... =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AN(F9="B",G9<48 0),"",480) .... The formula is not properly constructed but that doesn't matter because you have a logic mis-match: =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Can't figure out what you mean with this one but the problem is that you have 2 different return values for only one condition. Biff -----Original Message----- Thanks for all your help on this. I couldn't figure it out so I slept on it and now I have it. Here is the formula that worked. =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) It changes throughout the sheet, but used this formula as the example to just change numbers around. The numbers used in A and B are the same except B goes up to 480.The numbers go like this. A B 15 15 30 30 60 60 120 120 240 240 480 I have it all figured out except for one part. Here are the 2 formulas to combine like the previous one. =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) On Tue, 8 Mar 2005 21:45:37 -0800, "Biff" wrote: Hi! Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND(G9239,G9<481 ),480,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9=" A",(AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5 ),IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . . |
#9
![]() |
|||
|
|||
![]()
Hi!
Let me see if I understand this: If F9 = A, G9 will not be 240 and If F9 = B, G9 will not be 480 If that's true, try this: =IF(AND(F9="A",G9=240),240,IF(AND(F9="B",G9=480),4 80,"")) Biff -----Original Message----- Wouldn't it be along the same lines as the previous formula constructed based on the value of F9? =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Those are the 2 formulas I combined to make this one based on F9: =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) It is based on the value of F9 just like the previous one I combined that you helped me with. Is there a way to fix the formula above? Can something like the formula above accomplish what I want? A B 15 15 30 30 60 60 120 120 240 240 480 B goes up to 480 and A only goes up to 240 On Fri, 11 Mar 2005 00:32:26 -0800, "Biff" wrote: Hi! .... =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AN (F9="B",G9<480),"",480) .... The formula is not properly constructed but that doesn't matter because you have a logic mis-match: =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Can't figure out what you mean with this one but the problem is that you have 2 different return values for only one condition. Biff -----Original Message----- Thanks for all your help on this. I couldn't figure it out so I slept on it and now I have it. Here is the formula that worked. =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) It changes throughout the sheet, but used this formula as the example to just change numbers around. The numbers used in A and B are the same except B goes up to 480.The numbers go like this. A B 15 15 30 30 60 60 120 120 240 240 480 I have it all figured out except for one part. Here are the 2 formulas to combine like the previous one. =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) On Tue, 8 Mar 2005 21:45:37 -0800, "Biff" wrote: Hi! Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND (G9239,G9<481),480,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9= "A",(AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A 5),IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . . . |
#10
![]() |
|||
|
|||
![]()
That worked perfect. Thanks for helping me out through the whole
process. On Sat, 12 Mar 2005 10:18:05 -0800, "Biff" wrote: Hi! Let me see if I understand this: If F9 = A, G9 will not be 240 and If F9 = B, G9 will not be 480 If that's true, try this: =IF(AND(F9="A",G9=240),240,IF(AND(F9="B",G9=480), 480,"")) Biff -----Original Message----- Wouldn't it be along the same lines as the previous formula constructed based on the value of F9? =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Those are the 2 formulas I combined to make this one based on F9: =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) It is based on the value of F9 just like the previous one I combined that you helped me with. Is there a way to fix the formula above? Can something like the formula above accomplish what I want? A B 15 15 30 30 60 60 120 120 240 240 480 B goes up to 480 and A only goes up to 240 On Fri, 11 Mar 2005 00:32:26 -0800, "Biff" wrote: Hi! .... =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AN (F9="B",G9<480),"",480) .... The formula is not properly constructed but that doesn't matter because you have a logic mis-match: =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Can't figure out what you mean with this one but the problem is that you have 2 different return values for only one condition. Biff -----Original Message----- Thanks for all your help on this. I couldn't figure it out so I slept on it and now I have it. Here is the formula that worked. =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) It changes throughout the sheet, but used this formula as the example to just change numbers around. The numbers used in A and B are the same except B goes up to 480.The numbers go like this. A B 15 15 30 30 60 60 120 120 240 240 480 I have it all figured out except for one part. Here are the 2 formulas to combine like the previous one. =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) On Tue, 8 Mar 2005 21:45:37 -0800, "Biff" wrote: Hi! Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND (G9239,G9<481),480,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F9 ="A",(AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1: A5),IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . . . |
#11
![]() |
|||
|
|||
![]()
You're welcome! Thanks for the feedback.
Biff -----Original Message----- That worked perfect. Thanks for helping me out through the whole process. On Sat, 12 Mar 2005 10:18:05 -0800, "Biff" wrote: Hi! Let me see if I understand this: If F9 = A, G9 will not be 240 and If F9 = B, G9 will not be 480 If that's true, try this: =IF(AND(F9="A",G9=240),240,IF(AND(F9="B",G9=480) ,480,"")) Biff -----Original Message----- Wouldn't it be along the same lines as the previous formula constructed based on the value of F9? =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Those are the 2 formulas I combined to make this one based on F9: =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) It is based on the value of F9 just like the previous one I combined that you helped me with. Is there a way to fix the formula above? Can something like the formula above accomplish what I want? A B 15 15 30 30 60 60 120 120 240 240 480 B goes up to 480 and A only goes up to 240 On Fri, 11 Mar 2005 00:32:26 -0800, "Biff" wrote: Hi! .... =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AN (F9="B",G9<480),"",480) .... The formula is not properly constructed but that doesn't matter because you have a logic mis-match: =IF(G9<240,"",240) =IF(G9<480,"",480) If G9 < 240 it's also < 480, right? Can't figure out what you mean with this one but the problem is that you have 2 different return values for only one condition. Biff -----Original Message----- Thanks for all your help on this. I couldn't figure it out so I slept on it and now I have it. Here is the formula that worked. =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) It changes throughout the sheet, but used this formula as the example to just change numbers around. The numbers used in A and B are the same except B goes up to 480.The numbers go like this. A B 15 15 30 30 60 60 120 120 240 240 480 I have it all figured out except for one part. Here are the 2 formulas to combine like the previous one. =IF(G9<240,"",240) =IF(G9<480,"",480) Here is what I came up with. Excel accepted the entry, but gives me #VALUE! error. =IF(AND(F9="A",G9<240),"",240),IF(AND (F9="B",G9<480),"",480) On Tue, 8 Mar 2005 21:45:37 -0800, "Biff" wrote: Hi! Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? No, that'll have no negative impact. The problem is that the formula is not properly constructed. I can't really see why you need to use A= this formula and B= that formula unless A and B are related to specific and separate criteria. If you simply want to return different values based on the value of G9, you could do this: =IF(AND(G9119,G9<241),240,IF(AND (G9239,G9<481),480,"")) But, if you do need to base things on F9 equalling either A or B here are a couple of ideas: =IF(AND(F9="A",G9119,G9<241),240,IF(AND (F9="B",G9239,G9<481),480,"")) Using this formula, if F9=A and G9241 you'll get "". Not sure that's what you want but that's how you're separate formulas would have handled it. Here's something that uses Jason's approach with a little twist. Create defined names for the 2 formulas: A =IF(AND(G9119,G9<241),240,"") B =IF(AND(G9239,G9<481),480,"") Then use this formula: =CHOOSE(MATCH(F9,{"A","B"},0),A,B) This will work the same as the second formula I show above. Biff -----Original Message----- Thanks for the responses. Here is a more specific example of what I am doing. Here are the 2 formulas I have. =IF(AND(G9119,G9<241),240,"") =IF(AND(G9239,G9<481),480,"") If I put A in F9 it will use the first one and B in F9 will use the second one. Here is what I made based on the suggestion below. Excel didn't want to accept it. Note: I use a word instead of A or B. =IF(F9="B",(AND(G9239,G9<481),480,""),IF(F 9="A", (AND (G9119,G9<241),240,"")) Note: in F9 I made a drop-down list so it can pick A or B. Would that tamper with the formula? On Tue, 8 Mar 2005 11:04:08 -0800, "Biff" wrote: Hi! Try something like this as long as you only have 2 or 3 conditions: =IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1 :A5),IF (F9="C",MIN(A1:A5),""))) A = sum the range B = average the range C = minimum value in the range Experiment! Biff -----Original Message----- I am not knowledgable in Excel and I am trying to figure out how to do something. I want some cells in a row to choose between 2 or 3 formulas based on the value I enter for a certain cell in the same row. For example: If I enter "A" in F9 it would use Formula A in X9, AC9, etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc. How would I do this? I am not even sure of the terminology of the solution to research it. Variable, IF, ??????? Thanks. . . . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula that referance a Cell | Excel Discussion (Misc queries) | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
Cell contents vs. Formula contents | Excel Discussion (Misc queries) | |||
How do I do math on a cell name in formula? | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |