Plotting a graph with every nth cell

davyj93

New Member
Joined
Apr 14, 2017
Messages
14
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):

Time (mins)Engine Power (kW)

<tbody>
</tbody>


Thanks,
David
 

JustynaMK

Active Member
Joined
Aug 28, 2016
Messages
497
Office Version
365, 2013
Platform
Windows
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

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
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

New Member
Joined
Apr 14, 2017
Messages
14
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

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
Post the formula you are using here.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
do you have any hidden rows, perhaps? As is stands, the formula should do exactly what it says on the tin...
 

davyj93

New Member
Joined
Apr 14, 2017
Messages
14
Here is the table again with added columns:

nth cell values:
Time (mins)Power (kW)Time (mins)Power (kW)
112112
2122240
3152950
4173657
517#REF!#REF!
618#REF!#REF!
719#REF!#REF!
820#REF!#REF!
922#REF!#REF!
1025#REF!#REF!
1125#REF!#REF!
1225#REF!#REF!
1326#REF!#REF!
1427#REF!#REF!
1528#REF!#REF!
1630#REF!#REF!
1733#REF!#REF!
1835#REF!#REF!
1937#REF!#REF!
2038#REF!#REF!
2138#REF!#REF!
2240#REF!#REF!
2344#REF!#REF!
2444#REF!#REF!
2544#REF!#REF!
2647#REF!#REF!
2748#REF!#REF!
2849#REF!#REF!
2950#REF!#REF!
3052#REF!#REF!
3153#REF!#REF!
3253#REF!#REF!
3354#REF!#REF!
3455#REF!#REF!
3555#REF!#REF!
3657#REF!#REF!

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

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

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
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

New Member
Joined
Apr 14, 2017
Messages
14
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.
 

Forum statistics

Threads
1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top