Charting in a single cell question...

[lunchblaze]

Hi all,
not sure if this is the correct section, but here goes:

i've got a printed example of a report that i need to approximate i
excel if possible.

it's a 5 column list :

name | city | question 1 | q2| q3|


basically a report of question scores by name and city.

what i need to do is to show the numeric values in the question scor
columns as it would appear if i had done a bar chart for only tha
number - if that makes sense.

ex. for suzy, in phoenix, who scored 87% on question 1,

i want there to be in the col q1/row suzy cell, instead of just "87%",
a colored bar (a la a bar chart) with "87%" at the end of the bar - th
bar needs to take up 87% of the width of the cell so that it look
proportionally larger than bob from wisconsin beneath suzy who onl
scored 57% on Q1.

i can't see how to make this tabular representation going the standa
charting wizard route and all that, but neither can i find any kind o
custom bar-graph like formatting to perform on a single cell.


any ideas?


thanks

 

[LSMark]

I think your SOL on this one, unless someone is wiser on
charting. You could leave an empty column next to each
question, make a vertical X-axis bar chart, remove all
formatting entirely and squeeze and stretch it and the row
widths and hope it fits. If there is a limited number of
questions, you could squeeze about 50 columns together and
use the conditional formatting function which will
refernce the score and shade appropriately.

 

[Jon Peltier]

This isn't possible using Excel's charting feature. But you could have
VBA draw a bar covering a percentage of a cell.

This macro draws a blue rectangle covering part of cell F2, based on the
percentage in cell E2. You could expand it to draw a rectangle next to
any cell with a qualifying value (0<x<1).

Sub Macro1()
Dim myBar As Shape
Dim myRange As Range
Dim myPct As Double
Set myRange = ActiveSheet.Range("F2")
myPct = myRange.Offset(0, -1).Value

With myRange
Set myBar = ActiveSheet.Shapes.AddShape _
(msoShapeRectangle, .Left, .Top, .Width * myPct, .Height)
End With
With myBar
.Fill.ForeColor.SchemeColor = 12 ' Blue
End With
End Sub

- Jon

 

[lunchblaze]

thanks alot for the responses.

yeah, i have a good idea that this was originally done in a more
'robust' illustrator type prog.

i'll give the vb route a try if they still want to pursue it!


thanks again.

 

[Andy Pope]

Maybe you could use the REPT function to mimic a bar.
But this formula in cell A1. B1 contains the percentage.

A1: =REPT("|",INT(B1/5))
B1: 50

The division by 5 is so the cell contains a maximum of 20 characters.

Also you may want to use the CharacterMap program to choose an
alternative character to create a bar. Try U+2588: Full Block. from the
arial font.

thanks alot for the responses.

yeah, i have a good idea that this was originally done in a more
'robust' illustrator type prog.

i'll give the vb route a try if they still want to pursue it!


thanks again.

--

Cheers
Andy

http://www.andypope.info

 

[Tushar Mehta]

Treat multiple columns as though they constitute a single bar and you
can do what you want with simple conditional formatting.

Suppose your data are in column A:E. Suppose suzy is in row 2 and bob
in row 3.

Since you have 3 questions, you cannot create one bar from 100 columns.
It also doesn't make sense for aesthetic reasons. I decided to create
one bar from 25 columns.

So, select 25 cells from F6:AD2. Set the column width to 0.5. Set the
conditional formatting (Format | Conditional Formatting...) to 'Formula
Is' and specify the formula as =$C2/4>(COLUMN()-6) Click the Format...
button and select an appropriate color from the Patterns tab (I used
green).

Do the same for 25 more cells from AE2. This time, the conditional
formatting formula should be =$D2/4>(COLUMN()-31). Also, you might
want to use a different color.

Repeat for the 3rd question.

Experiment with the number of columns per bar (i.e., number of columns
per question) and the column width to achieve the desired aesthetic
effect.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions