Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Please help a rookie!

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    May 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    May 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi- see my edit.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •