Conditional Formatting using Percentages.

T

twoltal

New Member
Joined
Mar 6, 2014
Messages
2
Hello all,

I am having some problems trying to write the correct formula in conditional formatting. I would like to be able to get a result of a value that is greater or less than 15% For example cells A1 A2 and A3 all have a value of 100. cell B1 has a value of 78, B2 has a value of 109 and B3 has a value of 154. What I would like to know is if the value of cells B1,B2 and B3 are within 15% over, 15% under or within the 15% range

A B
1 100 78
2 100 109
3 100 154

In the above case B1 will show as outside the 15% range (under) cell B2 will show as within and cell B3 would show as outside the 15% range (over). Is this possible? Basically column B returning a +/- 15%.

I hope I am explaining this clearly and someone can offer some help. Thank you.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
To check to see if it is outside of 15%, you can use this formula in Conditional Formatting:
Code: 
=ABS((B1-A1)/B1)>15%
Obviously just switch the > sign to < or <= for your other condition, if you want to format that as well.
 
Upvote 0
<html><head><title>Excel Jeanie HTML</title></head><body>
<!-- ######### Start Created Html Code To Copy ########## -->
Excel Workbook
ABCDEF
110078UNDER85percent
2100109IN RANGE0.15
3100154OVER
4100130OVER
510054UNDER
610067UNDER
7100115IN RANGE
810085IN RANGE
910084UNDER
Sheet1

?
?
<!-- ######### End Created Html Code To Copy ########## -->
</body></html>

 
Upvote 0
marious,
That sure is a lot of unnecessary work. You can do all that with just one simple formula, like I posted.
Also, they were looking for a formula to use within Conditional Formatting, not something to return to cells on the worksheet.
 
Upvote 0
Joe4 said:
marious,
That sure is a lot of unnecessary work. You can do all that with just one simple formula, like I posted.
Also, they were looking for a formula to use within Conditional Formatting, not something to return to cells on the worksheet.
Click to expand...
I agree, your formula is better. I just saw your post when I finish mine. Not to much coffee in my system yet. He can easily do conditional format wit mine too.
Jeani doesn't show the real result but he can select the range in column D go to conditional formatting and then Icon Sets and choose ne with 3 options


<title>Excel Jeanie HTML</title>
<!-- ######### Start Created Html Code To Copy ########## -->
Sheet1

*ABCDEF
UNDER*
IN RANGE*
OVER**
OVER**
UNDER**
UNDER**
IN RANGE**
IN RANGE**
UNDER**

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]78[/TD]

[TD="align: right"]-1[/TD]

[TD="align: left"]percent[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]109[/TD]

[TD="align: right"]0[/TD]

[TD="align: left"]0.15[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]154[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]130[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]54[/TD]

[TD="align: right"]-1[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]67[/TD]

[TD="align: right"]-1[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]115[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]85[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]84[/TD]

[TD="align: right"]-1[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C1=IF((A1-A1*$F$2)>B1,"UNDER",IF(AND((A1-A1*$F$2)<=B1,(A1+A1*$F$2)>=B1),"IN RANGE",IF((A1+A1*$F$2)<=B1,"OVER")))
D1=IF((A1-A1*$F$2)>B1,TRUE*-1,IF(AND((A1-A1*$F$2)<=B1,(A1+A1*$F$2)>=B1),FALSE*1,IF((A1+A1*$F$2)<=B1,TRUE*1)))
C2=IF((A2-A2*$F$2)>B2,"UNDER",IF(AND((A2-A2*$F$2)<=B2,(A2+A2*$F$2)>=B2),"IN RANGE",IF((A2+A2*$F$2)<=B2,"OVER")))
D2=IF((A2-A2*$F$2)>B2,TRUE*-1,IF(AND((A2-A2*$F$2)<=B2,(A2+A2*$F$2)>=B2),FALSE*1,IF((A2+A2*$F$2)<=B2,TRUE*1)))
C3=IF((A3-A3*$F$2)>B3,"UNDER",IF(AND((A3-A3*$F$2)<=B3,(A3+A3*$F$2)>=B3),"IN RANGE",IF((A3+A3*$F$2)<=B3,"OVER")))
D3=IF((A3-A3*$F$2)>B3,TRUE*-1,IF(AND((A3-A3*$F$2)<=B3,(A3+A3*$F$2)>=B3),FALSE*1,IF((A3+A3*$F$2)<=B3,TRUE*1)))
C4=IF((A4-A4*$F$2)>B4,"UNDER",IF(AND((A4-A4*$F$2)<=B4,(A4+A4*$F$2)>=B4),"IN RANGE",IF((A4+A4*$F$2)<=B4,"OVER")))
D4=IF((A4-A4*$F$2)>B4,TRUE*-1,IF(AND((A4-A4*$F$2)<=B4,(A4+A4*$F$2)>=B4),FALSE*1,IF((A4+A4*$F$2)<=B4,TRUE*1)))
C5=IF((A5-A5*$F$2)>B5,"UNDER",IF(AND((A5-A5*$F$2)<=B5,(A5+A5*$F$2)>=B5),"IN RANGE",IF((A5+A5*$F$2)<=B5,"OVER")))
D5=IF((A5-A5*$F$2)>B5,TRUE*-1,IF(AND((A5-A5*$F$2)<=B5,(A5+A5*$F$2)>=B5),FALSE*1,IF((A5+A5*$F$2)<=B5,TRUE*1)))
C6=IF((A6-A6*$F$2)>B6,"UNDER",IF(AND((A6-A6*$F$2)<=B6,(A6+A6*$F$2)>=B6),"IN RANGE",IF((A6+A6*$F$2)<=B6,"OVER")))
D6=IF((A6-A6*$F$2)>B6,TRUE*-1,IF(AND((A6-A6*$F$2)<=B6,(A6+A6*$F$2)>=B6),FALSE*1,IF((A6+A6*$F$2)<=B6,TRUE*1)))
C7=IF((A7-A7*$F$2)>B7,"UNDER",IF(AND((A7-A7*$F$2)<=B7,(A7+A7*$F$2)>=B7),"IN RANGE",IF((A7+A7*$F$2)<=B7,"OVER")))
D7=IF((A7-A7*$F$2)>B7,TRUE*-1,IF(AND((A7-A7*$F$2)<=B7,(A7+A7*$F$2)>=B7),FALSE*1,IF((A7+A7*$F$2)<=B7,TRUE*1)))
C8=IF((A8-A8*$F$2)>B8,"UNDER",IF(AND((A8-A8*$F$2)<=B8,(A8+A8*$F$2)>=B8),"IN RANGE",IF((A8+A8*$F$2)<=B8,"OVER")))
D8=IF((A8-A8*$F$2)>B8,TRUE*-1,IF(AND((A8-A8*$F$2)<=B8,(A8+A8*$F$2)>=B8),FALSE*1,IF((A8+A8*$F$2)<=B8,TRUE*1)))
C9=IF((A9-A9*$F$2)>B9,"UNDER",IF(AND((A9-A9*$F$2)<=B9,(A9+A9*$F$2)>=B9),"IN RANGE",IF((A9+A9*$F$2)<=B9,"OVER")))
D9=IF((A9-A9*$F$2)>B9,TRUE*-1,IF(AND((A9-A9*$F$2)<=B9,(A9+A9*$F$2)>=B9),FALSE*1,IF((A9+A9*$F$2)<=B9,TRUE*1)))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
　
　
<!-- ######### End Created Html Code To Copy ########## -->


 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Negative times and conditional formating.
Replies
2
Views
161
Ludwig
L
O
  • Solved
How show and use percentage (%) into textboxs on userform
Replies
4
Views
103
Omar M
O
A
  • Solved
How can I search inside a range by using the Conditional Formatting to search a value by using the cell's letter and number instead of a value?
2
Replies
10
Views
347
arthurz11
A
Cliffork
  • Solved
Conditional Formatting Problem
Replies
8
Views
138
DanteAmor
DanteAmor
C
  • Solved
Variable number formatting formula
Replies
7
Views
223
Joe4
Joe4

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

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