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

V

vladimiratanasiu

Active Member
Joined
Dec 17, 2010
Messages
347
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi vladimiratanasiu,

Does this work for you?

vladimiratanasiu.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Product nameDay/monthTotalResult
212345678910111213141516171819202122232425262728293031
3A1007012001160000490039601701800002,2960054301901818
4B00590000140001,389442,190000014901,860001,1262,269001904,40901-589-589
Sheet1
Cell Formulas
RangeFormula
AH3:AH4AH3=INDEX($B3:$AF3,AGGREGATE(14,6,COLUMN($B$3:$AF$3)-COLUMN($A$3)/($B3:$AF3>0),1))-INDEX($B3:$AF3,AGGREGATE(15,6,COLUMN($B$3:$AF$3)-COLUMN($A$3)/($B3:$AF3>0),1))
 
Upvote 0
Toadstool said:
Hi vladimiratanasiu,

Does this work for you?

vladimiratanasiu.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Product nameDay/monthTotalResult
212345678910111213141516171819202122232425262728293031
3A1007012001160000490039601701800002,2960054301901818
4B00590000140001,389442,190000014901,860001,1262,269001904,40901-589-589
Sheet1
Cell Formulas
RangeFormula
AH3:AH4AH3=INDEX($B3:$AF3,AGGREGATE(14,6,COLUMN($B$3:$AF$3)-COLUMN($A$3)/($B3:$AF3>0),1))-INDEX($B3:$AF3,AGGREGATE(15,6,COLUMN($B$3:$AF$3)-COLUMN($A$3)/($B3:$AF3>0),1))
Click to expand...
Thank you very much, it works perfectly!
 
Upvote 0
You had sent me a private message as you were seeing errors in your live data. I've responded but pointed out the forum rules ask that all questions are discussed in the forum, not in private message. I am therefore addressing your challenge here but without your private data.

A #NUM error occurs if all values are zero. To avoid this you could wrap the formula in an IFERROR.

A zero is returned if only one cell in the range is greater than zero. If you want a different response then we could prefix the formula with
Excel Formula: 
=IF(COUNTIF($C3:$AG3,">0")=1,MAX($C3:$AG3),rest of formula)
or
Excel Formula: 
=IF(COUNTIF($C3:$AG3,">0")=1,0-MAX($C3:$AG3),rest of formula)
depending on if you treat the only number as first or last non-zero.

You had inserted a new column so I've done the same.

You queried the use of absolute addresses in part of the formula. In the two cases where COLUMN($B$3:$AF$3)-COLUMN($A$3) was used then the actual cell contents of that range are not used, just the column numbers (so I've changed them to COLUMN($C$2:$AG$2)-COLUMN($B$2) after inserting the new column).
Some parts of the formula use absolute columns (because we're always looking at the same column range) but relative rows (because we are checking the contents of that row). e.g. ($C3:$AG3>0).

Here is the amended sheet.

vladimiratanasiu.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1NameDescDay/monthTotal
212345678910111213141516171819202122232425262728293031
3AProd-A1007012001160000490039601701800002,29600543019018
4BProd-B00590000140001,389442,190000014901,860001,1262,269001904,40901-589
5CProd-C0000000000000000000000000000000 
6DProd-D1234567891011121314151617181920212223242526272829303130
7EProd-E0000000000000000999000000000000000
2nd
Cell Formulas
RangeFormula
AH3:AH7AH3=IFERROR(INDEX($C3:$AG3,AGGREGATE(14,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1))-INDEX($C3:$AG3,AGGREGATE(15,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1)),"")
 
Upvote 0
Toadstool said:
You had sent me a private message as you were seeing errors in your live data. I've responded but pointed out the forum rules ask that all questions are discussed in the forum, not in private message. I am therefore addressing your challenge here but without your private data.

A #NUM error occurs if all values are zero. To avoid this you could wrap the formula in an IFERROR.

A zero is returned if only one cell in the range is greater than zero. If you want a different response then we could prefix the formula with
Excel Formula: 
=IF(COUNTIF($C3:$AG3,">0")=1,MAX($C3:$AG3),rest of formula)
or
Excel Formula: 
=IF(COUNTIF($C3:$AG3,">0")=1,0-MAX($C3:$AG3),rest of formula)
depending on if you treat the only number as first or last non-zero.

You had inserted a new column so I've done the same.

You queried the use of absolute addresses in part of the formula. In the two cases where COLUMN($B$3:$AF$3)-COLUMN($A$3) was used then the actual cell contents of that range are not used, just the column numbers (so I've changed them to COLUMN($C$2:$AG$2)-COLUMN($B$2) after inserting the new column).
Some parts of the formula use absolute columns (because we're always looking at the same column range) but relative rows (because we are checking the contents of that row). e.g. ($C3:$AG3>0).

Here is the amended sheet.

vladimiratanasiu.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1NameDescDay/monthTotal
212345678910111213141516171819202122232425262728293031
3AProd-A1007012001160000490039601701800002,29600543019018
4BProd-B00590000140001,389442,190000014901,860001,1262,269001904,40901-589
5CProd-C0000000000000000000000000000000 
6DProd-D1234567891011121314151617181920212223242526272829303130
7EProd-E0000000000000000999000000000000000
2nd
Cell Formulas
RangeFormula
AH3:AH7AH3=IFERROR(INDEX($C3:$AG3,AGGREGATE(14,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1))-INDEX($C3:$AG3,AGGREGATE(15,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1)),"")
Click to expand...
 
Upvote 0
Hello!

1. I didn't mean to break the rules by private message(s), since I used before currently this method without request from other members, moderators etc. to post only on the forum. Anyway, I will take into account your comment in the future.
2. Thank you for your continuous background on my problem. After making the necessary changes within the large table, I noticed that for the rows with a single non-zero value (see the yellow cells from Book3.xlsx ) the final result is value zero. My question is how could be changed the existing formula, in order to return the single non-zero value too in such cases?
Thank you!
 
Upvote 0
Hi,

1. No problem.

2. Yes, I covered this in my previous response and now you've given direction I've included the first COUNTIF option.

vladimiratanasiu.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1NameDescDay/monthTotal
212345678910111213141516171819202122232425262728293031
3AProd-A1007012001160000490039601701800002,29600543019018
4BProd-B00590000140001,389442,190000014901,860001,1262,269001904,40901-589
5CProd-C0000000000000000000000000000000 
6DProd-D1234567891011121314151617181920212223242526272829303130
7EProd-E000000000000000099900000000000000999
8 
3rd
Cell Formulas
RangeFormula
AH3:AH8AH3=IF(COUNTIF($C3:$AG3,">0")=1,MAX($C3:$AG3),IFERROR(INDEX($C3:$AG3,AGGREGATE(14,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1))-INDEX($C3:$AG3,AGGREGATE(15,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1)),""))
 
Upvote 0
Solution
Toadstool said:
Hi,

1. No problem.

2. Yes, I covered this in my previous response and now you've given direction I've included the first COUNTIF option.

vladimiratanasiu.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1NameDescDay/monthTotal
212345678910111213141516171819202122232425262728293031
3AProd-A1007012001160000490039601701800002,29600543019018
4BProd-B00590000140001,389442,190000014901,860001,1262,269001904,40901-589
5CProd-C0000000000000000000000000000000 
6DProd-D1234567891011121314151617181920212223242526272829303130
7EProd-E000000000000000099900000000000000999
8 
3rd
Cell Formulas
RangeFormula
AH3:AH8AH3=IF(COUNTIF($C3:$AG3,">0")=1,MAX($C3:$AG3),IFERROR(INDEX($C3:$AG3,AGGREGATE(14,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1))-INDEX($C3:$AG3,AGGREGATE(15,6,COLUMN($C$2:$AG$2)-COLUMN($B$2)/($C3:$AG3>0),1)),""))
Click to expand...
Thank you very much! It fits exactly what I need!
 
Upvote 0
Another possible approach too

21 12 12.xlsm
CDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Day/monthTotal
212345678910111213141516171819202122232425262728293031
3100701200116000049003960170180000229600543019018
400590000140001389442190000014901860001126226900190440901-589
50000000000000000000000000000000 
61234567891011121314151617181920212223242526272829303130
7000000000000000099900000000000000999
Last - First
Cell Formulas
RangeFormula
AH3:AH7AH3=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Average first n non-zero results in a row
Replies
2
Views
269
arthurbr
arthurbr
V
  • Question Question
VBA find first non 0 value in range then count how many rows until next non 0 value
Replies
2
Views
571
kvsrinivasamurthy
K
P
  • Solved
VBA Find first empty cell in row
2
Replies
10
Views
5K
MARK858
MARK858
J
  • Question Question
How to find the first and last non blank cells in a row, then pull the column names (month/year) to get first seen and last seen
Replies
1
Views
1K
Peter_SSs
Peter_SSs
T
  • Question Question
Row Summary: First, Last, Contiguous, Non-contiguous
Replies
1
Views
404
Marcelo Branco
M

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top