Condition format

[Heera]

Hello Team,

I need an up and down arrow sign which should show the result. If it is above that it should give green, if it is below than red. Below is the sample data. Please fill the formulae in the empty box.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Sign[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128, colspan: 2"]Hours Required/Day[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]774:33:32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 128, colspan: 2"]Hours Achieved/Day[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]595:34:47[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks & Regards,
Heera

 

[JustynaMK]

Hi Heera,

This can be achieved with Conditional Formatting. Assuming that your data is presented as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sign[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hours Required/Day[/TD]
[TD]774:33:32[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hours Achieved/Day[/TD]
[TD]595:34:47[/TD]
[/TR]
</tbody>[/TABLE]

Click on cell B1 and insert the following formula:
=B3/B2
The formula will show a result of >1 if the number of "hours achieved" exceeds the number of "hours required". In our case, the result is 0.76 as the goal has not been achieved, so we would expect to see a red arrow.

While still having cell B1 selected, do the following:

• go to Excel > Home > Conditional Formatting > New Rule... > Format all cells based on their values (first selection);
• change the "Format Style" to "Icon Sets" and change "Icon Style" to arrows (red, yellow, green);
• click on a yellow arrow and select "No cell icon";
• move to two formula bars on the right and for both of them set ">=", 1, Number (Value & Type);
• also, check the box which says "Show icon only";
• click on OK - cell B1 should now display a red arrow;

I hope it helps.

 

[Heera]

Thank you so much. Its really works.

Regards,
Heera

 

[JustynaMK]

No problem at all Heera, I'm glad it works.