Excel Charts - Charting only whats "required"

[bwatson]

Hi , as i have experienced , Excel doesnt like to Graph large data sets.

So in order to "lessen" the load i have put in formulas in my data sheet to highlight certain items based on a formula.

this is now what i want graphed , how do i do this ?

So Example would be:

Destination / Prefix / 1st Sep / 2nd Sep / 3rd Sep
South Afri / +2711 / R50.00 / R4.50 / R123.73
UK / +441234 / R67.00 / R134.50 / R4.45

Now this would be repeated down the rows by about 50,000 prefixes and to the right by calender days for +- 150-365 !! Days hence the large data set = problem.

So i added a column to the above example :
Destination / Prefix / 1st Sep / 2nd Sep / 3rd Sep / Last entry > 20% of the avg todate ( = yes or no answer )

South Afri / +2711 / R50.00 / R4.50 / R123.73 / yes ( as 123.73 is greater than ( R50+R4.50 ) *1.2
UK / +441234 / R67.00 / R134.50 / R4.45 / No ( as R4.45 is less than Average of the other 2 values x 1.2

But now i want Excel to only Plot the values where the last column = "yes"

any suggestions ?

 

[hatstand]

I would suggest you use a formula from Cpearson (brilliant site) to create a column of only the cells that have 'yes' next to them. Have you considered a pivot table?

http://www.cpearson.com/excel/noblanks.aspx

 

[RoryA]

You could simply use an autofilter on the last column since a chart will be default only plot visible data.

 

[bwatson]

This doesnt work.

if i filter by my column "> 20% increase" it does reduce the amount of rows , but i still have 365 days ( in columns ) preset ready to be filled in and Excel wants to include these even though they have no data ( yet ) and as such excel complains about being greater than 255 again.

how can i get the graph to only graph the last 30 days , e.g. today minus 30 ?

 

[RoryA]

Use dynamic named ranges - see here for example.

 

[bwatson]

Hi guys , thanks but 2 things !

1) this is not working
2) possibly because my data is left to right , not top to bottom. I am trying to use both the blank / non blank range option and the offset function , but neither seem to work for left to right they all show for where :

- Rows are blank ( mine will be columns )
- where additional months are added vertically , mine are added horizontally , and playing with the formulas doesnt work , i try selecting what i consider to be the "row" iso the whole of column A and when i try create my graph it complains.