Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
Tr this
=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
That worked perfect, but I messed up on what i needed. so here is the updated
version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
Hmm,
I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
Hi, I need a formula, working with excel 2003, and very new to it.
so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
That did not come out right on the page. all of the 1s will be in column k and that formula will be in column k as well "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
Hi,
I can see the logic for the left column(I) i think. because there is a blank you have added the value from the next column. But I can't see the logic or how you get 144 for then next. However, try this function. Alt + F11 to open VB editor. Right click 'This Workbook' and insert module and paste the code below in on the right. You call the code like this =CountUp(n,n) the first n is the column Number (9 for column i) the second n is the amount of numbers you want to count. So =countup(9,13) count the last 13 entries and if there are blanks it counts the column to the right. I have added no error trapping so if it finds text it falls over. Function CountUp(col As Integer, num As Integer) Numcounted = 0 lastrow = Cells(Rows.Count, col).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, col).Value < "" Then CountUp = CountUp + Cells(x, col).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout Else CountUp = CountUp + Cells(x, col).Offset(, 1).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout End If Next getmeout: End Function Mike "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
I think we are getting really close.
here is what I saw in the columns [ I ][ J ][ K ] [1 ][ 4 ][ 1 ] [2 ][ 5 ][ 1] [3 ][ 6 ][ 1] [4 ][ 7 ][ 1] [ 5][ 8 ][ 1 ] [ 6 ][ 9 ][ 1 ] [ 7 ][10][ 1 ] [ 8 ][11][ 1 ] [12][ ][ 1 ] [10][13][1] [11][14][1] [12][15][1] [14][16][1] [15][17][1] [0][18][1] [ ][ ][=sum(xxx)] [107][144][13] the new formula would not use the space next to it. Also this spreadsheet gets added to mostly everyday. I use the spreadsheet to track the flights I do, so one row is one flight and at the end of 13 rows is one page in my log book and I was trying to make it easier to have the spreadsheet add all of the numbers from the 13 previous flights thanks for your help "Mike H" wrote: Hi, I can see the logic for the left column(I) i think. because there is a blank you have added the value from the next column. But I can't see the logic or how you get 144 for then next. However, try this function. Alt + F11 to open VB editor. Right click 'This Workbook' and insert module and paste the code below in on the right. You call the code like this =CountUp(n,n) the first n is the column Number (9 for column i) the second n is the amount of numbers you want to count. So =countup(9,13) count the last 13 entries and if there are blanks it counts the column to the right. I have added no error trapping so if it finds text it falls over. Function CountUp(col As Integer, num As Integer) Numcounted = 0 lastrow = Cells(Rows.Count, col).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, col).Value < "" Then CountUp = CountUp + Cells(x, col).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout Else CountUp = CountUp + Cells(x, col).Offset(, 1).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout End If Next getmeout: End Function Mike "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
I can't understand the logic of how you get 107 & 144 for the 2 columns and
the formatting of the post isn't helping. Post your file here (Its free) with the cells you use to get 117 and 144 highlighted and post the link and let me have a look. http://www.savefile.com/ Mike "vdubluv74" wrote: I think we are getting really close. here is what I saw in the columns [ I ][ J ][ K ] [1 ][ 4 ][ 1 ] [2 ][ 5 ][ 1] [3 ][ 6 ][ 1] [4 ][ 7 ][ 1] [ 5][ 8 ][ 1 ] [ 6 ][ 9 ][ 1 ] [ 7 ][10][ 1 ] [ 8 ][11][ 1 ] [12][ ][ 1 ] [10][13][1] [11][14][1] [12][15][1] [14][16][1] [15][17][1] [0][18][1] [ ][ ][=sum(xxx)] [107][144][13] the new formula would not use the space next to it. Also this spreadsheet gets added to mostly everyday. I use the spreadsheet to track the flights I do, so one row is one flight and at the end of 13 rows is one page in my log book and I was trying to make it easier to have the spreadsheet add all of the numbers from the 13 previous flights thanks for your help "Mike H" wrote: Hi, I can see the logic for the left column(I) i think. because there is a blank you have added the value from the next column. But I can't see the logic or how you get 144 for then next. However, try this function. Alt + F11 to open VB editor. Right click 'This Workbook' and insert module and paste the code below in on the right. You call the code like this =CountUp(n,n) the first n is the column Number (9 for column i) the second n is the amount of numbers you want to count. So =countup(9,13) count the last 13 entries and if there are blanks it counts the column to the right. I have added no error trapping so if it finds text it falls over. Function CountUp(col As Integer, num As Integer) Numcounted = 0 lastrow = Cells(Rows.Count, col).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, col).Value < "" Then CountUp = CountUp + Cells(x, col).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout Else CountUp = CountUp + Cells(x, col).Offset(, 1).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout End If Next getmeout: End Function Mike "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
hi, just uploaded it, my math was a little off. no wonder why!
http://www.savefile.com/files/1808407 thanks "Mike H" wrote: I can't understand the logic of how you get 107 & 144 for the 2 columns and the formatting of the post isn't helping. Post your file here (Its free) with the cells you use to get 117 and 144 highlighted and post the link and let me have a look. http://www.savefile.com/ Mike "vdubluv74" wrote: I think we are getting really close. here is what I saw in the columns [ I ][ J ][ K ] [1 ][ 4 ][ 1 ] [2 ][ 5 ][ 1] [3 ][ 6 ][ 1] [4 ][ 7 ][ 1] [ 5][ 8 ][ 1 ] [ 6 ][ 9 ][ 1 ] [ 7 ][10][ 1 ] [ 8 ][11][ 1 ] [12][ ][ 1 ] [10][13][1] [11][14][1] [12][15][1] [14][16][1] [15][17][1] [0][18][1] [ ][ ][=sum(xxx)] [107][144][13] the new formula would not use the space next to it. Also this spreadsheet gets added to mostly everyday. I use the spreadsheet to track the flights I do, so one row is one flight and at the end of 13 rows is one page in my log book and I was trying to make it easier to have the spreadsheet add all of the numbers from the 13 previous flights thanks for your help "Mike H" wrote: Hi, I can see the logic for the left column(I) i think. because there is a blank you have added the value from the next column. But I can't see the logic or how you get 144 for then next. However, try this function. Alt + F11 to open VB editor. Right click 'This Workbook' and insert module and paste the code below in on the right. You call the code like this =CountUp(n,n) the first n is the column Number (9 for column i) the second n is the amount of numbers you want to count. So =countup(9,13) count the last 13 entries and if there are blanks it counts the column to the right. I have added no error trapping so if it finds text it falls over. Function CountUp(col As Integer, num As Integer) Numcounted = 0 lastrow = Cells(Rows.Count, col).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, col).Value < "" Then CountUp = CountUp + Cells(x, col).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout Else CountUp = CountUp + Cells(x, col).Offset(, 1).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout End If Next getmeout: End Function Mike "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
Hi, just uploaded a better one to explain more.
http://www.savefile.com/files/1808407 So if I add a new flight date and input new take offs and flight hours it will update the latest 13 entries. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
what about doing something like this
if I created a cell to enter what row the last flight was on could a formula calculate the previous 13 cells in that row? so the last flight was on row 474, i enter 474 in cell b1100 and then the formula in cell I1100 adds the cells from I474 to I461 thanks "vdubluv74" wrote: Hi, just uploaded a better one to explain more. http://www.savefile.com/files/1808407 So if I add a new flight date and input new take offs and flight hours it will update the latest 13 entries. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
A couple of formula
http://www.savefile.com/files/1808452 Mike "vdubluv74" wrote: hi, just uploaded it, my math was a little off. no wonder why! http://www.savefile.com/files/1808407 thanks "Mike H" wrote: I can't understand the logic of how you get 107 & 144 for the 2 columns and the formatting of the post isn't helping. Post your file here (Its free) with the cells you use to get 117 and 144 highlighted and post the link and let me have a look. http://www.savefile.com/ Mike "vdubluv74" wrote: I think we are getting really close. here is what I saw in the columns [ I ][ J ][ K ] [1 ][ 4 ][ 1 ] [2 ][ 5 ][ 1] [3 ][ 6 ][ 1] [4 ][ 7 ][ 1] [ 5][ 8 ][ 1 ] [ 6 ][ 9 ][ 1 ] [ 7 ][10][ 1 ] [ 8 ][11][ 1 ] [12][ ][ 1 ] [10][13][1] [11][14][1] [12][15][1] [14][16][1] [15][17][1] [0][18][1] [ ][ ][=sum(xxx)] [107][144][13] the new formula would not use the space next to it. Also this spreadsheet gets added to mostly everyday. I use the spreadsheet to track the flights I do, so one row is one flight and at the end of 13 rows is one page in my log book and I was trying to make it easier to have the spreadsheet add all of the numbers from the 13 previous flights thanks for your help "Mike H" wrote: Hi, I can see the logic for the left column(I) i think. because there is a blank you have added the value from the next column. But I can't see the logic or how you get 144 for then next. However, try this function. Alt + F11 to open VB editor. Right click 'This Workbook' and insert module and paste the code below in on the right. You call the code like this =CountUp(n,n) the first n is the column Number (9 for column i) the second n is the amount of numbers you want to count. So =countup(9,13) count the last 13 entries and if there are blanks it counts the column to the right. I have added no error trapping so if it finds text it falls over. Function CountUp(col As Integer, num As Integer) Numcounted = 0 lastrow = Cells(Rows.Count, col).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, col).Value < "" Then CountUp = CountUp + Cells(x, col).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout Else CountUp = CountUp + Cells(x, col).Offset(, 1).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout End If Next getmeout: End Function Mike "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum last 13 cells in column
great, thanks for your help
"Mike H" wrote: A couple of formula http://www.savefile.com/files/1808452 Mike "vdubluv74" wrote: hi, just uploaded it, my math was a little off. no wonder why! http://www.savefile.com/files/1808407 thanks "Mike H" wrote: I can't understand the logic of how you get 107 & 144 for the 2 columns and the formatting of the post isn't helping. Post your file here (Its free) with the cells you use to get 117 and 144 highlighted and post the link and let me have a look. http://www.savefile.com/ Mike "vdubluv74" wrote: I think we are getting really close. here is what I saw in the columns [ I ][ J ][ K ] [1 ][ 4 ][ 1 ] [2 ][ 5 ][ 1] [3 ][ 6 ][ 1] [4 ][ 7 ][ 1] [ 5][ 8 ][ 1 ] [ 6 ][ 9 ][ 1 ] [ 7 ][10][ 1 ] [ 8 ][11][ 1 ] [12][ ][ 1 ] [10][13][1] [11][14][1] [12][15][1] [14][16][1] [15][17][1] [0][18][1] [ ][ ][=sum(xxx)] [107][144][13] the new formula would not use the space next to it. Also this spreadsheet gets added to mostly everyday. I use the spreadsheet to track the flights I do, so one row is one flight and at the end of 13 rows is one page in my log book and I was trying to make it easier to have the spreadsheet add all of the numbers from the 13 previous flights thanks for your help "Mike H" wrote: Hi, I can see the logic for the left column(I) i think. because there is a blank you have added the value from the next column. But I can't see the logic or how you get 144 for then next. However, try this function. Alt + F11 to open VB editor. Right click 'This Workbook' and insert module and paste the code below in on the right. You call the code like this =CountUp(n,n) the first n is the column Number (9 for column i) the second n is the amount of numbers you want to count. So =countup(9,13) count the last 13 entries and if there are blanks it counts the column to the right. I have added no error trapping so if it finds text it falls over. Function CountUp(col As Integer, num As Integer) Numcounted = 0 lastrow = Cells(Rows.Count, col).End(xlUp).Row For x = lastrow To 1 Step -1 If Cells(x, col).Value < "" Then CountUp = CountUp + Cells(x, col).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout Else CountUp = CountUp + Cells(x, col).Offset(, 1).Value Numcounted = Numcounted + 1 If Numcounted = num Then GoTo getmeout End If Next getmeout: End Function Mike "vdubluv74" wrote: Hi, I need a formula, working with excel 2003, and very new to it. so with those being the last cells this is what I would need the answers to be 107 144 those are the totals from the 3rd row to the bottom. but if you had 1s in the next column and then a formula after the last cells i would need 13 to be the total. 1 4 1 2 5 1 3 6 1 4 7 1 5 8 1 6 9 1 7 10 1 8 11 1 12 1 10 13 1 11 14 1 12 15 1 14 16 1 15 17 1 0 18 1 =sum(xxx) 107 144 13 thanks "Mike H" wrote: Hmm, I suspect were into VB now. Let us assume these are the last cells in columns I & J. What result do you expect and why? 1 4 2 5 3 6 4 7 5 8 6 9 7 10 8 11 12 10 13 11 14 12 15 14 16 15 17 0 18 Mike "vdubluv74" wrote: That worked perfect, but I messed up on what i needed. so here is the updated version find the last entry in a cell and calculate the previous 13 cells. the previous cells may be blank. because i have fomulas in the other cells i would need something like this. if (the last cell in column I is greater than 0) then (take the last cell in column J, next to column I, so if the last cell in I is I10 the formula would use J10. and add the number in the previous 13 cells in that column) wow "Mike H" wrote: Tr this =SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000) This an array which must be entered using CTRL+Shift+Enter and not just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself.. Mike "vdubluv74" wrote: I am looking for a formula that calculates the last 13 numbers in a column that are greater than 0. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
how do I update a column and create new rows for new column cells | Excel Discussion (Misc queries) | |||
Copying a column of single cells into a column of merged cells | Excel Discussion (Misc queries) |