Help with cell programming!
Solved/Closed
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013
-
Jul 19, 2013 at 03:02 AM
ozfox999 - Aug 1, 2013 at 07:47 PM
ozfox999 - Aug 1, 2013 at 07:47 PM
Related:
- Help with cell programming!
- R programming language download - Download - Programming languages
- Utorrent programming language - Guide
- Java programming app for pc - Download - Other
- Scratch programming download - Download - Programming languages
- Python programming app for pc - Download - Programming languages
10 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 20, 2013 at 07:13 AM
Jul 20, 2013 at 07:13 AM
=IF(AND(C3="X",H4>4),"y",IF(AND(C3="Y",H4<-4),"Y",""))
do coditioal formatting manually
do coditioal formatting manually
Hi venkat1926,
It is heartening to see that people can help each other for abolutely no personal gain. You have helped me immensely, for which I am grateful.
Thank you very much.
Ozfox999
It is heartening to see that people can help each other for abolutely no personal gain. You have helped me immensely, for which I am grateful.
Thank you very much.
Ozfox999
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 21, 2013 at 01:27 AM
Jul 21, 2013 at 01:27 AM
you are welcome
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013
Jul 22, 2013 at 12:16 AM
Jul 22, 2013 at 12:16 AM
Hi venkat1926.
I am hoping that this message will get to you personally and you can be kind enough to help me out once more. The last question, although answered perfectly, still leaves me with a problem, probably due to my inability to describe it clearly. Here is my second attempt:
In answer to my initial question, you provided me with:
=IF(AND(C3="X",H4>4),"X",IF(AND(C3="Y",H4<-4),"Y",""))
which worked perfectly, thank you.
However, now, I require to broaden the scope a little more, to include the following:
Problem 1
IF C3 > 4, X appears
AND IF C3 > 4, Y also appears (or is it OR?)
IF C3 <-4 Y appears
AND IF C3 <-4 X also appears (or is it OR?)
Otherwise "No Action"
Can we do that?
Although I have tried
=IF(AND(C3="X",-4>H4>4),"X",IF(AND(C3="Y",-4>H4<-4),"Y",""))
It appears that the X condition takes precedence over the Y condition and Y does Not appear.
Problem 2
Out of interest, what about if I wanted the following:
IF C3 > 4, X appears
AND IF C3 > 8, Y also appears (or is it OR?)
IF C3 <-4 Y appears
AND IF C3 <-8 X also appears (or is it OR?)
Otherwise "No Action"
Maybe in this case, instead of having 8 or -8 we can use, say, $P$1 for 8 and $Q$1 for -8? This would allow to change this threshold more easily...
I would be most grateful if you could help me on this one, with many thanks in advance.
Kind regards
Joseph Tefaye
I am hoping that this message will get to you personally and you can be kind enough to help me out once more. The last question, although answered perfectly, still leaves me with a problem, probably due to my inability to describe it clearly. Here is my second attempt:
In answer to my initial question, you provided me with:
=IF(AND(C3="X",H4>4),"X",IF(AND(C3="Y",H4<-4),"Y",""))
which worked perfectly, thank you.
However, now, I require to broaden the scope a little more, to include the following:
Problem 1
IF C3 > 4, X appears
AND IF C3 > 4, Y also appears (or is it OR?)
IF C3 <-4 Y appears
AND IF C3 <-4 X also appears (or is it OR?)
Otherwise "No Action"
Can we do that?
Although I have tried
=IF(AND(C3="X",-4>H4>4),"X",IF(AND(C3="Y",-4>H4<-4),"Y",""))
It appears that the X condition takes precedence over the Y condition and Y does Not appear.
Problem 2
Out of interest, what about if I wanted the following:
IF C3 > 4, X appears
AND IF C3 > 8, Y also appears (or is it OR?)
IF C3 <-4 Y appears
AND IF C3 <-8 X also appears (or is it OR?)
Otherwise "No Action"
Maybe in this case, instead of having 8 or -8 we can use, say, $P$1 for 8 and $Q$1 for -8? This would allow to change this threshold more easily...
I would be most grateful if you could help me on this one, with many thanks in advance.
Kind regards
Joseph Tefaye
Didn't find the answer you are looking for?
Ask a question
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013
Jul 22, 2013 at 12:29 AM
Jul 22, 2013 at 12:29 AM
Hi Vencat1926,
I just realized that I made a mistake in my last. Please find the correction below, with apologies, although I do believe that you would have worked it out :).
In answer to my initial question, you provided me with:
=IF(AND(C3="X",H4>4),"X",IF(AND(C3="Y",H4<-4),"Y",""))
which worked perfectly, thank you.
However, now, I require to broaden the scope a little more, to include the following:
In all cases, the operative cell is I4
Problem 1
IF H4 > 4, AND C3= X, X appears in I4
AND (or is it OR?) IF H4 > 4, AND C3= Y, Y also appears in I4 (or is it OR?)
IF H4 < -4, AND C3= Y, Y appears in I4
AND (or is it OR?) IF H4 < -4, AND C3= X, X also appears in I4 (or is it OR?)
Otherwise "No Action"
Can we do that?
Although I have tried
=IF(AND(C3="X",-4>H4>4),"X",IF(AND(C3="Y",-4>H4<-4),"Y",""))
It appears that the X condition takes precedence over the Y condition and Y does Not appear.
Problem 2
Out of interest, what about if I wanted to do the following:
IF H4> 4, AND C3= X, X appears in I4
AND (or is it OR?) IF H4> 8, AND C3= Y, Y also appears in I4 (or is it OR?)
IF H4<-4 AND C3= Y, Y appears in I4
AND (or is it OR?) IF H4<-8 AND C3= X, X also appears in I4
Otherwise "No Action"
Thanks again
Joseph
I just realized that I made a mistake in my last. Please find the correction below, with apologies, although I do believe that you would have worked it out :).
In answer to my initial question, you provided me with:
=IF(AND(C3="X",H4>4),"X",IF(AND(C3="Y",H4<-4),"Y",""))
which worked perfectly, thank you.
However, now, I require to broaden the scope a little more, to include the following:
In all cases, the operative cell is I4
Problem 1
IF H4 > 4, AND C3= X, X appears in I4
AND (or is it OR?) IF H4 > 4, AND C3= Y, Y also appears in I4 (or is it OR?)
IF H4 < -4, AND C3= Y, Y appears in I4
AND (or is it OR?) IF H4 < -4, AND C3= X, X also appears in I4 (or is it OR?)
Otherwise "No Action"
Can we do that?
Although I have tried
=IF(AND(C3="X",-4>H4>4),"X",IF(AND(C3="Y",-4>H4<-4),"Y",""))
It appears that the X condition takes precedence over the Y condition and Y does Not appear.
Problem 2
Out of interest, what about if I wanted to do the following:
IF H4> 4, AND C3= X, X appears in I4
AND (or is it OR?) IF H4> 8, AND C3= Y, Y also appears in I4 (or is it OR?)
IF H4<-4 AND C3= Y, Y appears in I4
AND (or is it OR?) IF H4<-8 AND C3= X, X also appears in I4
Otherwise "No Action"
Thanks again
Joseph
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 22, 2013 at 01:08 AM
Jul 22, 2013 at 01:08 AM
open this file
http://speedy.sh/fMeYk/oxfox999-130722.xlsx
now C3 and H4 (red)have validation list. when you select one of these cells you will see an arrow. click and choose any of those values. then see the formula in F1(yellow). change theentries in C3 and H4 and see F1. you can copy this formula anyhere (I4 also) excelpt c3 and H4
some of your conditions are repeat of previous. carefully look at those conditons.
I leave it as an exercise for the second problem
http://speedy.sh/fMeYk/oxfox999-130722.xlsx
now C3 and H4 (red)have validation list. when you select one of these cells you will see an arrow. click and choose any of those values. then see the formula in F1(yellow). change theentries in C3 and H4 and see F1. you can copy this formula anyhere (I4 also) excelpt c3 and H4
some of your conditions are repeat of previous. carefully look at those conditons.
I leave it as an exercise for the second problem
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013
Jul 22, 2013 at 02:24 AM
Jul 22, 2013 at 02:24 AM
Unbelievable. Your work is pure magic. Thank you heaps venkat!
ozfox999
Posts
5
Registration date
Friday July 19, 2013
Status
Member
Last seen
July 31, 2013
Jul 31, 2013 at 06:19 PM
Jul 31, 2013 at 06:19 PM
Hi Venkat,
Once again, I have a small problem and I hope you are not too busy to offer me some assistance. This one is fairly simple and I think I am having a problem with operator/statement precedence.
Looking at the cells below, I have the following condition:
A B
1 A
2 B C
3 A C
4 A
5
IF A1 = "A", then A2 = "B", IF A2 = "B", then A3 = "A", etc, repeating all the way down column A, to get an alternate As and Bs.
Now, I also need to introduce the following condition, ON TOP of the one above:
IF B2 = "C" AND B3 = "C", Then A4 = A3 (or, in this case, "A").
Thanking you in advance for your continued support.
Kind regards
Joseph
Once again, I have a small problem and I hope you are not too busy to offer me some assistance. This one is fairly simple and I think I am having a problem with operator/statement precedence.
Looking at the cells below, I have the following condition:
A B
1 A
2 B C
3 A C
4 A
5
IF A1 = "A", then A2 = "B", IF A2 = "B", then A3 = "A", etc, repeating all the way down column A, to get an alternate As and Bs.
Now, I also need to introduce the following condition, ON TOP of the one above:
IF B2 = "C" AND B3 = "C", Then A4 = A3 (or, in this case, "A").
Thanking you in advance for your continued support.
Kind regards
Joseph
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Aug 1, 2013 at 04:20 AM
Aug 1, 2013 at 04:20 AM
download thils and see colored cell whlich have formulas
http://speedy.sh/U2m8V/ozfox-130801.xlsx
http://speedy.sh/U2m8V/ozfox-130801.xlsx
