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

**dhue0715** · 01-10-2012, 05:28 PM

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.

**JBeaucaire** · 01-10-2012, 05:37 PM

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.

**dhue0715** · 01-10-2012, 05:47 PM

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.

**JBeaucaire** · 01-10-2012, 05:56 PM

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.

**dhue0715** · 01-10-2012, 06:02 PM

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?

**JBeaucaire** · 01-10-2012, 06:25 PM

Originally Posted by JBeaucaire

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

**dhue0715** · 01-10-2012, 06:43 PM

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.