Auto Complete/Auto Percentage Formulas

[AgentDirtySanchez]

Hi All,

I could use some help as I'm a basic Excel user and am in a bit over my head. What I'm asking for is so beyond me, but I know there are people on here that could do it in their sleep.

I need to enter a number under Pg1 (Column E) and have it automatically turn into a score out of 70. For example 35 + Enter Key = 35/70 in the cell, and at the same time having that score enter itself as a percentage in % (Column F). The same must be repeated for Pg2 ( /80), % and finally Total ( /150), % (Column G, H and I, J)

It would be really awesome (but not necessary) if I could get cell color changes for the percentages too!

25% and under = Red, 26% to 50% = Orange, 51% to 74% Yellow, and 75% to 100% = Green.

It looks a little jumbled in type, but I hope the attached sheet clears it up.

[shg]

You could custom format col E as 0"/70", and in col F, the formula =E2/70 formatted as Percent.

Doing this with VBA is vast overkill.

[AgentDirtySanchez]

What about the colours? Is that possible?

[shg]

Did you mean to say, "Thank you for that, it works fine. Is it also possible to do the colors?".

See http://www.contextures.com/xlcondFormat01.html

[AgentDirtySanchez]

Sorry, I'm trying to get this formula to take properly. I greatly appreciate the help.

[AgentDirtySanchez]

Yes that's exactly what I'm looking for with the colours. Thank you for all of your help. I'm determined to teach myself all of this, but the current deadline I'm facing does not allow for it. It's good there are people like you out there.

[AgentDirtySanchez]

I'm still having trouble with the =E2/70 formula. How do I get it to apply to each row? This registry will be ongoing with potentially thousands of entries and I would like it to perform this calculation automatically once the score is entered.

[shg]

Enter it in F2, select the cell, and drag down the fill handle to copy.

[AgentDirtySanchez]

Thank you Sir.

[AgentDirtySanchez]

Or Ma'am. I shouldn't make assumptions.