In Cell Chart Proportionality And Compelling Visual

[wsnyder]

Hi all,


Using Excel 2007.

I am tring to come up with a way to show values as data bars for several A/R "Types" on a weekly basis.


Ive been trying with data bar graph, but due to extremes in values (+/- $M's), may not be possible or at least visually interesting


I then came across this post as Juice Analytics

http://www.juiceanalytics.com/writing/lightweight-data-exploration-in-excel/


About 1/2 way through the comments I cam across an interesting comment from DC that revealed this formula:

=REPT("|",A2/MAX($A$1:$A$9999)*CELL("WIDTH",B2)*3)


I replaced the "|" with [Alt] + [219] to get a rectangle


I've been playing around with Column width and the 3 multiplier to try to find a way to show the data bars propotionately while still visaully interesting and small


My problem is that high values could exceed $1M and lows could be negatives


This weeks range was from $137K to -$17.12


Any thoughts on how to use the in cell charts and produce a visually compelling chart under these circumstances?


Thanks

Winston

 

[wsnyder]

Hi all,


Did some more research

Found a post here at Chandoo.org http://tinyurl.com/23m2oup

"How to Visualize Survey Results using Incell Panel Charts"


In the comments to the post, David Hager gives us this formula for scaling in-cell charts: =REPT(”|”,(data_point*100/MAX(data_range))*CELL(”width”,this_cell)/(14+NOW()*0))


I tried this out with 3 test values:

1,000,000

100,000

10


Based on my column width for "this_cell" the length of the corresponding in-cell charts:

78

7

0


Which, of course, is unacceptable.

Are there any other "tricks" for normalizing given the extreme values in the data to be graphed to produce a meaningful in cell chart?


Thanks

Winston

 

[Hui]

Winston

You will need to define what is acceptable ?