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!

 

[perola.rike]

Sounds very promising, I'll give all your tips a try later today!

 

[perola.rike]

I tried your formula, to some extent it works, but the black square won't place correctly. I have tried many formulas, including formulas with REPT, and those formulas, even though I've made them work on my computer, wont work (i.e., the black cirkle are not placed correctly) on other users' computers... I have been very close to make it work with REPT formula, like yours, but still, it not place itself correctly across computers.

I have used calibri size 10. But I have experimented with a lot of fonts and sizes.

Yes, I definetly could have used a lot more rows (61) to make it work, my "old" profile is built that way, but to make it more simple, I wanted it to be only one cell.

 

[pgc01]

Hi

If you continue to have problems with the display of the relative placement of the circle, you can use bobsan's formula with a character that makes the relative position clear, like a dot:

..⏺..........................................
............................⏺................
........................................⏺....
..........⏺..................................

Even if they don't display in the same position across computers, the display will clearly show their relative position in the range.

Something like:

=REPT(".",B2-20)&"⏺"&REPT(".",80-B2)

Remark: I used the character 9210 that displays a bit bigger in my PC.

 

[perola.rike]

Thank you for the formula, it works fine in one cell. I still have a problem with a numeric scale in the top row with numbers 20, 30...80, but I inserted the number 50 (mean) in the top row columns C, and a vba code that inserted a vertical line in column C in order to visually present the deviations from the mean in C2 and downwards. In addition, I have applied conditioned formatting with color scales on the symbols (⏺), red, orange and green based on their values in columns B. So I think this is satifactory! Thank you for all help/input!

 

[bobsan42]

I still think that Conditional formatting with data bars (instead of a dot) will be perfect for your case. And guaranteed to work properly across different computers, independent of font and size.

 

[perola.rike]

I totally agree with you regarding horisontal bars in one cell with conditional formatting. The only thing is that my users really want a numeric scale in the top row (cell) as mentioned before (20-30-40.....80), and 3 vertical lines representing mean and +/- 2 standard deviations at scores 30 and 70, respectively.

I want it to look like this picture (i manually made the scare and 3 vertical lines): https://www.dropbox.com/s/ligznlz3dk69f7d/profile.PNG?dl=0

So if any have any tips how to combine data bars with a scale in the top row that do not cahnge across computers, including three vertical lines, I would really appreciate that.

I have a code that place one vertical line centered in the cell.