Conditional Formatting based on Increase/Decrease of percentage

G

goku0650

New Member
Joined
Dec 10, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello, I am looking to highlight any cells Green that have at equal or greater of 5% increase from previous month. Then I am looking to highlight any cells Red that have a decrease of 5% of previous month. Below is a sample of using the Mini Sheet option as well as a screenshot. The Column "Charges" starts on A4. Thanks!


1639145293944.png




FY 2022 - Bill Wright.xlsx
ABCDEFGHIJKLMNOPQRST
4ChargesJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Total
54112.50-1.500.502.002.50-1.500.501.50--------12.50
6411 LIST-------------------
7ACT-800TFN-------------------
8ACT-DID4.0012.0021.005.502.508.508.7512.258.0020.25--------102.75
9ACT-DID800-------------------
10ACT-DIDINTL-------------------
11CHARGEBACK FEE-------------------
12CNAM -------------------
13CNAM - PER CALL-------------------
14CNAM-PER TN624.29631.86640.50633.89617.30490.08494.98485.35486.43485.23--------5,589.91
15CPAAS TERM -------------------
Summary By Customer
Cell Formulas
RangeFormula
B5:S15B5=SUMIFS(Summary!G:G,Summary!$C:$C,'Summary By Customer'!$A$2,Summary!$E:$E,'Summary By Customer'!$A$5:$A$119)
T5:T15T5=SUM(Table5[@[Jan-21]:[Jun-22]])
Named Ranges
NameRefers ToCells
_1_POINT_COMMUNICATIONS_9512='Summary By Customer'!$A$2B5:S15
Customers='Summary By Customer'!$A$2B5:S15
 
Sort by date Sort by votes
On another note, these amounts are calculated based on a name from a drop down list. So these cells will change based on the customer's name.
 
Upvote 0
Welcome to the MrExcel board!

It is not clear just how you want to treat the cell if it, or the previous cell, is zero. However, see if this gets you started.

goku0650.xlsm
ABCDEFGHIJKLMNOPQRST
4ChargesJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Total
54112.501.50.522.501.50.51.50000000012.5
6411 LIST0000000000000000000
7ACT-800TFN0000000000000000000
8ACT-DID412215.52.58.58.7512.25820.2500000000102.75
9ACT-DID8000000000000000000000
10ACT-DIDINTL0000000000000000000
11CHARGEBACK FEE0000000000000000000
12CNAM 0000000000000000000
13CNAM - PER CALL0000000000000000000
14CNAM-PER TN624.29631.86640.5633.89617.3490.08494.98485.35486.43485.23000000005,589.91
15CPAAS TERM 0000000000000000000
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5:S15Expression=AND(B5<>0,C5<=0.95*B5)textNO
C5:S15Expression=AND(C5<>0,C5>=1.05*B5)textNO
 
Upvote 0
Solution
@Peter_SSs, I was wondering if you can help me again with the above. So instead of just having Red/Green, I was asked to provide the following colors based on percentage ranges. Thanks!

Revenue changesColors
no changesBlue
increase 1-9%Yellow
increase 10% - 15%Light Green
increase 15%+Green
decrease 1-9%Pink
decrease 10% - 15%Red
decrease 15%+Orange
 
Upvote 0
Like this?

goku0650.xlsm
ABCDEFGHIJKLMNOPQRST
4ChargesJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22May-22Jun-22Total
54112.501.50.522.2101.50.51.50000000012.5
6411 LIST0000000000000000000
7ACT-800TFN0000000000000000000
8ACT-DID412215.52.58.58.7512.25820.2500000000102.75
9ACT-DID8000000000000000000000
10ACT-DIDINTL0005554.4000000000000
11CHARGEBACK FEE0000000000000000000
12CNAM 0000000000000000000
13CNAM - PER CALL0000000000000000000
14CNAM-PER TN624.29631.86640.5633.89617.3490.08494.98485.35486.43485.23000000005,589.91
15CPAAS TERM 0000000000000000000
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C5:S15Expression=AND(C5<>0,C5=B5)textNO
C5:S15Expression=AND(C5<>0,C5>B5,C5<1.1*B5)textNO
C5:S15Expression=AND(C5<>0,C5>=1.1*B5,C5<1.15*B5)textNO
C5:S15Expression=AND(C5<>0,C5>=1.15*B5)textNO
C5:S15Expression=AND(C5<>0,C5<B5,C5>0.9*B5)textNO
C5:S15Expression=AND(C5<>0,C5<=0.9*B5,C5>0.85*B5)textNO
C5:S15Expression=AND(C5<>0,C5<=0.85*B5)textNO
 
Upvote 0
Hello @Peter_SSs, yes thank you so much as I wasn't sure about the in between percentages. I greatly appreciate your help with this. :-)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Cell color based on increasing or decreasing values in a column.
Replies
1
Views
9K
AndyCoder
A
SeanWRX
  • Question Question
A bit stuck with conditional formatting based on figure in cell above
Replies
6
Views
2K
SeanWRX
SeanWRX
J
  • Question Question
Percentage increase or decrease on comparision to same time last year.
Replies
0
Views
663
JamesCES
J
H
  • Question Question
Calculating Increase/Decrease
Replies
4
Views
3K
howard
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top