ExcelBanter - View Single Post

vdubluv74

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