Plotting a graph with every nth cell

[davyj93]

Hi guys,

I am trying to plot a graph but I have a lot of data. I have 213,792 rows of data but Excel only lets you plot 32,000 data points on a graph.

How do I plot every 7th row of data instead?

Below is a small look at what my columns look like (and the rows would be filled in of course):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Time (mins)[/TD]
[TD]Engine Power (kW)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
David

 

[JustynaMK]

Hi David,

I am afraid that the only solution would be to create a separate table with cell references to the original table, i.e.
Time | Engine Power
=A2 =B2
=A9 =B9
=A16 =B16
etc.
...and then creating a chart linked to the new table.

 

[gaz_chops]

You could use below formula to give you every 7th row in the new table.

for the 1st row just use =A1
then use & drag down
=INDEX($A$2:$A$60,ROW(A1)*7)

Someone may have a better solution.

 

[davyj93]

I tried the formula.

It gave me the correct starting value but then gave the 14th value. After this it started giving every 7th value.

Is there a reason why the 2nd value (on the newly created column) came up as the 14th original value rather than the 7th?

 

[gaz_chops]

Post the formula you are using here.

 

[PaddyD]

do you have any hidden rows, perhaps? As is stands, the formula should do exactly what it says on the tin...

 

[davyj93]

Here is the table again with added columns:

[TABLE="class: grid, width: 339"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]nth cell values:[/TD]
[/TR]
[TR]
[TD="align: center"]Time (mins)[/TD]
[TD="align: center"]Power (kW)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Time (mins)[/TD]
[TD="align: center"]Power (kW)[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]57[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]33[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]34[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]35[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: center"]36[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
</tbody>[/TABLE]

In cell D3, I used
=A3

Similarly, in cell E3, I used
=B3

Then for cell D4, I used
=INDEX($A$4:$A$38,ROW(A3)*7)

And for cell E4, I used
=INDEX($B$4:$B$38,ROW(B3)*7)

Then I dragged down the formulas from E4 and D4.

 

[gaz_chops]

What row number is the 1st value in, Time Mins = 1?

 

[gaz_chops]

Ah the formula is incorrect


=INDEX($A$4:$A$38,ROW(A1)*7)

The row(A1)*7 = 7
as you drag it down then
row(A2)*7 = 14
row(A3)*7 = 21 etc

 

[davyj93]

The 1st value (Time = 1) is in row 3. This is cell A3.

That new formula does work. It just seems a bit strange to me because cell A1 is a blank cell.