Conditional Formatting of numbers, but not text, in recurring rows

[aj scott]

Situation:
I have a 2007 spreadsheet of repeating groups of 5 distinct rows, ie,
numbers in any one of the five represent something different from each of the
others tho' the values may be similar. Any one of the five rows may also have
random cells with text.
I have four "cell value" rules that work as I want -- their ranges and
priority effectively allowing them to act across all rows without conflict.
Problem:
Additionally I want to test for, & CF, Row 1 values < 116, but another row
has values that would be affected.
What I did:
--- New Rule > 'Format only cells that contain' > 'Cell Value' | 'less
than' | 116
--- specified the formatting
--- [Still in New Formatting Rule] > 'Use a formula...' > =MOD(ROW()-9,5)=0
[there are 8 rows of intro above the data]
--- OKed my way out.
--- This is the last rule evaluated
What's right:
--- Only Row 1 values < 116 are thus formatted
What's wrong:
--- All text in Row 1 meets that criterion! Actually, I'm sure it's not
paying attention to the "< 116", only to the MOD.

I don't want text to be CFd. Can I "AND" in a test for "ISNUMBER"? I can't
use ROW with it, because it's the cell level that has to be tested.
I tried preceding the problem rule with a rule to test for text, but it's
really bogus, =ISTEXT(CELL(0)), and doesn't work. Help, anyone?

 

[T. Valko]

I can't follow what you're doing with this very well but you can try to
combine the 2 conditions into 1 condition:

Instead of separate rules for:

'Cell Value' | 'less than' | 116
'Use a formula...' > =MOD(ROW()-9,5)=0

Combine them into a single "Use a formula" rule:

=AND(relative_cell_reference<116,MOD(ROW()-9,5)=0)

Note that if the cell is empty it will evaluate to be <116. To account for
that:

=AND(relative_cell_reference<>"",relative_cell_reference<116,MOD(ROW()-9,5)=0)

 

[aj scott]

Specifically, the 5 row sets are readings from 4 different sources plus a
date/time row.
The reason the rows recur is that the readings are reinitiated periodically.
The reason for the text is that if a reading is missing (or eccentric) a
word or two may be inserted.
Each of the conditions has the range of $B$9:whatever, expanding as new sets
are initiated.

Biff, thank you for your stab at a solution (a compound formula is what I'd
like to do), but it still leaves me up in the air as to what the
"relative_cell_reference" might be, how I would code it -- see above for my
desperate attempt with CELL. That's really the crux of my problem: How can I
make a general reference to a cell's value in a manner analagous to the way
the function ROW() operates in order that the CF fires only for numeric
values?

I'm afraid my perspective is in a rut; I need a new way of seeing. I hope
the added information will help you to help me.

Thanks again,

aj

 

[T. Valko]

If your first cell is B9:

=AND(B9<116,MOD(ROW()-9,5)=0)

Or:

=AND(B9<>"",B9<116,MOD(ROW()-9,5)=0)

The MOD portion will only be true on rows 9,14,19,24,29, etc., so both
conditions can only be true on the same row.

 

[aj scott]

But that's absolutely marvelous! Even tho' a specific address is given, the
formula generalizes to the entire range! You did indeed open my eyes. Thank
you for The Answer.

aj

PS I put the MOD clause before the B9 tests for efficiency's sake.

 

[T. Valko]

You're welcome. Thanks for the feedback!

PS I put the MOD clause before the B9 tests for efficiency's sake.

If you mean you did this:

=AND(MOD(ROW()-9,5)=0,B9<116)

It makes no difference. All the arguments are evaluated before the final
result is produced.

 

[aj scott]

This is a total aside, but I thought all languages [at execution] would
assign FALSE to an AND statement at the first false clause encountered in the
statement. Since it wouldn't matter if that false were followed by 200 true
clauses, why bother continuing to evaluate? But you're saying that Excel
plows right on? Does VBA? I'm quite sure VB doesn't.

aj

 

[T. Valko]

Hmmm...

You bring up a good point..."at execution".

I can't say that I know *exactly* how Excel does things at the execution
level.

It would seem prudent to terminate at the first encounter of FALSE rather
than continue.

Depending on what version of Excel you have, it has formula auditing tools
which steps through the formula evaluation process. It's my understanding
that this shows the *exact* sequence of steps that Excel uses to calculate
formulas. If you evalaute a formula like:

=AND(B9<116,MOD(ROW()-9,5)=0)

You can see that Excel is evaluating every argument.


--
Biff
Microsoft Excel MVP

This is a total aside, but I thought all languages [at execution] would
assign FALSE to an AND statement at the first false clause encountered in
the
statement. Since it wouldn't matter if that false were followed by 200
true
clauses, why bother continuing to evaluate? But you're saying that Excel
plows right on? Does VBA? I'm quite sure VB doesn't.

aj

 

[aj scott]

I agree: Hmmm... I'll have to fiddle around a bit, but I doubt that I'll
find a definitive answer since it's not clear what language a given snippet
of code is written in, AND there are so many other interesting things to
explore.

Thank you for all your replies. I'll mark this Answered.

aj

Hmmm...

You bring up a good point..."at execution".

I can't say that I know *exactly* how Excel does things at the execution
level.

It would seem prudent to terminate at the first encounter of FALSE rather
than continue.

Depending on what version of Excel you have, it has formula auditing tools
which steps through the formula evaluation process. It's my understanding
that this shows the *exact* sequence of steps that Excel uses to calculate
formulas. If you evalaute a formula like:

=AND(B9<116,MOD(ROW()-9,5)=0)

You can see that Excel is evaluating every argument.


--
Biff
Microsoft Excel MVP

This is a total aside, but I thought all languages [at execution] would
assign FALSE to an AND statement at the first false clause encountered in
the
statement. Since it wouldn't matter if that false were followed by 200
true
clauses, why bother continuing to evaluate? But you're saying that Excel
plows right on? Does VBA? I'm quite sure VB doesn't.

aj

You're welcome. Thanks for the feedback!

=AND(B9<116,MOD(ROW()-9,5)=0)
PS I put the MOD clause before the B9 tests for efficiency's sake.

If you mean you did this:

=AND(MOD(ROW()-9,5)=0,B9<116)

It makes no difference. All the arguments are evaluated before the final
result is produced.