Making certain cells with an “X” turn green (complicated formula)

green excel screen.jpg
I have got this formula:

=IF(OR(COUNTIF($AS2:$AW2,A$1),IF(ROW()>2,SUMPRODUCT(COUNTIF(INDEX($AS:$AW,ROW()-1,0),MOD(COLUMN()-1+{-2,-1,0,1,2},42)+1))>0,FALSE),IF(ROW()>2,SUMPRODUCT(COUNTIF(INDEX($AS:$AW,ROW()-2,0),MOD(COLUMN()-1+{-1,1},42)+1))>0,FALSE)),"X","")

In general I've got a set of 42 number ranging from 1 do 42 in normal order. Now I pick 5 random numbers out of those 42. They are presented in cells from AS to AW. Now I want every nubmer out of those 5 to be represented as an X and that previous X then creates 5 Xs below it and 2 Xs below those as shown in the screenshot.

Now, the screenshot depicts what I would like fully to achive. With the current formula I'm albe to get the "basic pattern". I would like additionally to turn some Xs green when a nubmer was selected that has been previously been covered by an X, and also it would be also nice when the total number of the green Xs would be shown in the AQ column.

Now, I could perfectly live with that, but if there is any chance to make the cells turn black when a number was selected that was not covered by an X I would be in heaven

Thank you in advance

Thank you very much for your reply, I am more than happy to provide an explanation

Alright, by „covered with an X” I mean the situation when a cell is filled with an “X” due to the “basic pattern” I have created. To give you a example, as you can see in the first set of 5 random numbers there is an 8 (which is the first number which was selected in selection process which has the aim of picking 5 random numbers out of the 42)

Now, the 8 is located in H2, it is black, because this is the first row, now according to the “basic pattern” the 8 creates 5 Xs in the row below itself ranging from F3 to J3 as well as 2 Xs two rows under itself, namely G4 and I4.

Now during the next process of picking 5 numbers a 9 was selected. The 9 occupies cell I3. I happens that I3 is a cell with an X due to the previous “basic pattern” that was used on the 8. In this instance I want that X to have a green background.

Now you can also see, that in the second process of picking 5 numbers a 12 was selected. The 12 sits in cell L3, which was not filled with an X, and therefore the background of this cell is supposed to be black.

Should you have any other questions, please do not hesitate to ask

This proposal employs four conditional formatting formulas in the following order:
For A4:AP17 (Green): =AND(COUNTIFS($AS4:$AW4,A$1),OR(COUNTIFS($AS2:$AW2,A$1-1),COUNTIFS($AS2:$AW2,A$1+1)))
For A3:AP17 (Green): =AND(COUNTIFS($AS3:$AW3,A$1),OR(COUNTIFS($AS2:$AW2,A$1-2),COUNTIFS($AS2:$AW2,A$1-1),COUNTIFS($AS2:$AW2,A$1),COUNTIFS($AS2:$AW2,A$1+1),COUNTIFS($AS2:$AW2,A$1+2)))
For A2:AP17 (Black): =AND(A2="x",COUNTIFS($AS2:$AW2,A$1),COUNTIFS($AS1:$AW1,A$1)=0)
For A2:AP17 (Green): =AND(A2="x",COUNTIFS($AS2:$AW2,A$1))
Note that the formulas in rows 18:33 are not needed, they are only left for demonstration of how the formatting formulas are applied.
Let us know if you have any questions.

As I was read your 1st post, I think that you only need condition format,
That's mean 1 and 42 will NEVER got a green mark, right ?
I don't need to takecare contents in cell that got 'X' or not, right ?
Just to check that if X was 'covered' with other 'X' (left & right) and listed in AS-AW, mark green or
if X was NOT 'covered' with other 'X' (left & right) and listed in AS-AW, mark black.

If yes, just use a conditon format

B2 : Mark Green

Please Login or Register  to view this content.

Note after insert column AS-AW will change to AT-AX

B2 : Mark Black

Please Login or Register  to view this content.

Note after insert column AS-AW will change to AT-AX

And for count how many X was covered, I need to insert a column between AP-AQ, so AQ will change to AR
and at AR2 can use this formula.

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for Array formula.

But I doubt , why my result in the row 2 and 5 is differnt from your's.
Row 2 : I've got 1, you've got 0
Row 5 : I've got 3, you've got 2


Regards.

JeteMC and menem I would like to thank you both very much for your help I very much appreciate it. Thank you Gentlemen

JeteMC Form what I can see you have got everything right Thnak you Sir. The only thing left would be to add the total number of green Xs in each row

menem I have utmost respect for people who try to help other people especially when they do it in a foreign language, and btw. I'm not a native english speaker, but from what I can tell you english is very good Again thank you Sir for your help.
When it comes to the diffriences in row 2 and 5 - in row 2 the number 27 should be black, and in row 5 number 37 should be black as well, because it doesn't fit the pattern that number 37 from the third row has created. Please remember the pattern consists of 5 Xs in the first row and 2 Xs in the second row and those 2 Xs have a free space inbetween them, that is why number 37 in row no. 5 should be black.
Now what you Sir have undoubtedly achieved is to count the number of green Xs in each row. Congratulations for that

JeteMC and menem If I may I would like to make an suggestion. In order to honor both of you Gentlemen for your help, I would like to propose to use a bit both of your solutions. In other words the "pattern solution" from JeteMC and the "counting of green Xs in a row" solution form menem. I believe integrating those two would make for a final solution to the thread

I'm still confuse. >_<
Please confirm.

AA2 (27) is black because Y3-AC3 , Z4-AB4 are X , (It will be green if AA4 is not X ? )
I3 (9) is green because match pattern G4-K4, H5, J5 are X and I5 is not X.

Why W3 (23) is green ? while U4-Y4, V5-X5 are X ?
(Same to W4, AA4, AB4, W5, X5)

No doubt about V6, AB6, AK6 that's fit the pattern.

Sorry to ask so much. T_T

Regards.

I would like to propose to use a bit both of your solutions. ...I believe integrating those two would make for a final solution to the thread

Great idea! Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. BTW this will not prevent you and menem from continuing your conversation in this thread. I hope that you have a blessed day.

JeteMc, slowdown please. I'm still confuse. >_<
I need more time and will be appiciate if you or bansche123 give me some picture of
GREEN / BLACK pattern.

For me, win/loss pattern are look the same. >_<

Regards.

I'm not sure is it correct. Please verify.

For Condition format of I3

Green :

Please Login or Register  to view this content.

Black :

Please Login or Register  to view this content.

For counting on AQ3

Please Login or Register  to view this content.

Press Ctrl-Shift-Enter for Array formula.

Regards.

I'm relief now. ^_^

BTW, my condition format is not directly check on X mark , you may try by delete cell's formula (A2:AP8) away. :P

Regards.

I just noticed that when I've copy condition format formula from I3 ( the orginal in creating formula ) it's refer reversed to row 1Ms.
So, I need to insert for 2 rows above old table for maintain row reference as it should be.

So the condition format formula of A4 ( Was A2 ) will changed to

Black

Please Login or Register  to view this content.

Green

Please Login or Register  to view this content.

Regards>

Unfortunately it dees not work. I made a new file with no formulas at all, maybe now it will be easier

Please be advised, that I have added a new feature so to say with the "EXTRA NUMBER" ranging form 1 to 4. Now I would need the same formulas for that as well with a diffrient pattern however, but the pattern is really simple. Whenever no. 1 or no. 3 are picked the X goes under both of those numbers, and when np. 2 or no. 4 selected the X goes under them.

May I ask ?

Did in the NEW black contains an X ?
And can you please tell me, why each NEW block is Black / Green.
(I still can not understand English well, so explain for each cell maybe easier for me. >_<" )

Regards.

Ps. If I continue, I will continue from my file.

Thank you for the reply, so answearing all your questions in order:

1.

Did in the NEW black contains an X ?

To be honest it does not really matter in the NEW case. If it will be easier to do without an X then here you go
2.

And can you please tell me, why each NEW block is Black / Green.

Honestly I am sorry, but I am not sure what you mean by "block". Essentially the idea behind the extra number is the same as it was before with the 42 numbers. The differences are:

1. 42 numbers changed to 35 numbers. Pattern stays the same, everything stayes the same, the only thing that changed is the amount of numbers.
2. Extra number was added and here the general idea behind it is the same as in the 35 numbers, but there is a different pattern.

Out of the 4 numbers only one number is chosen. The pattern looks like this: whenever number 1 or number 3 are picked then a X should appear beneath the 1 and the 3 in the next row - to give you an example:
- in row 2 number 3 was selected (AO2) this means an X has to appear in AM3 and AO3

Now, whenever number 2 or 4 are selected then the X has to appear below the 2 and the 4 - for example:
- in row 5 number 4 was selected (AP5) this means an X has to appear in AN6 and AP6

P.S. in the new file I only wanted to show what I mean by all of that. Of course, please feel free to continue in your file

Formula in condition format in my file.

AT4 : Black (Need to stop if true)

Please Login or Register  to view this content.

AT4 : Green

Please Login or Register  to view this content.

Regards.

Hi again I was trying to do the same thing that you have done, but this time for 50 numbers and 35 number only. I got the pattern with the Xs, but I don't know how to add the colours green and black to the pattern. May I kindly ask for your help? Also I have noticed that I cannot use the file you have created for the 42 numbers, because when I edit the formulas to fit 35 or 50 numbers for some reason it doesn't work that is why I would like to kindly ask you use both of my uploaded files here Thank you in advance

All you need is adding 2 blank rows (yellow cells)


Regards.

This is a first step, please verify X and color cells.
Many are different , please tell me why.

For example
My I4,C5 are X and Green
but your are only X. (Tell me that what I missed)

or

My I10 is X and Black
but your is X and Green.


Regards.