Chart range that skips non-numerical data points

[labratto]

Hi all,
I am trying to make a chart that skips points that are not numbers. For the x-axis my values are coming from this named range formula (paintMSdatapointnumber) which shows the last specified number of values.

=OFFSET(' Paint'!$A$14,COUNT(' Paint'!$A:$A),0,-' Paint'!$X$11,1)

I thought with the count function it would skip the "" cells, but it appears that is not the case. The cells that appear blank have formulas in them that result in "" if there is no data entered into the parent cells.
The y values are the above names range with the column offset.

=OFFSET(paintMSdatapointnumber,0,4)

Can I make this work, or should I start over with a different strategy?

 

[Jeffrey Mahoney]

This is my first take on it. I usually create helper columns for the chart that filter the data I need. Column B provides a row number if the test is true. Columns E-G return the data based on row numbers

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1	Numbers	Row	Y1	Y2	Xvalues	Y1 Values	Y2 Values
2	1	1	20	5	1	20	5
3	2	2	30	10	2	30	10
4	3	3	40	15	3	40	15
5	4	4	50	20	4	50	20
6	5	5	60	25	5	60	25
7	g	0	70	30	6	80	35
8	6	6	80	35	5	90	40
9	5	7	90	40	4	100	45
10	4	8	100	45	7	110	50
11	7	9	110	50	8	130	60
12	j	0	120	55	9	140	65
13	8	10	130	60	10	160	75
14	9	11	140	65
15	i	0	150	70
16	10	12	160	75
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=XLOOKUP(ROW()-1,$B$2:$B$16,$A$2:$A$16,"")
F2:F16	F2	=XLOOKUP(ROW()-1,$B$2:$B$16,$C$2:$C$16,"")
G2:G16	G2	=XLOOKUP(ROW()-1,$B$2:$B$16,$D$2:$D$16,"")
B2:B16	B2	=IF(ISNUMBER(A2),MAX($B$1:B1)+1,0)

 

[labratto]

This is my first take on it. I usually create helper columns for the chart that filter the data I need. Column B provides a row number if the test is true. Columns E-G return the data based on row numbers

Book2 (version 1).xlsb
	A	B	C	D	E	F	G
1	Numbers	Row	Y1	Y2	Xvalues	Y1 Values	Y2 Values
2	1	1	20	5	1	20	5
3	2	2	30	10	2	30	10
4	3	3	40	15	3	40	15
5	4	4	50	20	4	50	20
6	5	5	60	25	5	60	25
7	g	0	70	30	6	80	35
8	6	6	80	35	5	90	40
9	5	7	90	40	4	100	45
10	4	8	100	45	7	110	50
11	7	9	110	50	8	130	60
12	j	0	120	55	9	140	65
13	8	10	130	60	10	160	75
14	9	11	140	65
15	i	0	150	70
16	10	12	160	75
Sheet2

Cell Formulas
Range	Formula
E2:E16	E2	=XLOOKUP(ROW()-1,$B$2:$B$16,$A$2:$A$16,"")
F2:F16	F2	=XLOOKUP(ROW()-1,$B$2:$B$16,$C$2:$C$16,"")
G2:G16	G2	=XLOOKUP(ROW()-1,$B$2:$B$16,$D$2:$D$16,"")
B2:B16	B2	=IF(ISNUMBER(A2),MAX($B$1:B1)+1,0)

I am attempting this solution, but my formula in for the second column (your B) always returns 0.

=IF(ISNUMBER('Paint Chart Data'!H15), MAX($A$3:A3)+1,0)

 

[labratto]

I am attempting this solution, but my formula in for the second column (your B) always returns 0.

=IF(ISNUMBER('Paint Chart Data'!H15), MAX($A$3:A3)+1,0)

The numbers are not typed in, they are also a formula

 

[Jeffrey Mahoney]

A formula resulting in a number will still test TRUE by ISNUMBER. Is your formula in column A? Is the cell number formatting set to a number and not text?