Can someone explain these CF formulas to me

How can something be even and odd at the same time? What is the difference between this code?

'=ISEVEN($B$25) $L$1,$L$9,$L$17,$R$17,$R$9,$R$1,$L$27,$R$27,$L$30,$R$30,$L$33,$R$33

'=ISODD($B$25) $L$1,$L$9,$L$17,$R$17,$R$9,$R$1,$L$27,$R$27,$L$30,$R$30,$L$33,$R$33



Can someone tell me how to interpret these formulas below?

'=IF(AND($AF$19="D",$AV2>$W$19),1,"") $AI$2:$AI$56

'=IF(AND($AE$19="U",$AV2<$W$19),1,"") $AI$2:$AI$56





'=IF(AND($AF$32="D",$BA2>$W$32),1,"") $AN$2:$AN$36

'=IF(AND($AE$32="U",$BA2<$W$32),1,"") $AN$2:$AN$36




'=IF(AND($I$20="E",ISODD($AV3)),1,"") $AJ$2,$AJ$4,$AJ$6,$AJ$8,$AJ$10,$AJ$12,$AJ$14,$AJ$16,$AJ$18,$AJ$20,$AJ$22,$A J$24,$AJ$26,$AJ$28,$AJ$30,$AJ$32

'=IF(AND($J$20="O",ISEVEN($AV3)),1,"") $AJ$2,$AJ$4,$AJ$6,$AJ$8,$AJ$10,$AJ$12,$AJ$14,$AJ$16,$AJ$18,$AJ$20,$AJ$22,$A J$24,$AJ$26,$AJ$28,$AJ$30,$AJ$32



'=IF($A3>$L3,1,"") $R$11:$R$15,$R$3:$R$7,$R$19:$R$23 ( I know this says that is A3 is greater than L3 to put a 1 or is it add 1 in one of these cells (
$R$11:$R$15,$R$3:$R$7,$R$19:$R$23) am I correct?


'=IF($A3<$L3,1,"") $S$11:$S$15,$S$3:$S$7,$S$19:$S$23

If I have a few more formulas I want an explanation for should I open a new post or ask fora few more in this one? I want to know the best way to handle this please so i do not upset anyone or the board rules (-:

Thanks for your expertise ya'll!!

At first sight none of these formulae make sense to me. Do they produce anything but #VALUE! ?

I've just thought… are these conditional format formulae followed by the ranges they apply to?
So in the case of the ISODD/ISEVEN pair at the top these will conditionally format the cells $L$1,$L$9,$L$17,$R$17,$R$9,$R$1,$L$27,$R$27,$L$30,$R$30,$L$33,$R$33 one way if B25 is even, and anotherway if B25 is odd, with a mix of formats possibly appearing in those cells at one time.
Conditional formatting formulae should really only evaluate to TRUE or FALSE, but in the rest of your formulae a 1 counts as TRUE and a "" as FALSE.
These formulae can be shortened:
=IF(AND($AF$19="D",$AV2>$W$19),1,"")
becomes:
=AND($AF$19="D",$AV2>$W$19)

=IF(AND($I$20="E",ISODD($AV3)),1,"")
becomes:
=AND($I$20="E",ISODD($AV3))

=IF($A3<$L3,1,"")
becomes:
=$A3<$L3
etc.

yes these are all CF rules I have. I had someone else write some rules and I'm trying to figure out what they are doing. I have some things on my spreadsheet not working like they should and I'm trying to troubleshoot and see what's going on. So, first I need to understand what's written lol. Let me digest this and respond back please so I totally understand

This is in my Conditional formatting:

'=IF(AND($I$19="E",ISODD($AX2)),1,"") (Range) $AI$2:$AI$56

When you place a E in I19 how does it know to take away certain numbers in column AI? You can remove the E from I19 and the cells in column AI will go back to black. Place a E or O in any of the cells in columns IJ that they will take a way certain numbers in AI:AN

I want to know what tells AI:AN to black out certain numbers (hope this makes sense)

Hopefully you can help me out and if you need additional info let me know or sheets but most of all THANK YOU for helping or if this is going to be too much to deal with please let me know as I know time is valuable to all

Quote:

---

Originally Posted by Larbec

'=IF(AND($I$19="E",ISODD($AX2)),1,"") (Range) $AI$2:$AI$56

When you place a E in I19 how does it know to take away certain numbers in column AI? You can remove the E from I19 and the cells in column AI will go back to black. Place a E or O in any of the cells in columns IJ that they will take a way certain numbers in AI:AN

I want to know what tells AI:AN to black out certain numbers (hope this makes sense)

---

It's to do with absolute and relative references in the formula; see https://www.youtube.com/watch?v=_PBPU_5jTUA which explains it, moreover it talks about it regarding conditional formatting from 37 minutes into the video.

Looking at your workbook:
It has some 300+ separate different conditional formats - some only applying to 1 cell, some to many. It is very hard work to sort it out. You would do better, as I said in my previous message in your other thread here: http://www.vbaexpress.com/forum/show...l=1#post331136 , to remove all formatting (work on a copy of the sheet), both conditional and non-conditional formatting and re-introduce them. You will probably need only about 10.

The person who wrote the CFs for you wrote formulae which were longer than necessary - eg. you can dispense with the red in the below formula
=IF(AND($I$19="E",ISODD($AX2)),1,"")
leaving:
=AND($I$19="E",ISODD($AX2))

I will watch the video. I am sure it will enlighten me more I have already rewritten the actual game sheet CF for all the coloring of the cells. I have a feeling doing the rest may be a nightmare lol. If I do not understand how a letter like E takes away certain numbers to begin with I can not re write the logic... although, I have the charts in hand what numbers to take away with certain number groups. The part I need to understand better is the absolute and relative references in the formula as you stated above. Eventually I will figure it out as I get better (-: Again, thanks for your help

You may be confusing the "test" as apposed to the "action". That is the conditional format (action) and when to apply the action"The Test". In the following =IF(AND($I$19="E", ISODD($AX2,1,"") (Range) $AI$2:$AI$56 is basically testing for cell $I$19 value to be "E" and cell $AX2 to be odd then carry out the required action ( remove some numbers) from the range $AI$2:$AI$56. Excel later versions understands the use of IF, and the logical parts true or false and these no longer need to be used in the construct of the formula.

Aussie,

its much uch appreciated the way you explained it What I. Struggling with is the required action. I understand what's written in the CF but where does it go (what set of formulas ) that says which numbers to be removed? If I wanted to add an additional number to be taken away how would I go about doing this?

Thanks

Open up the workbook, select the worksheet you wish to examine. Click on Format/ Conditional Formatting and if my version is the same as yours a small window will pop up "Manage Rules". Make sure the "This Sheet" option is selected and it should then show the current rules for that sheet

Aussie,

i very familiar liar with the way to use the CF "Manage Rules" and can do some programming. As a matter if fact I have my entire sheets CF rules in an excel file I can't seem to figure out "how it knows specifically which numbers to remove when a E O U D is palaces in a cell)". if it's in my rules I'm missing it big time If you can explain the trail for just 1 cell I can do the rest. I "want" to do the rest.

What I see in the rules is only fir example: the letter E is placed in I:19 and "some" numbers are removed in AI2:AI56. What are the "some" numbers (which numbers) and what "other" formulas determine the "some" numbers

Again, in, thank you for your expertise and valuable time

Quote:

---

Originally Posted by Larbec

I can't seem to figure out "how it knows specifically which numbers to remove when a E O U D is palaces in a cell)". if it's in my rules I'm missing it big time

---

A cell such as AI8 has 17 conditional formatting rules to decide how it's formatted! It's hard work to work out which one is influencing the appearance of the cell esecially when 16 of them all turn the cell black. If the cell is black you have to trundle through 16 formulae to see which one is TRUE! Most of the 16 conditional formats are probably not needed, or can be condensed to 1. It's very hard work for others trying to help you to reverse engineer the formulae to try to determine intent.

So rather than us trying to analyse existing CFs, you tell us how a given cell should appear - in words. Better still, if you can say that a group of cells (rather than a single cell) should have the same appearance at the same time due to the same conditions elsewhwere on the sheet, then a single CF could be applied to all the cells at the same time.

Quote:

---

Originally Posted by Larbec

If you can explain the trail for just 1 cell I can do the rest.

---

As above, but rather than talking about one cell, let's talk about one condition; over to you to supply, in words, that condition, and which cells it should affect.
The sort of thing I'd be looking for is:

"Turn any cell in the range AI2:AI56 black if the corresponding cell in column B is greater than 100 and cell C2 has an odd number."

There could be multiple conditions for a whole group of cells - they might be combined into one formula, maybe not - let's see how complicated the conditions (expressed in words) are.

A little background as to what this sheet is for and what you're trying to highight might help us too.

Thanks P4. Let me wrap my brain around my charts and see how to put it into a logical approach

Quote:

---

Originally Posted by Larbec

I had someone else write some rules and I'm trying to figure out what they are doing.

---

Then in all sincerity, go back to this person and ask them to explain how and why the rule was written the way it is.

I wish I could, he has passed on.... so I am trying to deal with all this the best I can. He was a good friend

Well on that basis, ( and I do feel for your loss of your friend), unless you can convince us of the merits of your workbook, and given the complexity of the conditional formatting per individual cells, you are in for a long row. Maybe, you need to sit down and re-evaluate the concept, and then tell us in clear terms just what you were chasing to get the workbook to perform as you wish. BTW: I notice this thread has been marked as "solved", is that your understanding of the issue?

Aussie,

yours and others help is much appreciated. Let's leave this as being solved Thank you and P45 for the what you have done so far. You have me thinking and this has been not only an emotional task for me but also forces me to apply myself more with studying even harder which is a good thing Both of you have a blessed day!