• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Does this conditional format equal even or odd

Jack-P-Winner

Jack-P-Winner

Member
I have been staring at this formula and I think its wrong.

=IF(AND($I$20="E",ISODD($AV4)),1,"")
=IF(AND($J$20="O",ISEVEN($AV4)),1,"")

I is the Even cell and OJ is the Odd cell. If I place an E in this cell is this statement saying it will be = to Odd?. I have several cells that are giving me an opposite reading and I think this may be the issue

Thanks
 
Last edited:
Hi Larry ,

What the statement says is :

If cell I20 has E in it , and if cell AV4 has an odd number in it , then output 1 , else output a blank.

So if this formula is entered in A1 , then A1 will have 1 in it if both conditions are satisfied i.e. cell I20 has to have E in it , and cell AV4 has to have an odd number in it. If either of these conditions is not satisfied , or if both coditions are not satisfied , then A1 will display a blank.

Narayan
 
hmm, this is one of the cells that is backwards or so I think. when I put the draw numbers in it high lights Red (odd) and in order to be correct I have to place an E in that cell. Would it be fixed if I rewrote the command and switched the I and J?
 
Hi Larry ,

As I understand it , even numbers are colored GREEN , while odd numbers are colored RED , but the column I is only testing for even numbers , while column J is only testing for odd numbers ; am I correct ?

Thus , column I numbers will never be colored RED even if they are odd , while column J numbers will never be colored GREEN even if they are even.

Narayan
 
Yes you are partially correct. I can only be E and J can only be O. The red and green cells are next to them in GH. I fixed a few of the U and Ds as they were taking off numbers in the wrong location AI AJ AK AL
 
should I be looking at the CF for GH? I may as well look. I need some of them to color opposite. If it says Red , I need it to be Green
 
AI AJ AK AL AN. It is the ones that take away the numbers

These cells determine which numbers go away when an UD or EO is placed in the cells IJ TU AE AF )next to the colored green and red cells)
 
Row J20 is making me place a E in cell I20 to make this true [be able to show the draw number 4 (AJ4)]

If I place an O in cell J20 the 4 disappears
 
I need to figure out how t change the number(s) in cells A 11-22. The numbers in those cells determine if the cells in GH will be Red or Green

I can put a -1 in the cell and that will change it but that's bot the right way to fix it. A -1 has to be in all those cells
 
Last edited:
Hi Larry ,

What I am finding is that there are always 4 cells which are colored GREEN in columns AI , AJ , AK and AL ; these are the 4 numbers in cells A3 through A6.

What the E , O , U and D in columns I , J , T , U , AE and AF do is colour the other cells BLACK.

The 4 cells which were colored GREEN continue to remain colored GREEN.

Putting E , O , U or D in any of the above columns does not in any way change the values or the formatting in columns G , H ,...

Narayan
 
That is correct what you are saying. I "want" to change the values in the cells that are colored incorrectly. Issue is I can not find where to change them?
 
Hi Larry ,

That is what I am saying , that the cells which are colored GREEN in the columns AI , AJ , AK and AL will never change , since they are dependent only on the numbers which are in the range A3 through A6.

You can try and change the GREEN and RED colored cells in columns G , H , R , S , AC and AD.

Which ones do you wish to change ?

Narayan
 
That's it Narayan, I wish to change the colors in cells GHRS AC and AD. You got it !!!!! That is one of my main issues LOL. It varies from game to game. In Game 49 it is cell J2. In another game it is in another cell. I have issues with at least 7/10 games with 1 cell or another. If I can figure out how to change ONE, I can do the rest

My other issues has to do with the reset....talk on that later LOL YAY!!!!!
 
Hi Larry ,

The values in columns G and H are colored GREEN or RED in a very simple way ; if the number in the corresponding cell in column A is even , then the cell in column G is colored GREEN ; similarly , if the number in the corresponding cell in column A is odd , then the cell in column H is colored RED.

The problem is only with cells in columns R , S , AC and AD.

Narayan
 
correct again and I have issues with GH also. My question is "how do I change that number in the cell for column A" I know if there is a 7 then I will get Odd and Red and if there is a 6 I will get Even and Green. I need to know how to change the 7 to a 6 for example
 
Hi Larry ,

G and H are no problem.

If we take the numbers in rows 11 through 14 ,

A11 depends on 'Drawn Numbers'!$C12-1 , which in turn depends on 'DO NOT DELETE OR ALTER!!!!!!!!!'!B12

A12 ................. 'Drawn Numbers'!$N12-1 ....................................... 'DO NOT DELETE OR ALTER!!!!!!!!!'!E12

A13 ................. 'Drawn Numbers'!$Y12-1 ....................................... 'DO NOT DELETE OR ALTER!!!!!!!!!'!H12

A14 ................. 'Drawn Numbers'!$AJ12-1 ...................................... 'DO NOT DELETE OR ALTER!!!!!!!!!'!K12

The latter have the COUNTIF formula , which I had posted in an earlier thread.

The basic variables in the above formula are the INPUT numbers.

Narayan
 
Narayan,

I understand all of this but I can not change what your calling the variable which are the Input numbers. Once an input number has drawn it is what it is. I then put these numbers into this system. I can not go back and change the drawn number because then I will have invalid information. It is like if you are playing Bingo and they call G12. On your ticket you do not have G12 instead you have H12 so you change your ticket from H12 to G12. When you bring your ticket up to the person calling the Bingo numbers they will see you changed the number and letter and you will not win. This is the same way kind of. I can not change what is drawn, There has to be another way to change the cell from Green to Red

What am I missing?
 
Are you suggesting that 'DO NOT DELETE OR ALTER!!!!!!!!!'!B12 has errors?

=(COUNTIF(C10:C108,C12)+COUNTIF(F10:F108,C12)+COUNTIF(I10:I108,C12)+COUNTIF(L10:L108,C12)+COUNTIF(Y10:Y108,C12)+COUNTIF(Z10:Z108,C12)-(COUNTIF(C10:C11,C12)+COUNTIF(F10:F11,C12)+COUNTIF(I10:I11,C12)+COUNTIF(L10:L11,C12)+COUNTIF(Y10:Y11,C12) +COUNTIF(Z10:Z11,C12)))
 
Hi Larry ,

I do not know what this formula is supposed to do , so I cannot say if it is wrong ; can you explain what you want this formula to do ?

Narayan
 
I can explain but it will not make a difference for what I am trying to do. hmmm, lets go back to post #20 if you do not mind
 
Hi Larry ,

We can do one thing , which is to start all over again !

Either we verify whether the formulae are all correct , or we start from the first game , and go over everything one game at a time , or we identify at what point the errors start , and go back one step and see if we can identify the cause.

The COUNTIF formula is just one formula ; there are formulae in a lot of the other columns , and only you can say whether they are correct or wrong.

Narayan
 
I understand and I am sure some formula is not correct. I am missing something that turns these on and off so to speak. UG LOL I have been going through these 1 cell at a time. Thanks Narayan, I was hoping it would be something simple to find
 
You must log in or register to reply here.
Back
Top