Excel Chart Data

[Win2Win]

How do I collect data for a graph in steps in Excel 2007?

I haven't used the function for about 2 years, and forgotten....or I may not have....MS may have changed it.

In 2002, I'm sure I'd select the data range for the chart as such;
='Lambourn Yuck!$I$15:$I$1804

then to only do the graph based on every other 5 points I'd do this;
='Lambourn Yuck!$I$15:$I$1804 STEP 5

That may be wrong, but I think that's the way I done it....however in Excel 2005 it comes up as an error :doh .

I don't want 1804 points in the graph, as it takes a while to process, only every other 10 for the general trend. Anyone know Excel 2007?

I'll have a fiddle in Open Office, probably does it the old way.

[mathare]

I've never played with stepped charts I'm afraid.

And I've never even seen Excel 2007

[vegyjones]

Steps in the Charts! :yikes:

Although I must admit 5678 was one hell of a catchy tune :pk1

[Win2Win]

What's an ID Vegy? :laugh

[vegyjones]

id /ɪd/ Pronunciation Key - Show Spelled Pronunciation[id] Pronunciation Key - Show IPA Pronunciation
–noun Psychoanalysis. the part of the psyche, residing in the unconscious, that is the source of instinctive impulses that seek satisfaction in accordance with the pleasure principle and are modified by the ego and the superego before they are given overt expression

You'd know that if you watched the Simpsons! :)

[sparkyminer]

id /ɪd/ Pronunciation Key - Show Spelled Pronunciation[id] Pronunciation Key - Show IPA Pronunciation
–noun Psychoanalysis. the part of the psyche, residing in the unconscious, that is the source of instinctive impulses that seek satisfaction in accordance with the pleasure principle and are modified by the ego and the superego before they are given overt expression



To put in laymans terms. It's what makes Vegy act like a twit, spelt with an 'a'.:D

[GlosRFC]

It's difficult for MS to change a function that was never there in the first place! I'm certainly not aware of any STEP function in 2007 or any previous incarnations of Excel.

However, that doesn't mean that step charts (http://www.tushar-mehta.com/excel/charts/step_chart/) can't be created but I suspect that's not what you actually want to do. What you're asking is...can Excel select every nth (in this case the 5th) piece of data in your series? The answer is that there's no function or command that can specifically do this. However, there is a way - you have to fool Excel into believing that some of your data actually has a value of N/A. These values are actually ignored and not plotted by Excel's charts so you need to find a way to pluck out every 5th value but substitute N/A for every non-5th value.

Fortunately your data starts on a convenient row number so try adding this formula into J15
=IF(MOD(ROW(),5)=0,I5,NA())

What this does is to use modulus arithmetic to check the value of the current row. Modulus returns the remainder after the value has been divided by the relevant divisor, e.g. both 5MOD2 and 10MOD9 would both equal 1 (2 goes into 5 twice, which equals 4, so there is 1 left over while 9 goes into 10 once, leaving 1 as well).

So the formula takes the row number - ROW() - (in this case it's 5) and then applies the modulus formula to it - MOD(ROW(),5) - and then checks to see if there is a remainder - =0

The modulus formula is enclosed with an IF statement - =IF(this=0,return I5,otherwise return NA())

So in row 5, 5MOD5 does equal 0 and so the IF statement tells Excel to return the value in I5. In row 6, 6MOD5 equals 1 (5 goes into 6 with 1 left over) so the IF statement returns an N/A value instead. The same goes for the next 3 rows:
7MOD2 = 2 so N/A is returned
8MOD2 = 3 so N/A is returned
9MOD2 = 4 so N/A is returned
In the 10th row it returns the correct value again:
10MOD2 = 0 so I10 is returned

Copy the formula you've created in cell J5 to the end of your data series at J1804 and you should be able to plot the data in ='Lambourn Yuck!$J$15:$J$1804 (note the column references have changed) and let Excel strip out all of those N/A values from the chart automatically.

If you wanted, you could amend the formula slightly to read:
=IF(MOD(ROW(),$J$1)=0,I5,NA())
so that you can plot every 2nd, 5th, 10th, 23rd or whatever value you wish providing you've entered it into cell J1

Hope that helps

[GlosRFC]

By the way Keith, what's the problem with the advert in the top-right corner of the page (usually a Betfair one)? It seems to be positioned wrong as it often butts up against the Win2Win graphic and it also has a habit of changing sizes which makes the horizontal scroll bar pop into action here.

The attached graphic shows the problem - note how it's cut off in my browser plus how tight it is against your header graphic.

The biggest problem though (especially on dial-up) is that it can take an absolute age to download even if there's a previous version of the same advert in the local cache. This sometimes has the effect of slowing up the rest of the page depending on the mood IE is in and how it decides to render it - text or graphics first.

Just thought you might want to know and take a look.

[Win2Win]

Cheers Glos (big wad of Rep on the way), I must have picked up a VBA Function off the Internet, but ye olde spreadsheets with it on don't load it in 2007 :splapme

I'll fix the ad now......:)

[GlosRFC]

Excellent...'cos it's just refusing to download again...bah! I guess there's a glitch with the adfarm server that's causing the delay there.

[Win2Win]

Working fine here :doh, maybe the update was only half uploaded when u refreshed!!

[GlosRFC]

Strange...then again, if you're on broadband you probably don't notice a delay with it. Size-wise the ad looks much better now - and no horizontal scrollbar popping up...thanks :)

This graphic shows what I often get...nothing. But when that happens I keep getting the loading image in the IE tab plus the waiting message at the bottom of the page