Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring rows
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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" wrote: 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_ref erence<116,MOD(ROW()-9,5)=0) -- Biff Microsoft Excel MVP "aj scott" <aj wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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. -- Biff Microsoft Excel MVP "aj scott" wrote in message ... 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" wrote: 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_ref erence<116,MOD(ROW()-9,5)=0) -- Biff Microsoft Excel MVP "aj scott" <aj wrote in message ... 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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" wrote: 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. -- Biff Microsoft Excel MVP "aj scott" wrote in message ... 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" wrote: 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_ref erence<116,MOD(ROW()-9,5)=0) -- Biff Microsoft Excel MVP "aj scott" <aj wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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. -- Biff Microsoft Excel MVP "aj scott" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "aj scott" wrote in message ... 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" wrote: 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_ref erence<116,MOD(ROW()-9,5)=0) -- Biff Microsoft Excel MVP "aj scott" <aj wrote in message ... 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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" wrote: 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. -- Biff Microsoft Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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 "aj scott" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting of numbers, but not text, in recurring
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 "T. Valko" wrote: 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 "aj scott" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recurring Numbers | Excel Discussion (Misc queries) | |||
Numbers & text in same cell - conditional formatting | Excel Worksheet Functions | |||
conditional formatting with numbers and text | Excel Worksheet Functions | |||
Denoting recurring numbers in formulas | Excel Worksheet Functions | |||
How do I generate non-recurring random numbers to assign groups? | Excel Discussion (Misc queries) |