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
2	Base Cost:	$ 10	$ 15	$ 12
3	Cost 1:	$ 7	$ 11	$ 11
4	Cost 2:	$ 11	$ 20	$ 13
5	Cost 3:	$ 13	$ 5	$ 16

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A2 Base Cost: B2 10 C2 15 D2 12 A3 Cost 1: B3 7 C3 11 D3 11 A4 Cost 2: B4 11 C4 20 D4 13 A5 Cost 3: B5 13 C5 5 D5 16 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Allienne]

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

UNIT COST 1	UNIT COST 2	BASE UNIT COST
$10.00	$20.00	$15.00

<colgroup><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>
</tbody>

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