Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to work some trickery in excel where each cell in a row is shaded based on how many have been completed:

Example table


So the idea is that as more of the columns in the row are ticked the whole row (or at least the task column cells) go from red towards green.

I'm trying to use the three-colour-scale Colour Scales conditional formatting in Excel 2013 but I'm not having much luck. I started by creating a helper column that returned the amount of completed tasks and setting the formatting to look at that, but apparently this function doesn't accept indirect references.

I thought I would be able to use the Formula type:

Formula type

But neither of these formula work:

=MIN(COUNTA($B2:$G2))
=(COUNTA($B2:$G2))/2
=MAX(COUNTA($B2:$G2))


=(COUNTA($B2:$G2)=0
=(COUNTA($B2:$G2)=3
=(COUNTA($B2:$G2)=6


It's pretty clear to me that I'm misunderstanding the fundamental use of the Formula type, but I'm having a huge amount of difficulty finding any clear information on it via our old friend Google.

So my question is; what is the correct use of the Formula type, where am I going wrong and how can achieve the effect mentioned above?


UPDATE
So I've managed to solve the specific task I was attempting:

To get the ticks I used the font Marlett and set the cell value to a.

Instead of entering the letter a (this is part of a small VBA function I wrote that ticks and unticks cells when you double click on them) I added a small function to the script that added a number to the cell instead, then set the custom number format to "a".

The number references the column number of the active cell minus 1 giving me the correct number for the cell so that I can then set the conditional formatting on numbers with 1 at the bottom and 6 at the top, giving me a nice growing success gradient as more cells are ticked!

enter image description here enter image description here


Sadly this still doesn't really answer my question, as I still don't understand the proper use of the Formula type in the conditional formatting settings.

share|improve this question
    
Did they change the use of relative references in conditional formatting in Excel 2013? They haven't been allowed prior to 2013. This may be useful to you –  Raystafarian Mar 10 at 12:58
    
I honestly don't know, that may be the issue then? –  CLockeWork Mar 10 at 13:10
    
I guess then the question is what other purpose is there for Formula if you can't do relative references? –  CLockeWork Mar 10 at 13:24

2 Answers 2

If my understanding of your task is correct, it cannot be done using the 3-color scale with formulas only. You need to use relative references, which are not acceptable in formulas of the 2/3-color scale and icons set.

There are two possible solutions:

  1. The one you used.

  2. If the 1st tick in each rows should always be red, 2nd orange and so on, then you can create 6 rules of the "Use formula to determine…" type and choose 6 different colors using the following formulas:

=AND( B2<>"", COUNTA($B2:B2)=1)=true

=AND( B2<>"", COUNTA($B2:B2)=2)=true

=AND( B2<>"", COUNTA($B2:B2)=3)=true

=AND( B2<>"", COUNTA($B2:B2)=4)=true

=AND( B2<>"", COUNTA($B2:B2)=5)=true

=AND( B2<>"", COUNTA($B2:B2)=6)=true

The result will look similar to this:

enter image description here

share|improve this answer
    
Thanks Alex, as you said, the one I went with works well, but it also uses VBA which isn't great for everyone. Your solution is the best (albeit most legywork-y) one. Good to know my relative references were the problem. Still, can you confirm what the correct use of the Formula type is? –  CLockeWork Mar 13 at 9:34

use an dedicated column for this (more easy to handle than directly on whole cell impacted) put this formula inside

=COUNTA(B1:D1)/(COUNTA(B1:D1)+COUNTBLANK(B1:D1))

or (if only column in the area you check)

=COUNTA(B1:D1)/COLUMNS(B1:D1)

where the cell with the formula is A1 in thios case and cells to check (B1:D1) are B1, C1 and D1 here.

The formula count the proportion of filled cell against count of them

Then put you conditionnal formating on this cell (A1) with the three color code based on fixed value of 0 for the lower and 1 for the higher.

share|improve this answer
    
Thank you for this, but sadly it doesn't answer my question. Ideally I need to know how to achive the effect mentioned in my question, and at the least to know why I cannot. –  CLockeWork Mar 10 at 12:58
    
adresse are relative but your forumula will always return the same value for the comparaison. Counta is the same as itself (min=max every time due to the 1 value to compare). so this is the formula to change and compare to fixed value (0 and 1 in my case but you could compare to a fixed value if not relmative like mine) –  NeronLeVelu Mar 10 at 16:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.