+ Reply to Thread
Results 1 to 8 of 8

Greater Than - Less Than - Equal to.... formula help

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    Prattville Alabama
    MS-Off Ver
    Excel 2003
    Posts
    4

    Greater Than - Less Than - Equal to.... formula help

    I am in the Air Force and my unit wants me to create a spread sheet with pushups situps and runtime for our pt test. We will be doing a mock test every month to see progress of our members. I need to show improvment or not in the spreadsheet. Such as if in Jan I do 50 pushups then Feb I do 52 I would like for that to show up green for improvement red if I got less and yellow if stayed the same. I will be doing each month on its own sheet. The only way I have found to do this is naming the prior months cell and usng conditional formating to create the three rules seperatley. This works but with over 50 members and doing this every month there seems like there has to be a quicker way. Any input would be appreciated. I don't know ton about excel but I learn quick.

    Also the run time I would need to be the opposite with green for less yellow for same red for more.

    Please ask any questions if you dont understand my ramblings.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Greater Than - Less Than - Equal to.... formula help

    You don't need named ranges, leaving them out allows you to reference "adjacent columns" instead.

    Assuming the Jan/Feb/March columns are adjacent.... you conditionally format B2:L2 like so:

    GREEN
    =AND(B2>0, B2>A2)

    RED
    =AND(B2>0, B2<A2)

    YELLOW
    =B2=A2


    Since those cell references are relative, they will adjust as you copy that cell around.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    Prattville Alabama
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Greater Than - Less Than - Equal to.... formula help

    I'm going to be putting all 50 members on the same sheet with their stats for that month. I couldn't think of another way to organize it without putting each month on a seperate sheet if that makes sense. So their stats month to month would be on a different sheet.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Greater Than - Less Than - Equal to.... formula help

    Then there is no "easy" way to do it. Conditional formatting from one sheet to another requires named ranges and that would mean creating unique formulas for each sheet.

    A true database should be one sheet. People names down the A column, Month columns. Each exercise would be a new group of rows.

  5. #5
    Registered User
    Join Date
    01-10-2012
    Location
    Prattville Alabama
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Greater Than - Less Than - Equal to.... formula help

    Then from one sheet to the next would there be a way to create a formula for all the members pushups at one time? or is the only way to conditional format each person’s individual exercise to the same persons last month?

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Greater Than - Less Than - Equal to.... formula help

    Quote Originally Posted by JBeaucaire View Post
    A true database should be one sheet. People names down the A column, Month columns. Each exercise would be a new group of rows.
    You're still in the design phase, why not switch to one sheet now before you get any further into this part?

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 01-10-2012 at 07:30 PM.

  7. #7
    Registered User
    Join Date
    01-10-2012
    Location
    Prattville Alabama
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Greater Than - Less Than - Equal to.... formula help

    Yea I guess I will have to change the design of it. I was thinking of using a seperate sheet for each event. That way there wont be so much data on one sheet of paper and I wont have to compare data off of different sheets. If I run into problems with that I will be using the design you posted above. thanks a ton for all of your input you saved me a lot of time trying to figure out something that cant be done.

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Greater Than - Less Than - Equal to.... formula help

    If you are using one sheet per exercise, then my original suggestion will work, so this CAN be done that way.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1