| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sum the last 5 nonzero numbers in a row?
I have values in a table and want to add a column on the right that will look
to the left at the previous 20 columns and find the last 5 numbers (rightmost) that are not zero and sum them. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
try this array formula, confirm by Ctrl, Shift and Enter
assume your data is in column A =SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "crabatin" wrote: I have values in a table and want to add a column on the right that will look to the left at the previous 20 columns and find the last 5 numbers (rightmost) that are not zero and sum them. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
I have misread your request, you want to sum the last five non zero columns
instead of rows, try this array formula in U2 ( the 21st column), confirm by Ctrl, Shift and Enter =SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Francis" wrote: try this array formula, confirm by Ctrl, Shift and Enter assume your data is in column A =SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "crabatin" wrote: I have values in a table and want to add a column on the right that will look to the left at the previous 20 columns and find the last 5 numbers (rightmost) that are not zero and sum them. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
Francis wrote:
try this array formula, confirm by Ctrl, Shift and Enter assume your data is in column A =SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1)) However, the OP said he wanted to sum the last 5 non-zero numbers in a ROW of 20 cells, not in a column, and he wanted that sum to be in the 21st column. When I alter your formula for a row of 20 cells and put it to the right, I get a circular reference, just like I got when I put your formula in A101. Any idea how to fix that? =SUM(OFFSET($A$1,0,LARGE(IF($A$1:$T$1<0,COLUMN($A $1:$T$1)),5)-1,1,20)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
Francis wrote:
I have misread your request, you want to sum the last five non zero columns instead of rows, try this array formula in U2 ( the 21st column), confirm by Ctrl, Shift and Enter =SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5))) Please ignore my post, this answers my question (and the OP's). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
Yes this answers my question very well. Thank you for your help!
"Francis" wrote: I have misread your request, you want to sum the last five non zero columns instead of rows, try this array formula in U2 ( the 21st column), confirm by Ctrl, Shift and Enter =SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Francis" wrote: try this array formula, confirm by Ctrl, Shift and Enter assume your data is in column A =SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "crabatin" wrote: I have values in a table and want to add a column on the right that will look to the left at the previous 20 columns and find the last 5 numbers (rightmost) that are not zero and sum them. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
Thanks again for the help - I ran into a little snag.
After looking at this a little bit, I realized that this worked fine as long as the data started in column A, but if it wasn't exactly right when the data starts in column D. How can I modify it for any starting position? "Francis" wrote: I have misread your request, you want to sum the last five non zero columns instead of rows, try this array formula in U2 ( the 21st column), confirm by Ctrl, Shift and Enter =SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Francis" wrote: try this array formula, confirm by Ctrl, Shift and Enter assume your data is in column A =SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "crabatin" wrote: I have values in a table and want to add a column on the right that will look to the left at the previous 20 columns and find the last 5 numbers (rightmost) that are not zero and sum them. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I sum the last 5 nonzero numbers in a row?
Try this array formula (commit with CTRL+SHIFT+ENTER):
=SUM(W1:INDIRECT(ADDRESS(ROW(),LARGE((D1:W1<0)*CO LUMN(D1:W1),5)))) crabatin wrote: Thanks again for the help - I ran into a little snag. After looking at this a little bit, I realized that this worked fine as long as the data started in column A, but if it wasn't exactly right when the data starts in column D. How can I modify it for any starting position? "Francis" wrote: I have misread your request, you want to sum the last five non zero columns instead of rows, try this array formula in U2 ( the 21st column), confirm by Ctrl, Shift and Enter =SUM(T2:INDEX(A2:T2,LARGE(COLUMN(A2:T2)*(A2:T2<0) ,5))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Francis" wrote: try this array formula, confirm by Ctrl, Shift and Enter assume your data is in column A =SUM(OFFSET($A$1,LARGE(IF($A$1:$A$100<0,ROW($A$1: $A$100)),5)-1,0,100,1)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "crabatin" wrote: I have values in a table and want to add a column on the right that will look to the left at the previous 20 columns and find the last 5 numbers (rightmost) that are not zero and sum them. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Address of last nonzero value | Excel Worksheet Functions | |||
| Address of last nonzero value in a range | Excel Worksheet Functions | |||
| Locating Last Nonzero Cell in a Row | Excel Worksheet Functions | |||
| nonzero value | Excel Worksheet Functions | |||
| find first nonzero value in column | Excel Worksheet Functions | |||
Linear Mode
