Please help a rookie!

[katiekat]

Hi, I'm a rookie, and would appreciate help.
my spread sheet looks something like this.
A B
79/6:30am 235/11:30am
The numbers before the forward slash eg: 79 and 235 are blood sugar numbers and after the forward slash are the times they were taken.
I want to develop a formula so that when the blood sugar numbers are below 80 the cell is red and when numbers are over 180 the cell is blue. I'm finding it difficult to find a formula that will change the cell color based on numbers before the slash and ignore the ones after it. Thanks

[Yogi Anand]

On 2002-05-09 20:21, katiekat wrote:
Hi, I'm a rookie, and would appreciate help.
my spread sheet looks something like this.
A B
79/6:30am 235/11:30am
The numbers before the forward slash eg: 79 and 235 are blood sugar numbers and after the forward slash are the times they were taken.
I want to develop a formula so that when the blood sugar numbers are below 80 the cell is red and when numbers are over 180 the cell is blue. I'm finding it difficult to find a formula that will change the cell color based on numbers before the slash and ignore the ones after it. Thanks

If the blood pressure readings are in cells A1 and B1
and the lower and upper limits of interest are incells A2 and A3
then use the following Conditional formula for cells A1 and B1
Condition1 ... Formula is:
=LEFT(A1,FIND("/",A1,1)-1)+0 Format|Pattern -- color --> 'red'

Condition2 ... Formula is:
=LEFT(A1,FIND("/",A1,1)-1)+0>A3 ...
Format|Pattern -- color --> 'blue'

Please post back if it works for you ... otherwise explain a little further and let us take it from there!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:17 ]

[dsnbld]

The sugar levels and reletive times need to be in two different columns/cells. Then you can use conditional formatting [Menu/Format/Conditional Formatting].

Yogi posted at the same time I did...go with his post.

[ This Message was edited by: dsnbld on 2002-05-09 20:39 ]

[Yogi Anand]

On 2002-05-09 20:38, dsnbld wrote:
The sugar levels and reletive times need to be in two different columns/cells. Then you can use conditional formatting [Menu/Format/Conditional Formatting].

Hi dsnbld:
You are right in that it would be simpler to do it that way -- however, see my post above, wherein I have extracted the values of interest from the associated strings, coerced them into numbers and then I applied the Conditional formatting.

Regards!

[Jay Petrulis]

Hi,

Welcome to the board.

If A1 has the 79/6:30am entry, do the following

1. Select A1

2. Pull down the Format menu
Format>Conditional Formatting

3. Condition1
Formula Is
=(LEFT($A$1,SEARCH("/",$A$1,1)-1)+0)<80
and then Format as Red

4, Condition1
Formula Is
=(LEFT($A$1,SEARCH("/",$A$1,1)-1)+0)>180
and then Format as Blue

Adjust the range to match your data and format all the cells in this manner.

Bye,
Jay

EDIT: It is amazing what a browser refresh will do. Sorry about the duplicate post. Nice job, Yogi.

[ This Message was edited by: Jay Petrulis on 2002-05-09 20:48 ]

[dsnbld]

Yogi- see my edit.

[Yogi Anand]

Hi Katiekat:
My formula for Condition1 shoul read

=Left(A1,find("/",A1,1)-1)+0
but it does not show correctly in my post, even though I have input it correctly. I have had this problem a number of times, when my input does not match what the board shows -- perhaps some one will clarify for me why is this happening in some cases but not all the cases.

Hi Jay:

Our responses are pretty identical except for the difference I noted above, and in your formula it says Condition1 twice -- second time around it should be Condition2.

Regards!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:18 ]

[katiekat]

Thanks but no luck. This spreadsheet has a several month history of blood sugar numbers. I want to be able to automatically color the high and low numbers. Each cell has different numbers and times. If sugar level is below 80 it is "low" and I want to be able to highlight it automatically. If the number is over 180 I aslo need to highlight that a different color as that is a "high" blood sugar number. Between 80 and 180 is Ok and is not colored. The top of the spreadsheet has headings over each respective columms like Breakfeast Lunch Dinner and Bedtime. The left rows have the dates and days of the week. Each individual cell has both the blood sugar level and the time it was taken. these are seperated in each cell by a forward slash. EG: 59/6:02pm.
If the blood sugar happens to be 67 at 5pm then I type in the cell 67/5:00pm I want that to automatically turn red when I press enter. because it is under 80. and Blue if it is over 180 regardless of what cell column or row I am using and regardless of what time the blood sugar was taken. Thanks again for your help
Katie

[Brian from Maui]

Katie,

Do give up so fast!

Highlight the columns or range with the sugar levels and use Yogi's or Jay's formula. You can do more than one cell at a time. It shouldn't take you too long to do it.

[Yogi Anand]

On 2002-05-10 00:06, Brian from Maui wrote:
Katie,

Do give up so fast!

Highlight the columns or range with the sugar levels and use Yogi's or Jay's formula. You can do more than one cell at a time. It shouldn't take you too long to do it.

Hi Katie:
Just like Brian said, the Conditional Formating formulas that have been posted in response to you question have been setup for one cell -- and now here is the beauty of Excel. Nowcopy the cell that's been correctly formatted, and copy its format to all the other cells , hundreds, thousands, or tens of thousands, at no extra charge.

Please post back if it works for you ... otherwise explain a liitle further and let us take it from there.

Katie, if you want you can email your file with some recods -- i can look at it and mark which cells will have to have conditional formatting and how that will be done.

NB:
I have taken your Low and High levels as cell references -- that means, should you need to update the numbers associated with tracking the Low and High end values, you would have to change the numbers in one place and not hundreds or thousands of locations.

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:18 ]