problem with formula

[lakke2120]

The formula I have is working to a degree but it is not recognizing cells in a row that have a zero value (0). I have a row of numbers that have several blank cells and I am trying to display the values in order or left to right in a row including zeros. Here is the formula I have:
=IFERROR(OFFSET($B14,0,SMALL(IF($B14:$BA14<>0,COLUMN($B14:$BA14)),COLUMN(J14))-2),"")

thank you

 

[maabadi]

How About:

=IFERROR(OFFSET($B14,0,SMALL(IF(ISNUMBER($B14:$BA14),COLUMN($B14:$BA14)),COLUMN(J14))-2),"")

Press CTRL+SHIFT+ENTER

 

[Toadstool]

Hi Lakke2120,

I believe this should work for you.

Lakke2120.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA
14	1		2	3	4	5	6	7	8	9			0	0	0	0	0	0	0	0						0	0	0	1	3	5	7	0	0						0	0	0	5	6	7	0	0	0	0	0	0	10
15
16	1	2	3	4	5	6	7	8	9	0	0	0	0	0	0	0	0	0	0	0	1	3	5	7	0	0	0	0	0	5	6	7	0	0	0	0	0	0	10
Sheet1

Cell Formulas
Range	Formula
B16:BA16	B16	=IFERROR(INDEX($B$14:$BA$14,AGGREGATE(15,6,COLUMN($B$14:$BA$14)-COLUMN($A$14)/($B$14:$BA$14<>""),COLUMN()-COLUMN($A$14))),"")