Help a newb....

[gibson00]

Sorry if this is the wrong forum, wasn't sure which would be best. I a
trying to complete a spreadsheet for work, and am a complete exce
rookie. I'd -really- appreciate some help. I'm sure these are basi
questions for you guys/gals:

We are tracking some metrics in a spreadsheet. In the cells where
have the scores, I would like to:
1. Next to the actual score, have the percentage value of the scor
show as well. So if their score was 10 out of 20 (the 20 value is in
cell above as a numeric title, so can reference that), I want '50%' t
show next to the 10 value. Maybe I need to create an extra column nex
to the scores? Or can it be shown in the same cell?

2. If the score is below a certain value percentage, like 50, I'd lik
the cell to highlight red.

Thanks!!!!!!!!!!!!!!!!!!!

 

[MartinShort]

I'm going to make some assumptions here.

A1=10
B1=20

So to get the percentage, in C1 type "=A1/B1" and then click the
button on formatting toolbar at the top of the page.

To get the cell to change colour, have a look a
http://www.excelforum.com/showthread.php?t=56016

 

[gibson00]

I'm going to make some assumptions here.

A1=10
B1=20

So to get the percentage, in C1 type "=A1/B1" and then click the
button on formatting toolbar at the top of the page.

To get the cell to change colour, have a look a
http://www.excelforum.com/showthread.php?t=560169

Thanks for the reply.
I do know how to calculate the percent, but was wondering if there wa
a way to show both the actual score, and the percent, in the sam
cell.
So if the metric/test were out of 20, and they scored a 10, I'd wan
the cell to read:
10 (50%)

Is that possible?

Thanks

 

[MartinShort]

Hmmm. Yes there is. Continuing from before, in D1 type


Code

 

[gibson00]

When I use the conditional formatting for the color change, I get th
drop down boxes, and choose:
"Cell Value Is"
"Less Than"

And then for the formula, I try D4/2. I also tried $D$4/2.
I'm basically trying to say if less than 50%. Neither works, it alway
comes back as true even if its not.

To clarify, all of row '4' contains the possible scores for each task.
Then going down the chart (rows 5 onward), you get the individua
scores for each task. Like:

Column D E F G H

Row
4 20 15 20 15 20

5 12 10 8 7 17
6 8 13 14 12 7
7 16 12 13 11

 

[gibson00]

OK, I can get the highlighting to work for a single cell, and possibly
single column, but I can't seem to highlight all the scores and inser
one formula to handle them all, ASSUMING that all the scores are no
out of 20, so they each need to reference their own 'out of
score....:

 

[MartinShort]

Hi sorry for the delay - lunch proved even more important!

OK - the answer lies in use of the formula part of conditional
formatting.

So your columns now look like this:

A1 = *9*
B1 = *20*
C1 = *A1/B1*
D1 = *=A1&" ("&C1&"%)"*

Open up the Conditional Formatting dialogue box for D1 and change Cell
Value to Formula. In the Formula condition type

=IF(C1<50,TRUE,FALSE)
Now select Red as the conditional format.

Copy this formula down your entire column (including formatting) and
hide column C as before.

HTH

Martin