Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sharon
 
Posts: n/a
Default The best method for forumlas and charts???

Hello all,

I'm looking for suggestions on the best way to go about getting the
information I want to display in a chart. Depending on the response, I may
also need help implementing the suggestion. Here's what I want to do:

On Sheet3 I have a range of data, for example:

Columns
A B C D
20-Sep R Y G
21-Sep Y n/a R
22-Sep n/a G Y
23-Sep R R n/a

On Sheet7, I want to have 16 line graph charts that:
1) X axis = Column A

2) Y axis to be a label that says, "Red, Yellow, Green" but the actual data
to be based on the range of Column B, C, D, etc.

3) In my example above, I have 3 columns of data. In my real spreadsheet
there are 16 columns of data. I will have a separate chart based on each
column.

So for example,
one line chart with the X axis = Column A and Y axis = Column B,
then a second chart with the X axis = Col A and Y axis = Col C, etc.

But, I only want to plot dates with R, Y, or G. If there is an N/A in
Column C, I don't want to plot that date in the line chart. Likewise for the
rest of the charts. So based on my sample data above, I would have my first
chart like this:

X axis = Column A
Y axis = Column B
Y axis label = "Red, Yellow, Green"

Green |
|
Yellow | x
|
Red | x x
_____________________________________________
9/20 9/21 9/23

Here's what I'm currently doing:

On Sheet3:
In column BA I have the formula =A1 to get the date.
In column BB, BC, BD, etc. I have the formula
=IF(B3="R",1,IF(B3="Y",2,IF(B3="G",3,"")))

On Sheet7 I have a line chart:
The Y axis = scale 1-4 with Word Art placed at Red = 1, Yellow = 2, and
Green = 3.
The X axis = Sheet3!BA1:BA400
The first series = Sheet3!BB1:BA400

This plots the dates on the X axis and if a date has a G, then it plots the
marker for the date at the Y axis 3 gridline, if the date has a Y it plots it
at the 2 gridline, or if it has a R, it plots the mark at the 1 gridline.
But if there was an n/a, from the original data set, my IF statement ignores
that and leaves a blank in the range. Then when the chart sees a blank, it
plots a 0 for that date and my line graph takes a dive. So I want to skip
the dates with blanks or n/a's from the original data set in my graph.

Is there an easy modification to what I'm currently doing or is there just
simply a more efficient way to go about this? I'm a little afraid to create
a chart in VB since I don't have any experience doing that, but not to say if
someone told me how to do that I wouldn't try it. :)

Thanks for any input,

Sharon
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
linkingof charts in worksheets to cells steve Excel Discussion (Misc queries) 0 May 6th 05 01:44 AM
Urgent help! how to automatically resize all the charts? crossingmind Excel Worksheet Functions 2 April 28th 05 04:56 PM
Working with Charts J. Stone Excel Discussion (Misc queries) 1 April 6th 05 09:06 AM
Can't create dynamic charts Brian Sells Charts and Charting in Excel 7 March 22nd 05 05:23 AM
i need help automating, or at least simplifying, my charts JZip Charts and Charting in Excel 1 February 9th 05 01:46 AM


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"