Conditional Formatting: Data vis

[Warren-1529]

Hi all, I am trying to do some conditional formatting to track performance overtime and am having some issues. Right now I have amounts per month per person in cells with simple formatting and it works how I want but I am running into an issue when it comes to the totals per person. I want to be able to color code the totals based on monthly amounts. I have increment targets per month per person and want to be able to add the cell values and give the grand total a color; good, bad, or neutral based off of these increments but want it so that while I am in for example the month of May and don’t have the data for future months, to be able to format the color of the grand total cell without the future month cells values affecting the condition of the grand total.

Not sure if I explained that well or not, but I have tried a couple of different ways to attempt this and have not been able to get it to work. I tried doing formulas in the conditional formatting such as if statements, ifs, sumifs, if(and, and a couple others. I also thought a script would work and possible be easier but I am not versed in libre basic enough to get the notation down. Any help would be appreciated!

[Hrbrgr]

IMHO you can only achieve this if you enter the target value for the calculation in each month.

[dscheikey]

I have created your table with the conditional formatting. I hope it fits now. Have a look at the conditions.

Conditional Formatting Data vis.ods (33,9 KB)

Have fun!

[Warren-1529]

That is perfect thank you! I understand the math behind it now that I see it, but could you by chance explain the average notation?

[dscheikey]

If you want to use the same criteria for the year total as for the months in order to use conditional formatting, we need the AVERAGE() function. In your screenshot, you had a zero in the cells without an entry. With the AVERAGEIF() function, I wanted to prevent the calculation from becoming incorrect if there are zeros in the cells. If the cells are only empty, you don’t need this.
It always depends on the order of the conditions, because the first condition that is true is then used.

I hope this answered your question. Otherwise, please contact me again.

dscheikey

[Hrbrgr]

AVERAGEIF function

[dscheikey]

Hi, Warren,
I’m not sure I really understood your problem. I can’t see any system behind your example. The good, bad neutral formatting seems random.
If I understand correctly, the zeros in the rows for the months after May are interfering with your calculations. You could solve this problem with the function: AVERAGEIF() or SUMIF().

=AVERAGEIF(B3:B14,">"&0,B3:B14)

or

=SUMIF(B3:B14,">"&0,B3:B14)

Is this what you were looking for?
If not, it would be helpful to better explain the defaults for the conditional formatting.

[Warren-1529]

Sorry should have specified target amounts. So the performance per month per person is below.

Jack: cell val >= 30k = good , cell val is between 15k and 30k = neutral , and cell val <= 15k = bad.
Luke: cell <= 30k = bad, cell >=50k = good, cell is between 30k and 50k = neutral.
Bob: cell <= 10k = bad, cell >= 20k = good, cell val is between 10k and 20k = neutral.

I don’t want the data in the future months to affect the current month until the data is inputted into the cell. I don’t think an average will get what I am going for, and I already tried a sumif but maybe my notation was just wrong. My goal is to get the totals per person to be tracking via color whether or not each person is on track based on those monthly goals.

Hope this clears things up a little, thanks!