Showing min and max values as a line inside a cell

[emma390]

Hi there,
I am hoping there is a way to do this, I feel like I've seen it done before.

I have a table which lists a number of questions. The respondents chose their answers from 5 possible selections (very little through to very much, for example).

I've given each of the answers a corresponding number and found the median answer for each question, which I have then displayed using conditional formatting data bars. So the minimum value is 1 and the maximum is 5. I'd now like to present the min/max as a line over the top of the conditional formatting in the cell (similar to the way a sparkline would work, but a horizontal line that looks something like this |----------| and is positioned within the cell at the min and max points).

Hope this makes sense. Any solution would be very much appreciated!

Thanks in advance.
E

[hemesh]

@ emma! Hi and welcome to the forum could you upload a sample book

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[emma390]

Hi Hemesh - hopefully this gives you an idea of what I am after... I could manually put all of those in using shapes, but the actual survey has over 60 questions and I'd really love to not have to do that!
Thanks!

[FlameRetired]

Is something like in the attached something you can build on? The formula in B12 and filled down

Please Login or Register  to view this content.

[emma390]

Not quite what I need. Depending on the size of the column and text/content alignment it throws the positioning of the line out. But that's the closest I've come without doing them all manually...

[FlameRetired]

Let me sleep on it. Maybe something will take shape.

[emma390]

Thank you! Much appreciated!

I've been working on it all day :/

[hemesh]

may be in b2
copy paste below
=REPT(" ",[@Min]-1)&"|"&REPT("--",SUM([@Max]+[@Min]))&"|"

[emma390]

Not quite - lines aren't quite sitting in the right place and still dependant on sizing of the column.

Thank you both for your help - might just do them manually for now!

[FlameRetired]

emma390,

Here's another approach. This uses a lookup table and this array entered formula

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

I used the MS Gothic 12 font for this. You may want to experiment with fonts until you get the "Goldie Locks" combination.

The formula above references a hand typed lookup table and data. I reasoned that there are only so many combinations of lower and upper boundaries. The rest could be filled by formula.

Row\Col	A	B	C	D	E	F	G	H
1		Sample ouput	Lower boundaries	Upper boundaries		Lookup Table
2	Question 1	[--------]	1	4		Mins	Maxes	Line Charts
3	Question 2	[--------]	2	5		1	1	[--]
4	Question 3	[----]	3	4		1	2	[----]
5	Question 4	[----------]	1	5		1	3	[------]
6	Question 5	[------]	3	5		1	4	[--------]
7	Question 6	[------]	2	4		1	5	[----------]
8	Question 7	[----]	1	2
9	Question 8	[----]	2	3
10	Question 9	[------]	1	3

[FlameRetired]

emma390,

Here's another approach. This uses a lookup table and this array entered formula

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulae array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

I used the MS Gothic 12 font for this. You may want to experiment with fonts until you get the "Goldie Locks" combination.

The formula above references a hand typed lookup table and data. I reasoned that there are only so many combinations of lower and upper boundaries. The rest could be filled by formula.

Edit The header add-in that I use didn't retain the spacing in the below grid. I'll post the WB I did it in. Hope this helps.

Row\Col	A	B	C	D	E	F	G	H
1		Sample ouput	Lower boundaries	Upper boundaries		Lookup Table
2	Question 1	[--------]	1	4		Mins	Maxes	Line Charts
3	Question 2	[--------]	2	5		1	1	[--]
4	Question 3	[----]	3	4		1	2	[----]
5	Question 4	[----------]	1	5		1	3	[------]
6	Question 5	[------]	3	5		1	4	[--------]
7	Question 6	[------]	2	4		1	5	[----------]
8	Question 7	[----]	1	2
9	Question 8	[----]	2	3
10	Question 9	[------]	1	3