How to adjust this chart dynamic range to extend to the rightmost cell that contains number?

[Rnkhch]

Hello,

I have the following chart dynamic range that up until now was working perfectly because I had contiguous range of numbers (E3 in the example XL2BB):

=$E$3:INDEX($E$3:$S$3,COUNT($E$3:$S$3))

However, now I have rows that do not necessarily have contiguous numbers (row 6 in the example XL2BB), and this COUNT-based formula fails (E7 in the example XL2BB). So, I'm trying to adjust the formula such that it returns the correct range (highlighted on row 8 in example XL2BB) starting from E6 to the rightmost number in row 6 including all the blank cells in between (by the way, in my real data, the blanks in the ranges are formula-based blanks, not empty blanks).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2			Ex1	contiguous numbers:	3	6	4	8	11	45
3				correct output	3	6	4	8	11	45
4
5
6			Ex2	non-contiguous numbers:	5					32
7				incorrect output:	5	0
8				desired output:	5					32
9
Sheet1

Cell Formulas
Range	Formula
E3:J3	E3	=$E$2:INDEX($E$2:$S$2,COUNT($E$2:$S$2))
E7:F7	E7	=$E$6:INDEX($E$6:$S$6,COUNT($E$6:$S$6))
Dynamic array formulas.

Thanks for any input!

 

[cmowla]

How about this:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2			Ex1	contiguous numbers:	3	6	4	8	11	45
3				correct output	3	6	4	8	11	45
4
5
6			Ex2	non-contiguous numbers:	5					32
7				incorrect output:	5	0
8				desired output:	5					32
Sheet2

Cell Formulas
Range	Formula
E3:J3	E3	=$E$2:INDEX($E$2:$S$2,COUNT($E$2:$S$2))
E7:F7	E7	=$E$6:INDEX($E$6:$S$6,COUNT($E$6:$S$6))
E8:J8	E8	=IF( $E$6:INDEX($E$6:$S$6,MAX(IF(ISNUMBER($E$6:$S$6),COLUMN($E$6:$S$6),0))-COLUMN($E$6)+1)=0, "", $E$6:INDEX($E$6:$S$6,MAX(IF(ISNUMBER($E$6:$S$6),COLUMN($E$6:$S$6),0))-COLUMN($E$6)+1) )
Dynamic array formulas.

If so, this is "shorthand" for that formula.

Book1.xlsx
	D	E	F	G	H	I	J
8	desired output:	5					32
Sheet2

Cell Formulas
Range	Formula
E8:J8	E8	=IFERROR(1/(1/($E$6:INDEX($E$6:$S$6,MAX(IF(ISNUMBER($E$6:$S$6),COLUMN($E$6:$S$6),0))-COLUMN($E$6)+1))),"")
Dynamic array formulas.

 

[Rnkhch]

This is fabulous! Thank you!! Your second formula at the bottom of your post is even better. I challenged both formulas by adding extra spaces and numbers after the current ones, and I even added text to some cells, and the second formula works flawlessly.

 

[cmowla]

You're quite welcome. Thank you for the feedback! (I didn't know that the second would actually be better than the first at first glance . . . I'm now glad I chose to shorten the formula in that manner!)

 

[Rnkhch]

Yeah, I'm also glad you did it; it's because of the two "1/content" flips which generates errors for text cells that are subsequently converted to blanks by IFERROR.

 

[Fluff]

FWIW another option

=$E$6:INDEX($E$6:$S$6,LOOKUP(2,1/($E$6:$S$6<>""),SEQUENCE(,14)))

 

[Rnkhch]

Hi Fluff,

Thanks. It seems that your formula generates zeros for all the blanks? Also if there's any text, it shows the text in the output as well.

 

[Fluff]

If the cells all have formula blanks (as you stated) then you would not get zeros in the output.

Also if there's any text, it shows the text in the output as well

Not really surprising, as you never mentioned anything about text in the row.

Can you post some more realistic data?

 

[Rnkhch]

Oh I see, right I decided to test text later in post #3 as extra challenge to the formulas Thank you!