Excel help

[atceagle]

Here's my situation..I'm working with Office 2010. I provide sports med coverage for a high school that will be beginning two-a-day practices on Monday. I will be weighing the players before and after every practice looking for those kids that are not hydrating themselves between practices and trying to prevent heatstroke. I will be handwriting their weights down but will input them into my spreadsheet after every practice.

I want their post practice weight cell to change colors depending of the % of weight loss from their pre-practice weight, where as less than 2% weight loss, the cell turns green; more than 2% but less than 5%, the cell turns yellow; more than 5%, the cell turns red.

I have researched a little and read about conditional formatting but it was over my head. Any help is greatly appreciated.

[Denzien]

Conditional Formatting is what you want; I use this all the time. Select the cell(s) and click Home\Conditional Formatting\Highlight Cells Rules and then Greater than, Less than or between.

Do this once for each rule as you've already defined them -
Less than 2% - Green
Between 2% and 5% - Yellow - You don't need this rule, just format the cells to be Yellow by default
Greater than 5% - Red

...and that's it.

Let me know if you can't get this, I suck at explaining things.

[atceagle]

so am I able to do all 3 rules per cell?

[Denzien]

Yes. If the rules conflict, you can order them by managing the rules on that cell.

Make sure that the values 2 and 5 are accounted for.

Edit: actually, there's no reason to setup a rule for the between case - you should just make the cells yellow, which will be overridden by the other two rules when appropriate.

Also note that you can define the formatting rule to affect a range of cells, so you only make it once.

[atceagle]

ok,,another problem. I have the conditional formatting worked out but when I go to paint the format down the sheet, I'm having issues. The reference cell is not changing. Example,,,The person's name is in A4, the am pre-practice weight is B4, am post is C4, pm pre is D4, and pm post is E4. When I go to paint the conditional format from C4 to C5, it keeps referencing B4 when it should reference B5. Suggestions?

[Surviving Saint]

Originally Posted by atceagle

ok,,another problem. I have the conditional formatting worked out but when I go to paint the format down the sheet, I'm having issues. The reference cell is not changing. Example,,,The person's name is in A4, the am pre-practice weight is B4, am post is C4, pm pre is D4, and pm post is E4. When I go to paint the conditional format from C4 to C5, it keeps referencing B4 when it should reference B5. Suggestions?

Is there a dollar sign in the reference cell? If so remove it. Something tells me that's not the problem though. When you format make sure you select the entire group of cells that you want the color to change in.

[atceagle]

Originally Posted by Surviving Saint

Is there a dollar sign in the reference cell? If so remove it. Something tells me that's not the problem though. When you format make sure you select the entire group of cells that you want the color to change in.

Yes, I did remove the $ but it didn't make a difference. Will try the other suggestion.

[Surviving Saint]

Originally Posted by atceagle

Yes, I did remove the $ but it didn't make a difference. Will try the other suggestion.

If there was a dollar sign or signs reformat it and make sure they arent there before hitting ok. You need to select all the cells though when formatting. I don't have my computer with me at work but it should work as Denzien instructed. I have a spreadsheet that uses it similarly and it was fairly easy to do. I would build it for you but I don't have a pc handy.

[atceagle]

holy crap, I never knew I could be this incompetent.....frustrated.

[Denzien]

Originally Posted by atceagle

ok,,another problem. I have the conditional formatting worked out but when I go to paint the format down the sheet, I'm having issues. The reference cell is not changing. Example,,,The person's name is in A4, the am pre-practice weight is B4, am post is C4, pm pre is D4, and pm post is E4. When I go to paint the conditional format from C4 to C5, it keeps referencing B4 when it should reference B5. Suggestions?

Okay, so you are not calculating the % difference in a new column? It might be easier if you do this, otherwise you need to actually use formulas for the Conditional Highlighting.

In fact, a lot of times I calculate values in hidden columns to use for other calculations or for highlighting. I don't know if this is a "best practice" for Excel though - just the way I think as a programmer.

I could probably help better if I had a sanitized copy of your spreadsheet.

[BHM]

Just use these and be done with it.

[atceagle]

Thanks to Denzien, hopefully there will be a few less kids on the MS Gulf Coast suffering from any heat illnesses as they battle through two-a-days. The colored boxes give them instant feedback on their hydration status and really drives the point home.