First/Last Non-Zero Value in a list

[Syer]

Heyy

I'm looking for formula to deduct 2 from the first non-zero cell and from the last non-zero value cell only.
So far I use IF formula (with reference to the table below):

=IF(B1=(INDEX($B1:$F1,MATCH(TRUE,INDEX($B1:$F1<>0,),0))), (B1-2), IF(B1=(LOOKUP(2,1/($B1:$F1<>0),$B1:$F1)), (B1-2), B1))

Data	0	2	4	4	1
Result	0	0	4	4	-1

Problem 1
The problem here is that if there are similar number for the first non-zero (INDEX($B2:$F2,MATCH(TRUE,INDEX($B2:$F2<>0,),0)) or the last non-zero (LOOKUP(2,1/($B2:$F2<>0),$B2:$F2) within the list, so the formula will result in minus 2 for the duplicate numbers as well instead of just the first/last non-zero value.

Example:

Data	0	2	4	1	1
Result	0	0	4	-1	-1

Problem 2
The second issue is that we don't want -1 result. Therefore, if the last/first non-zero value is 1 (which will result in -1) we need it to minus 1 only and bring the other 1 (from the original minus 2) to the second first/last non-zero value.

Example:

Data	1	1	0	4	5
Result	0	0	0	4	3

Any ideas how to solve these problems? Or at least one of them?

Please and thank you!

 

[pgc01]

Hi
Welcome to the board

Try in B2:
=IF(AND(B1<>0,COUNTIF($B$1:B1,"<>0")=1),MAX(B1-2,0),IF(AND(B1<>0,SUM($B$1:B1)-B1=1),B1-1,IF(AND(B1<>0,SUM(B1:$F$1)-B1=1),B1-1,IF(AND(B1<>0,COUNTIF(B1:$F$1,"<>0")=1),MAX(B1-2,0),B1))))

Copy to the right till F2

Remark: I did not consider the overlap of the first and last values

Ex.:

Book1
	A	B	C	D	E	F
1	Data	1	2	4	0	1
2	Result	0	1	3	0	0
Sheet1

 

[Syer]

Thank you so much! The formula works like a charm

However, I noticed that some of my data is like this:

Data	0	5	1	0	0
Result	0	3	0	0

How do it take into consideration this overlap?

 

[pgc01]

Hi

I'm glad it helped so far.

You did not post what you want to do in the case of overlap.

Please explain the logic for the case of overlap and also post the result you expect in these 2 cases:

Book1
	A	B	C	D	E	F
1	Data	0	5	1	0	0
2	Result
Sheet1

and

Book1
	A	B	C	D	E	F
1	Data	0	0	3	0	0
2	Result
Sheet1

 

[Syer]

Hi,

Apologies for that.

Here's the result that I need for each of the cases:

Case 1:

Data	0	5	1	0	0
Result	0	2	0	0	0

*basically the sum of these 2 numbers has to minus 4 since it is the only data available

Case 2:

Data	0	0	3	0	0
Result	0	0	0	0

*still have to deduct 4 and since it'll be a -1 then it should just become 0

Thank you.

 

[pgc01]

Hi

Try in B2:

=MAX(0,B1-(IF(AND(B1<>0,COUNTIF($B$1:B1,"<>0")=1),2,IF(AND(B1<>0,SUM($B$1:B1)-B1=1),1))+IF(AND(B1<>0,SUM(B1:$F$1)-B1=1),1,IF(AND(B1<>0,COUNTIF(B1:$F$1,"<>0")=1),2))))

Copy to the right

 

[Syer]

Yup works amazingly. Thank you so much!

 

[pgc01]

You're welcome. Thanks for the feedback.