+ Reply to Thread
Results 1 to 8 of 8

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

Hybrid View

  1. 01-10-2012, 06:28 PM #1
    dhue0715
    dhue0715 is offline
    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.
    Reply With QuoteRegister To Reply
  2. 01-10-2012, 06:37 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert 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!)
    Reply With QuoteRegister To Reply
  3. 01-10-2012, 06:47 PM #3
    dhue0715
    dhue0715 is offline
    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.
    Reply With QuoteRegister To Reply
  4. 01-10-2012, 06:56 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  5. 01-10-2012, 07:25 PM #5
    JBeaucaire
    JBeaucaire is offline
    Forum Expert 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?

       A             B       C       D       E       F       G
		Jan	Feb	Mar	Apr	May	Jun   
PUSHUPS     |         |       |      |       |       |       |
Joe         |         |       |      |       |       |       |
Bob         |         |       |      |       |       |       |
Sam         |         |       |      |       |       |       |
            |         |       |      |       |       |       |
PULLUPS     |         |       |      |       |       |       |
Joe         |         |       |      |       |       |       |
Bob         |         |       |      |       |       |       |
Sam         |         |       |      |       |       |       |
            |         |       |      |       |       |       |
SITUPS      |         |       |      |       |       |       |
Joe         |         |       |      |       |       |       |
Bob         |         |       |      |       |       |       |
Sam         |         |       |      |       |       |       |
    Last edited by JBeaucaire; 01-10-2012 at 07:30 PM.
    Reply With QuoteRegister To Reply
  6. 01-10-2012, 07:02 PM #6
    dhue0715
    dhue0715 is offline
    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?
    Reply With QuoteRegister To Reply
  7. 01-10-2012, 07:43 PM #7
    dhue0715
    dhue0715 is offline
    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.
    Reply With QuoteRegister To Reply
  8. 01-10-2012, 08:28 PM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert 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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1