Percentile Gradients Issue

[Allienne]

Hello!

I need to be able to add a gradient if one fee is 10% higher to 30% higher than the base cost or 10% lower to 30% lower than the base cost.

Example: I have a base cost of $15. Different comparison costs are $10 and $20. I need to be able to add a gradient color depending on the percentage difference between the two costs and the base cost. So since $10 is more than 30% lower than $15, it would be dark green. The $20 comparison is more than 30% high and would be bright red. Any help would be appreciated.

Maybe I could use an IF statement?

 

[Jeffrey Mahoney]

You're talking about using Conditional formatting?? Since you didn't provide a table, I created an example. Highlight the range B3:D5. Add these two conditional formatting formulas:

for the costs less than 30% of base
=(B3/B$2)<0.7000001


For the costs greater than 30%
=B3/B$2>1.29999

I formatted the cells with a gradient green and a gradient red respectively.

Unknown

	A	B	C	D
Base Cost:
Cost 1:
Cost 2:
Cost 3:

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"] $ 10 [/TD]
[TD="align: right"] $ 15 [/TD]
[TD="align: right"] $ 12 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"] $ 7 [/TD]
[TD="align: right"] $ 11 [/TD]
[TD="align: right"] $ 11 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"] $ 11 [/TD]
[TD="align: right"] $ 20 [/TD]
[TD="align: right"] $ 13 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"] $ 13 [/TD]
[TD="align: right"] $ 5 [/TD]
[TD="align: right"] $ 16 [/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]Base Cost:[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]15[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]Cost 1:[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]Cost 2:[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]20[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A5[/TH]
[TD="align: left"]Cost 3:[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]13[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Allienne]

I'm not all that familiar with conditional formatting. I'll post an example.

[TABLE="width: 550"]
<colgroup><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]UNIT COST 1
[/TD]
[TD]UNIT COST 2
[/TD]
[TD]BASE UNIT COST
[/TD]
[/TR]
[TR]
[TD]$10.00
[/TD]
[TD]$20.00
[/TD]
[TD]$15.00
[/TD]

[/TR]
</tbody>[/TABLE]

So as you can see, I need to put a conditional formatting if it is 10% higher the color is pink, 20% higher the color is between pink and red, and 30% higher is bright red. Same with green for lower pricing. What I would like is something that can determine if Unit Cost 1 is 10%, 20%, or 30+% higher/lower, show a certain color.

 

[Jeffrey Mahoney]

Here are some rules to Conditional Formatting (CF) formulas:

* Always reference the top left cell you want to affect
* Using Anchors ($ dollar signs) to lock rows and columns is very important
* The formula must always result in a TRUE or FALSE

Assuming that your first unit cost is cell A2. Highlight A2:B2. Create a formula based CF as such: =(A2/$C2)>0.0999 and format it with pink
Highlight A2:B2. Create a formula based CF as such: =(A2/$C2)>0.1999 and format it with pink/red

Do the same for the other CF's

Jeff