Result of formula reflected in Chart (Excel 2002)

[Helen]

I am conducting a program evaluation, tracking clients' progress in various
Categories as a result of their participation in a program. In each Category
a client may be deemed In Crisis, At Risk, Stable, Safe, or Thriving (I'll
call these Series). There are Y possibilities in each Series. (Each Series
might have a different value for Y.)

The client responds to a questionnaire by marking responses in various
components of each category. The responses are cataloged by me on a Matrix
broken down by Category and Series.

I want to track these responses on a chart (I'm thinking a bar style),
reflecting the percentage of each positive response per Series. Is it
possible to do this without having an additional column for the formula?

Series1 Max Series 2 Max Series 3 Max
Category1 X Y X Y X Y

Question clear as mud?

 

[Del Cotter]

I want to track these responses on a chart (I'm thinking a bar style),
reflecting the percentage of each positive response per Series. Is it
possible to do this without having an additional column for the formula?

In theory, yes. You can create a named range that divides X by Y, then
use that named range in the chart formula.

But it's a party trick: an immense amount of work for no practical
benefit. I strongly advise you to just make the required extra columns.
It's less complicated to develop, less effort to maintain, and more easy
to explain to someone else.

 

[Frank Pytel]

Helen;

I have to run out the door, but I think I have what you are looking for, If
I am understanding you correctly. Try this quicky table that I made. If it
works contact me and I will see if I can explain what I did.

http://groups.google.com/group/excel-applications-and-spreadsheet-programming

Man Hour Revenue Chart.xls

Column D is a calculation. It could easily be changed to data. This should
chart what your looking for. Everything is Week Dependent, as the chart
shows. But Week could easily be changed for Category.

I hope this helps

Frank Pytel

 

[Helen]

Thanks for giving me access to your work product. It looks like, as Del
said, I'm going to need to add a column for the formula. Your spreadsheet
has been useful in helping me visualize a layout that won't be cumbersome.

Thanks again!

 

[Frank Pytel]

Helen;

Glad I could help. Del is right in that anytime you can break down a formula
into it's component parts, especially charting in excel, the more versatile
your charts can be. I try to avoid long calculations unless I am validating
with IF statements. Excel has many great functions that shorten up the
creation time of spreadhseets but it makes them much less scalable, in my
dirt poor stupid opinion. I am sure there are lots of excel brainiacs that
would disagree. What do I know? (not much, thank God).

God Bless

Frank Pytel

http://groups.google.com/group/excel-applications-and-spreadsheet-programming