Place a symbol in a cell based on value in adjacent cell that correspond to a scale in top row?

[perola.rike]

I have a lot of rows with cognitive data, range 20-80 (standardized T-scores) in column B that I want to represent visually in column C with a symbol (square or small circle) that correspont to the T-score scale in the first row (20-80).
In addition, I need vertical lines that represent the mean score (value 50), and + and - 2 standard deviations (T=30 and 70).
I have multiple rows with data, up to 50 rows.
I have a lot of missing data in some rows, so I need to hide many rows.
I also sort the rows a lot, e.g., in some cases "Attention" in row 2 below, sometimes are sorted and placed lower.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]T-score[/TD]
[TD] 20 30 40 50 60 70 80[/TD]
[/TR]
[TR]
[TD]Attention[/TD]
[TD]20[/TD]
[TD] O[/TD]
[/TR]
[TR]
[TD]Language[/TD]
[TD]20[/TD]
[TD] O[/TD]
[/TR]
</tbody>[/TABLE]

I can't believe how difficult this turned out to be!

Even though I've mangaged with formulas to visually create a table that works for me, even with vertical lines representing the mean and SDs, on other computers, with other users, the lines moves slightly and the cirles in rows 2, 3 do not always correspond to the scale in the first row. Even though zoom=100 and screen resolution are the same.

I need a universal solution for all my users across computers.

I have been looking a lot, spent too many hours on this, and found a lot of customized Excel-tables that are nice, including e.g., forest plots, but the problem is that they are not fully able to handle missing data (that often occur in my data set, I hide rowns when needed), I also sort the data based on parameters not shown in this post, so the visual representation needs to handle this as well. I havent found any that works for me, even after hours of modification.


I think that if I manage to place a circle/square in the adjacent cell that correspond to a scale in the top row, that works across all computers, It would be easy to maintain, add new rows, etc.

Any solutions are appreciated!

 

[bobsan42]

depending on you EXCEL version:
I believe you can make use of the extended conditional formatting (Home tab) capabilities: Data bars, Color scales, Icon sets
All these can be set depending on the cell value while the value itself can be hidden - dig a little deeper in the rules adjustments and you can achieve a lot.

Also the is an interesting tool to keep handy: Sparklines on the Insert tab

P.S.: Create formulas that evaluate to numbers, but when data is missing to evaluate to empty string "".

 

[pgc01]

I think that if I manage to place a circle/square in the adjacent cell that correspond to a scale in the top row, that works across all computers, It would be easy to maintain, add new rows, etc.

Hi

I don't understand. What do you mean by placing the circle?
The circle is just a character, or are you doing something special?

... the cirles in rows 2, 3 do not always correspond to the scale in the first row.

Which circle character are you using?

Can you give an example where the same character with the same font and the same size looks different in different cells?


Please clarify.?

 

[perola.rike]

Let me explain further:

In Column A, the names of cognitive tests are listed.
In column B the results from each test are listed (values from 20 to 80).
In column C, top row, there is a numeric scale visually presented like this: 20 - 30 - 40 - 50- 60 - 70 - 80
In column C, the black circle (Black Circle Unicode Character code 25CF) are to be placed relative to the numbers in the top row. One circle in each cell in the column.
The circle represents the corresponding number in Columns B. So if the value in a cell in columns B is 50, the black circle shall be placed below value 50 as represented in the top row.

 

[perola.rike]

I have spent a lot of time with conditional formatting, and sparklines, but need to check it out further.

 

[pgc01]

In column B the results from each test are listed (values from 20 to 80).
In column C, top row, there is a numeric scale visually presented like this: 20 - 30 - 40 - 50- 60 - 70 - 80
In column C, the black circle (Black Circle Unicode Character code 25CF) are to be placed relative to the numbers in the top row. One circle in each cell in the column.
The circle represents the corresponding number in Columns B. So if the value in a cell in columns B is 50, the black circle shall be placed below value 50 as represented in the top row.

This means that in C2 you have:

=IF(C$1=$B2;"●";"")

Copy down and across.

Right?

 

[bobsan42]

Now I get it I think.

Is there a reason to squeeze it all in column C only?
Why not spread it a bit and use columns C to I for numbers 20 to 80 with a step of 10.
Or even take it further and use the next 61 columns for numbers 20 to 80 with a step of 1?

How do you place the circle in C? are you using code or formula? And what font are you using?

I think I managed to simulate something.
I put all integers from 20 to 80 in col A, A2 = 20 .... A62=80
In B1 I put this: 20 30 40 50 60 70 80 (with a space between each number). Then for column B - cell formatting: horizontal alignment I set to Distributed (Indent).
Then in cell B2 i put the following formula: =REPT(UNICHAR(160),A2-20)&UNICHAR(9679)&REPT(UNICHAR(160),80-A2)

Then fill the formula down to B62. Then Autofit column B - depending on the font size you choose column width will vary. HTH.

 

[perola.rike]

Somewhat correct, yes! But what I want is the ● to be placed in cell C2 in different places that corresponds to the scale in C1 (20-30-...-80). If B2 is 20, then place the ● below 20, to the left in cell C2, if B2 is 80, then place the ● to the right in cell C2. I could have solved this with many more columns, but I'm looking for a singe column solution.

 

[bobsan42]

And an afterthought:
You could also represent scores below average different from scores above with different symbols:

=REPT(UNICHAR(160),A2-20)&IF(A2<$A$1,UNICHAR(9675),UNICHAR(9679))&REPT(UNICHAR(160),80-A2)

 

[bobsan42]

Well, I believe that's what I suggested does.
Change some of the values in the cells A2 to A62 and see how the dot moves.

I just described a way to see this visually. Let's try again:
In C1 put this: 20 30 40 50 60 70 80 (with a space between each number).
Then for column C - cell formatting: horizontal alignment set to Distributed (Indent).
Then in cell C2 put the following formula:

=REPT(UNICHAR(160),B2-20)&UNICHAR(9679)&REPT(UNICHAR(160),80-B2)

And do not forget to AutoFit column C (double click on the line between the labels of columns C and D)