Using Offset with Countifs in a scatter chart

[Peter Davison]

HI All,
I have a scatter graph
The x axis ranging from column KG17 to KG500 (Some of the cells are blank)
The Y axis ranging from column KH17 to KH500 (Some of the cells are blank)
In column KF17 to KF500 I have the numbers 1 to 10 in variable amounts matching the entries in Column KG and KH (e.g. 10 rows may be 1, 20 rows may be 2 etc)

Each of these number 1 to 10 are a series but because they can vary how many rows each number has I wanted to make it dynamic using a named range

I created the formula below for the X and Y axis, but somehow it doesn't like it and I'm not sure what I have done wrong.

=OFFSET(Combined!$KG$17,,,COUNTIFS(Combined!$KG$17:$KG$500,”<>”,Combined!$KF$17:$KF$500,1))
=OFFSET(Combined!$KH$17,,,COUNTIFS(Combined!$KG$17:$KG$500,”<>”,Combined!$KF$17:$KF$500,1))

I know I will then have to create a named range for each of the 1 to 10 Series for both x and y axis once I have the formula correct. (I think I will)?

If anyone can advise me I would really appreciate it.
Regards

 

[pgc01]

I'm glad it helped.
Thanks for the feedback.