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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.