PDA

View Full Version : [SOLVED] Rookie Question: Three Columns - Total, Sub-Total, Calculate Percentage...?



VB-AN-IZ
10-21-2016, 03:42 AM
I'm convinced this would be really simple to arrange, but I've been wrong before... If anybody can help out, it'd be much appreciated.

I basically want to enter text into Column A and B, then have column C formulate and format the result:

Column A = total
Column B = sub-total
Column C = A minus B, divided by A, multiplied by 100, rounded to one decimal point; also (if possible) cell colored green if 99%+; yellow if 98%+, or red if below 98%

Apologies if I'm asking the wrong place!

Paul_Hossler
10-21-2016, 06:10 AM
I think an example of the before and after would help

If you enter text (e.g. ABC, DEF, cat, dog, etc.) in A and B, then there is no Total

VB-AN-IZ
10-21-2016, 06:41 AM
Ah. I guess I was working on the assumption that only numbers (and commas and full stops, I guess) would be recognized in any of these columns. And I would only have to enter the numbers into columns A and B.

Example:

A = 2,345
B = 67
C = 97.1%

So C would automatically calculate and format:

A minus B: 2,345 - 67 = 2,278
Divided by A: 2,278 ÷ 2,345 = 0.9714285714285714
Multiplied by 100, rounded to one decimal point: 0.9714285714285714 x 100 = 97.1%

Paul_Hossler
10-21-2016, 04:39 PM
Why not just use a worksheet formula and some number formatting?

17389

VB-AN-IZ
01-27-2017, 12:54 AM
Why not just use a worksheet formula and some number formatting?

17389

(Firstly, apologies for a long delay...)

Thank you! Have implemented your formula:



=ROUND((A1-B1)/A1, 3)


...and have formatted the resulting cell to a percentage displaying one decimal place.


(I didn't even know how to do THAT...)


Would I need to use a macro to have the columns/cells displaying the percentage to be filled with a certain color based on the number? Or can I just use a formatting option for that as well?

Paul_Hossler
01-27-2017, 07:16 AM
Two ways

1. Custom Number Formats - example is positive / zero / negative = green font - yellow font - red font

2. Conditional formatting which give you more choices - I used simplest

VB-AN-IZ
01-28-2017, 11:50 PM
Hmm. What about coloring the cell itself, rather than its text?

I'm trying to color any cells containing 99%+ green, 98%-99% to be colored yellow and below 98% to be colored red...

18179

SamT
01-29-2017, 08:27 AM
See Right side of his screenshot.

Paul_Hossler
01-29-2017, 09:04 AM
Hmm. What about coloring the cell itself, rather than its text?

I'm trying to color any cells containing 99%+ green, 98%-99% to be colored yellow and below 98% to be colored red...



Here's a more detailed version of my first picture that better shows conditional formatting (aka 'CF')

18181

I've always found CF a little hard to get to do what I want it to do, but in this case it should be pretty straight forward

VB-AN-IZ
01-30-2017, 01:02 AM
Thank you! Understood and have it working now. Much appreciated.