Find the first and last non-zero numbers in a row and subtract them

[vladimiratanasiu]

Hello!

I have a monthly large table of stocks with a lot of products, as the example shown in the linked document Book3.xlsx . Taking into account that each product has variations in terms of volume, some days it has zero / non-zero values. I need a formula / macro to find the first and the last non-zero numbers from a row (e.g. B3, AE3 / D4, AF4) and subtract the first from the last one (e.g. AE3-B3/ AF4-D4 - see the yellow cells).

Thank you!

 

[vladimiratanasiu]

Another possible approach too

21 12 12.xlsm
	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
1	Day/month																															Total
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
3	1	0	0	7	0	12	0	0	116	0	0	0	0	49	0	0	396	0	170	18	0	0	0	0	2296	0	0	543	0	19	0	18
4	0	0	590	0	0	0	14	0	0	0	1389	44	2190	0	0	0	0	149	0	1860	0	0	1126	2269	0	0	19	0	4409	0	1	-589
5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	30
7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	999	0	0	0	0	0	0	0	0	0	0	0	0	0	0	999
Last - First

Cell Formulas
Range	Formula
AH3:AH7	AH3	=IFNA(LOOKUP(9E+99,IF(C3:AG3=0,"",C3:AG3))-IF(COUNTIF(C3:AG3,">0")=1,0,INDEX(C3:AG3,MATCH(TRUE,C3:AG3>0,0))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you too for the answer !

I applied your array formula and it works well. The only issue could be the instability of formula's content. When I simply access it in the formula bar, the arrays dissapear and the final result is replaced with message #VALUE. I don't use very often this type of formula, that's why I could miss the method for overcoming the mentioned inconvenient.