Calculating a % of a previous cell in table.

Question

Hi,

I need some help to write a formula. I have a table that gets composed. I need to know how to

I'd like to have the cell in blue (see attached image) change colors depending on the percentage of the preceding cell.

Example, if the count in xx is over 75% of the preceding cell, then it is green. If it is under 75% then it is yellow etc.

Obviously I will need multiple rules set up with different percentages set as the color trigger per percentage.

Currently, my formula is as below. When the cell count is less than the preceding cell, it changes the cell color to blue.

[VIN Count]>[01 Contacted]

Can anyone help?

percentage calc.jpg

Yoshiro_Fujimori · Answer

Hi @dkeever1 ,

The formula which decides the cell color is actually embedded in the Table tool.

But if you want to see the formula, it can be set as a column like below.

Workflow

Formula

IF [VINs Contacted by CRC] >= [VIN Count] * 0.75
THEN "Green"
ELSE "Yellow"
ENDIF

Output

ColoredTable.yxmd

dkeever1 · Answer

Thanks Yoshiro San,

Your solution is very close to what I need, however I need the formula to be automatic when VIN Count vs VINs Contacted is calculated. I need to be able to set up rules for if VIN contacted is 75% of VIN Count, then the cell turns green. If it is under 75% of VIN count, then it turns yellow etc.

I hope my additional query is clear.

Yoshiro_Fujimori · Answer

Hi @dkeever1 ,

If you want to output to Excel file with colored cell, you need to use Table and Render tools.

Workflow

Table tool configuration

You can add as many rules as you need.

Output image

I hope this answers to your question.

ColoredTable.yxmd