|  | Simple "If" Function - HELP | | |
| | | James8309 |  |
|  Posted: Tue Jun 17, 2008 12:49 pm Post subject: Simple "If" Function - HELP |  |
Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance! |
| |
| | | GB | |
| | Posted: Tue Jun 17, 2008 3:47 pm Post subject: Re: Simple "If" Function - HELP | |
| |  | |
"James8309" <jaedong1221@gmail.com> wrote in message
news:98914eb8-9963-4199-910e-4b2daf28b476@p39g2000prm.googlegroups.com...
| Quote: | Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance!
|
Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.
An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables. |
| |
| | | James8309 | |
| | Posted: Tue Jun 17, 2008 11:05 pm Post subject: Re: Simple "If" Function - HELP | |
| | | |
On Jun 18, 3:47 am, "GB" <NOTsome...@microsoft.com> wrote:
| Quote: | "James8309" <jaedong1...@gmail.com> wrote in message
news:98914eb8-9963-4199-910e-4b2daf28b476@p39g2000prm.googlegroups.com...
Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance!
Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.
An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables.- Hide quoted text -
- Show quoted text -
|
Firstly, Thanks for your help!
Basically,
1. I have Range A1 to A850 containing alphabets. They are almost
distributed.
i.e. A1 might be B, A2 might be Z ... etc
2. Column B, C, D and E contains numbers.
3. Each alphabet in Range("A1:A850") has different families.
i.e. A will have A110, A220, A33, A555 etc depending on relationship
between Column B,C,D and E
Example, lets say A group
A111 = B>C, C = D, D<E
A222 = B<C, C<>D, D>E
etc etc etc
if I was to code this conditions in column F it would be something
like this
"
if(and(A1="A",B>C,C=D,D<E),"A111",if(and(A1="A",B<C,C<>D,D>E),"A222",if
if if if if if etc etc etc
This is why I will end up with over 90 "if" & "And" statement in one
cell. I don't even know if it is possible. As you can see in every if
statement, there is condition A1="A". I just didn't know how to lock
it or make it common to all other statements.
Thanks again! |
| |
| | | Pete_UK | |
| | Posted: Wed Jun 18, 2008 12:28 am Post subject: Re: Simple "If" Function - HELP | |
| | | |
Do the 3 digits which follow the letter all follow the same consistent
rules? i.e. does 1xx always mean B>C and 2xx mean B<C (and presumably
0xx mean B=C)? And does x1x always mean C=D, x2x mean C<>D? And does
xx1 always mean D<E and xx2 mean D>E?
There is some inconsistency here, but I'm just going off your example.
You basically have 4 possible outcomes of comparing two adjacent
columns:
B=C, B>C, B<C, and B<>C (if you count this)
and similarly with the other adjacent columns C to D and D to E. So
you could allocate a specific code to these conditions, i.e. 0, 1, 2
and 3 and thus calculate the overall code, something like:
=A1 & IF(B1=C1,0,IF(B1>C1,1,2)) & IF(C1=D1,0,IF(C1>D1,1,2)) &
IF(D1=E1,0,IF(D1>E1,1,2))
and then copy this down. This is more consistent, but does not check
for C<>D and thus does not tie up exactly with your example (but then
there were only two combinations shown !!). This will give you an
output like "A111", or "A020" depending on the conditions being met -
you might need to play about with it a bit to get your exact
combinations.
Hope this helps.
Pete
On Jun 18, 12:05 am, James8309 <jaedong1...@gmail.com> wrote:
| Quote: | On Jun 18, 3:47 am, "GB" <NOTsome...@microsoft.com> wrote:
"James8309" <jaedong1...@gmail.com> wrote in message
news:98914eb8-9963-4199-910e-4b2daf28b476@p39g2000prm.googlegroups.com...
Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance!
Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.
An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables.- Hide quoted text -
- Show quoted text -
Firstly, Thanks for your help!
Basically,
1. I have Range A1 to A850 containing alphabets. They are almost
distributed.
i.e. A1 might be B, A2 might be Z ... etc
2. Column B, C, D and E contains numbers.
3. Each alphabet in Range("A1:A850") has different families.
i.e. A will have A110, A220, A33, A555 etc depending on relationship
between Column B,C,D and E
Example, lets say A group
A111 = B>C, C = D, D<E
A222 = B<C, C<>D, D>E
etc etc etc
if I was to code this conditions in column F it would be something
like this
"
if(and(A1="A",B>C,C=D,D<E),"A111",if(and(A1="A",B<C,C<>D,D>E),"A222",if
if if if if if etc etc etc
This is why I will end up with over 90 "if" & "And" statement in one
cell. I don't even know if it is possible. As you can see in every if
statement, there is condition A1="A". I just didn't know how to lock
it or make it common to all other statements.
Thanks again!- Hide quoted text -
- Show quoted text - |
|
| |
| | | Pete_UK | |
| | Posted: Wed Jun 18, 2008 12:32 am Post subject: Re: Simple "If" Function - HELP | |
| | | |
A few other thoughts - if you don't have a purely consistent coding
system (i.e. the combinations for the "B" family might be different
than the "A" family etc), then you can use the approach I have
suggested to give you an intermediate code, and also have a lookup
table of those intermediate codes and the actual codes and use VLOOKUP
to translate into the "real" codes.
Hope this helps.
Pete
On Jun 18, 1:28 am, Pete_UK <pashu...@auditel.net> wrote:
| Quote: | Do the 3 digits which follow the letter all follow the same consistent
rules? i.e. does 1xx always mean B>C and 2xx mean B<C (and presumably
0xx mean B=C)? And does x1x always mean C=D, x2x mean C<>D? And does
xx1 always mean D<E and xx2 mean D>E?
There is some inconsistency here, but I'm just going off your example.
You basically have 4 possible outcomes of comparing two adjacent
columns:
B=C, B>C, B<C, and B<>C (if you count this)
and similarly with the other adjacent columns C to D and D to E. So
you could allocate a specific code to these conditions, i.e. 0, 1, 2
and 3 and thus calculate the overall code, something like:
=A1 & IF(B1=C1,0,IF(B1>C1,1,2)) & IF(C1=D1,0,IF(C1>D1,1,2)) &
IF(D1=E1,0,IF(D1>E1,1,2))
and then copy this down. This is more consistent, but does not check
for C<>D and thus does not tie up exactly with your example (but then
there were only two combinations shown !!). This will give you an
output like "A111", or "A020" depending on the conditions being met -
you might need to play about with it a bit to get your exact
combinations.
Hope this helps.
Pete
On Jun 18, 12:05 am, James8309 <jaedong1...@gmail.com> wrote:
On Jun 18, 3:47 am, "GB" <NOTsome...@microsoft.com> wrote:
"James8309" <jaedong1...@gmail.com> wrote in message
news:98914eb8-9963-4199-910e-4b2daf28b476@p39g2000prm.googlegroups.com....
Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance!
Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.
An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables.- Hide quoted text -
- Show quoted text -
Firstly, Thanks for your help!
Basically,
1. I have Range A1 to A850 containing alphabets. They are almost
distributed.
i.e. A1 might be B, A2 might be Z ... etc
2. Column B, C, D and E contains numbers.
3. Each alphabet in Range("A1:A850") has different families.
i.e. A will have A110, A220, A33, A555 etc depending on relationship
between Column B,C,D and E
Example, lets say A group
A111 = B>C, C = D, D<E
A222 = B<C, C<>D, D>E
etc etc etc
if I was to code this conditions in column F it would be something
like this
"
if(and(A1="A",B>C,C=D,D<E),"A111",if(and(A1="A",B<C,C<>D,D>E),"A222",if
if if if if if etc etc etc
This is why I will end up with over 90 "if" & "And" statement in one
cell. I don't even know if it is possible. As you can see in every if
statement, there is condition A1="A". I just didn't know how to lock
it or make it common to all other statements.
Thanks again!- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text - |
|
| |
| | | James8309 | |
| | Posted: Wed Jun 18, 2008 1:07 am Post subject: Re: Simple "If" Function - HELP | |
| | | |
On Jun 18, 10:32 am, Pete_UK <pashu...@auditel.net> wrote:
| Quote: | A few other thoughts - if you don't have a purely consistent coding
system (i.e. the combinations for the "B" family might be different
than the "A" family etc), then you can use the approach I have
suggested to give you an intermediate code, and also have a lookup
table of those intermediate codes and the actual codes and use VLOOKUP
to translate into the "real" codes.
Hope this helps.
Pete
On Jun 18, 1:28 am, Pete_UK <pashu...@auditel.net> wrote:
Do the 3 digits which follow the letter all follow the same consistent
rules? i.e. does 1xx always mean B>C and 2xx mean B<C (and presumably
0xx mean B=C)? And does x1x always mean C=D, x2x mean C<>D? And does
xx1 always mean D<E and xx2 mean D>E?
There is some inconsistency here, but I'm just going off your example.
You basically have 4 possible outcomes of comparing two adjacent
columns:
B=C, B>C, B<C, and B<>C (if you count this)
and similarly with the other adjacent columns C to D and D to E. So
you could allocate a specific code to these conditions, i.e. 0, 1, 2
and 3 and thus calculate the overall code, something like:
=A1 & IF(B1=C1,0,IF(B1>C1,1,2)) & IF(C1=D1,0,IF(C1>D1,1,2)) &
IF(D1=E1,0,IF(D1>E1,1,2))
and then copy this down. This is more consistent, but does not check
for C<>D and thus does not tie up exactly with your example (but then
there were only two combinations shown !!). This will give you an
output like "A111", or "A020" depending on the conditions being met -
you might need to play about with it a bit to get your exact
combinations.
Hope this helps.
Pete
On Jun 18, 12:05 am, James8309 <jaedong1...@gmail.com> wrote:
On Jun 18, 3:47 am, "GB" <NOTsome...@microsoft.com> wrote:
"James8309" <jaedong1...@gmail.com> wrote in message
news:98914eb8-9963-4199-910e-4b2daf28b476@p39g2000prm.googlegroups.com...
Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance!
Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.
An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables.- Hide quoted text -
- Show quoted text -
Firstly, Thanks for your help!
Basically,
1. I have Range A1 to A850 containing alphabets. They are almost
distributed.
i.e. A1 might be B, A2 might be Z ... etc
2. Column B, C, D and E contains numbers.
3. Each alphabet in Range("A1:A850") has different families.
i.e. A will have A110, A220, A33, A555 etc depending on relationship
between Column B,C,D and E
Example, lets say A group
A111 = B>C, C = D, D<E
A222 = B<C, C<>D, D>E
etc etc etc
if I was to code this conditions in column F it would be something
like this
"
if(and(A1="A",B>C,C=D,D<E),"A111",if(and(A1="A",B<C,C<>D,D>E),"A222",if
if if if if if etc etc etc
This is why I will end up with over 90 "if" & "And" statement in one
cell. I don't even know if it is possible. As you can see in every if
statement, there is condition A1="A". I just didn't know how to lock
it or make it common to all other statements.
Thanks again!- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
|
Pete, Thanks mate! You are a champ!  |
| |
| | | Pete_UK | |
| | Posted: Wed Jun 18, 2008 1:14 am Post subject: Re: Simple "If" Function - HELP | |
| | | |
You're welcome, James - thanks for feeding back.
Pete
On Jun 18, 2:07 am, James8309 <jaedong1...@gmail.com> wrote:
| Quote: | On Jun 18, 10:32 am, Pete_UK <pashu...@auditel.net> wrote:
A few other thoughts - if you don't have a purely consistent coding
system (i.e. the combinations for the "B" family might be different
than the "A" family etc), then you can use the approach I have
suggested to give you an intermediate code, and also have a lookup
table of those intermediate codes and the actual codes and use VLOOKUP
to translate into the "real" codes.
Hope this helps.
Pete
On Jun 18, 1:28 am, Pete_UK <pashu...@auditel.net> wrote:
Do the 3 digits which follow the letter all follow the same consistent
rules? i.e. does 1xx always mean B>C and 2xx mean B<C (and presumably
0xx mean B=C)? And does x1x always mean C=D, x2x mean C<>D? And does
xx1 always mean D<E and xx2 mean D>E?
There is some inconsistency here, but I'm just going off your example.
You basically have 4 possible outcomes of comparing two adjacent
columns:
B=C, B>C, B<C, and B<>C (if you count this)
and similarly with the other adjacent columns C to D and D to E. So
you could allocate a specific code to these conditions, i.e. 0, 1, 2
and 3 and thus calculate the overall code, something like:
=A1 & IF(B1=C1,0,IF(B1>C1,1,2)) & IF(C1=D1,0,IF(C1>D1,1,2)) &
IF(D1=E1,0,IF(D1>E1,1,2))
and then copy this down. This is more consistent, but does not check
for C<>D and thus does not tie up exactly with your example (but then
there were only two combinations shown !!). This will give you an
output like "A111", or "A020" depending on the conditions being met -
you might need to play about with it a bit to get your exact
combinations.
Hope this helps.
Pete
On Jun 18, 12:05 am, James8309 <jaedong1...@gmail.com> wrote:
On Jun 18, 3:47 am, "GB" <NOTsome...@microsoft.com> wrote:
"James8309" <jaedong1...@gmail.com> wrote in message
news:98914eb8-9963-4199-910e-4b2daf28b476@p39g2000prm.googlegroups.com...
Hi, Everyone
Let me first explain what I am trying to achieve very briefly.
1. If A1 = "E" and A2 >A3 and A3<A4 then return "ABC"
= > I know I can do this by using " if(and(A1="E",A2>A3,
A3>A4),"ABC",""))
However! However!!
2. if A1 = "E" stays same... except next criterias are different and
yes there are 95 of them. I just thought that it is painful to use
"And" function to hook up all the three criterias.
i.e. a) A1 = "E", A2<A3, A3=A4, then return "DEF" etc etc
b) A1 = "E", A2=A3, A4>A3 then return "XXX" etc etc etc etc x 95
times.
Is there any other way to lock that A1 = "E" ?? so I don't have to
type "And(A1 = "E", etc etc )" 95 times???
Should I be using VBA instead? Would that be easier?
Thank you for your help in advance!
Hmm, interesting. So, if I have understood you correctly, your cell will
contain 95 If statements all linked together with &s? You don't explain if
there is any logic in the way those ABCs and DEFs are generated. I can't
remember the maximum number of characters in a cell, but hopefully that
won't be a problem. However, actually generating the 95 If statements will
be (almost) impossible to do accurately by hand. I suggest that you create a
spreadsheet specifically to generate the formula you want to use.
An alternative is to use a scoring system, so A1 = A scores 1, through to
A1=Z scores 26. Also A2<A3 scores 100, A2= A3 scores 200, etc. (The scores
are done so that each combination is bound to give a unique value.) Then add
the 3 scores and use a lookup tables.- Hide quoted text -
- Show quoted text -
Firstly, Thanks for your help!
Basically,
1. I have Range A1 to A850 containing alphabets. They are almost
distributed.
i.e. A1 might be B, A2 might be Z ... etc
2. Column B, C, D and E contains numbers.
3. Each alphabet in Range("A1:A850") has different families.
i.e. A will have A110, A220, A33, A555 etc depending on relationship
between Column B,C,D and E
Example, lets say A group
A111 = B>C, C = D, D<E
A222 = B<C, C<>D, D>E
etc etc etc
if I was to code this conditions in column F it would be something
like this
"
if(and(A1="A",B>C,C=D,D<E),"A111",if(and(A1="A",B<C,C<>D,D>E),"A222",if
if if if if if etc etc etc
This is why I will end up with over 90 "if" & "And" statement in one
cell. I don't even know if it is possible. As you can see in every if
statement, there is condition A1="A". I just didn't know how to lock
it or make it common to all other statements.
Thanks again!- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
Pete, Thanks mate! You are a champ! - Hide quoted text -
- Show quoted text - |
|
| |
|
|
