Confusion with graph data...

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I am trying to put 2 graphs together based on inputted data on Sheet 'DATA'. There are 40 possible inputs. Not all may be used...

DCP1.jpg

The columns I'm interested in are E & F for one graph and E & I for another.

When I input the data series for Depth (E) & Cum N (F) I use:

=DATA!$F$2:$F$41 - (x-series)
=DATA!$E$2:$E$41 - (y-series)

This plots this graph which is good

DCP2.jpg


However, when I try to plot the data series for Depth (E) & CBR (I) I use:

=DATA!$I$2:$I$41 - (x-series)
=DATA!$E$2:$E$41 - (y-series)

it plots the same graph as above when it should be looking like:

DCP3.jpg


which I was able to plot by changing the data series (on x) to:

=DATA!$I$2:$I$21 - (x-series)
=DATA!$E$2:$E$41 - (y-series)

So it appears that the 'blank' entries (after the initial 20 values) are making it throw a wobbly...

Can anyone help throw some light on this for me? How can I make my graph show the correct curve regardless as to how many values are inputted?

Thanks very much for your time

Rob
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,657
Office Version
  1. 2010
Platform
  1. Windows
I have found Excel graph control to be difficult and very poor. I had a similar problem and I put some code into the worksheet change event to update the graph automatically to tie up with the actual data . This is a graph in columns E and F of my sheet:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Application.EnableEvents = False
   
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetSourceData Source:=Range(Cells(1, 5), Cells(lastrow, 6))
    Application.EnableEvents = True

End Sub
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
Thanks offthelip, that is just the ticket! Great way to work with dynamic ranges!

Thanks again for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top