Is there a formula that returns last row number containing data?

[JCinPA]

I am not a programmer, and I guess not even a 'power user' of Excel worksheet navigation functions, but I am looking for an easy way to update ranges in formulae as new data is added to a sheet containing monthly values. Here are two examples:

I have a cell, named Months, where I manually plug in the number of months of return data I have and use it to calculate the number of periods for a rate calculation. Like this.

=RATE(Months/12,0,-1,F141)

At the end of the calendar quarter, I will add three more rows to the worksheet. Right now the cell labeled Months contains the value 110. When I add the three new rows, I will manually change that value to 113, and then I will manually edit the RATE formula so the last argument is F144.

I think there is a way to use built-in functions to sense the last row which contains data and to automate these for me. It's not hard to do manually, but I am checking a lot of formulae and making a lot of manual changes, which as everyone knows is error-prone.

Currently my monthly data starts in row 32 and ends in row 110. It will always start in row 32, but the bottom will increment by 3 every quarter. Here is an example of another formula I have to manually edit every quarter.

=STDEV(B32:B141)*3.4641

Here, the 32 row will always be static, but the B141 will become B144, B147, and etcetera.

Thanks for any help you folks can offer. I appreciate it!

John

[tlafferty]

I'd suggest naming the F column range of data, then doing some math like:

Please Login or Register  to view this content.

The math will come in if the first row of data doesn't begin with F1. You will need to add that number to the end of your formula to account for the start of the data. In the case above, the assumption is that your data begins on row 2, and that row 1 is for your labels/field names. If your data begins on row 3, your formula will end in +2, for row for it will be +3, etc.

[ConneXionLost]

Have a look at Dynamic Named Ranges.

Cheers,

[JCinPA]

Ooooh, Dynamic Named Ranges looks very promising! I knew there were automated tools for this in Excel.

Thanks, ConneXionLost, I will report back if that solves my issues. Preesh!

John

[JCinPA]

Aha! I solved my first issue with the COUNTA function, thusly ...

Into the cell I have named Months, I inserted the following, which will take me out to 50 years, and I won't hardly last that long!

=COUNTA(A32:A631)

I tested it and it counts properly as I add months (rows). So, first issue is settled. The second still remains. I don't need to define an expanding range, I need to point to whatever value is in the last row of a column which contains data. For example:

Right now my Rate Formula looks like this (through June, 2011 monthly data)

=RATE(Months/12,0,-1,F141)

My 'Months' issue is solved with the COUNTA function. However, at the end of September I will need that F141 to change to F144, because row 144 will be the last with data in it in column F. I don't need to know how many rows contain data (like count) and I don't need to specify a range of rows in that column that expands, I want to retrieve the actual value which will reside in cell F144 at that time.

I suspect tlafferty is aiming in the right direction, but I am not smart enough to figure it out.

Am I making any sense? I feel like I may not be articulating this well. For that RATE formula, I need Excel to intelligently go and retrieve the value in the last row in column F that contains data, wherever row that is.

My last issue does involve an expanding range, but I think I am on the right path to a solution with ConneXionLost's link. I'll come ask about that later if I get stuck. But I need to know how to intelligently increment that F141 cell row reference.

Thanks!

John

[scottylad2]

Substitude F41 with =Lookup(9.99999999E+307,F1:F5000) this will return the last numberic entry in volumn F.....does that help?

PS if it's numberic the formula can be adapted to allow for that

[JCinPA]

No, I get an error message when I do that, scottylad2. However your post did cause me to investigate the LOOKUP funtion. I think you are aiming the right way, but there is a problem. Here is what I found on LOOKUP ...

Syntax for Array Form

LOOKUP(lookup_value,array)

- Lookup_value is a value that LOOKUP searches for in an array. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

o If LOOKUP cannot find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

o If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

- Array is a range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

If I understand that correctly, you are trying to specify a huge number, and it will only return the last value in that column if the array values increase monotonically. If in my example, the value in cell F141 is actually larger than the value in cell F144, this will return the value in cell F141. Am I wrong about this?

What I need is for this thing to see that there is data in F144, but NO DATA in F145 and intelligently return the value in cell F144. I need the value in the bottom cell containing data, it may not be the largest or smallest in that column. Make sense?

Thanks, by the way. Maybe you are right, and I am misunderstanding, but in any event my formula will not take your fix, it is rejected.

Thanks.

John

[JCinPA]

I found the answer here:

http://excel-tips.blogspot.com/2009/...in-column.html

Here is my new formula

=RATE(Months/12,0,-1,VLOOKUP(9.99999999999999E+307,F:F,1))

Eureka!

Scottylad2, I was getting the error message because you cannot put the "=" in front of a function within another function, apparently. I still think your version would have returned the largest number in the array, but I think I am good to go now.

Thanks to everyone! Great forum!

John

[scottylad2]

I copied and pasted the formula direct from a cell, thats why the = were still there. The formula i posted will return the last numeric in the column, regardless of whether it's the biggest or smallest

[JCinPA]

OK, this last part is going to be easy, I think, at least for you braniacs.

Here is a current formula in my workbook:

=STDEV(B32:B141)

Now I do need a dynamic range, and I'm wondering if I cannot combine my COUNTA result with the top cell -- B32 -- to create a dynamic range here. In other words, rather than 'feeling' for how big the range is, maybe I can tell it using the COUNTA value?

I'm going to Google up some Dynamic Range pages and I'll probably stumble onto it eventually. If anyone wants to put me out of my misery, though, feel free to jump in!

Thanks, you all got me looking in fruitful directions for sure.

When I add three more months of data, by the way, my COUNTA result will be 113 and that formula should be:

=STDEV(B32:B144)

Maybe that helps someone visualize how to combine these???

Thanks,

John

[JCinPA]

I copied and pasted the formula direct from a cell, thats why the = were still there. The formula i posted will return the last numeric in the column, regardless of whether it's the biggest or smallest

OK, cool. Apparently there are several ways to get there. I just did not know I had to drop the "=".

Shows what a knucklehead I am. Thanks, Scottylad2!

[scottylad2]

=STDEV(B1:INDEX(B1:B500,COUNTA(B1:B500))) this will give you the Stdev from B1 to the last cell containing a numeric as long as there's no blanks

[JCinPA]

Woot! That is perfect!

Scottylad2, you, sir, are a mensch! If you were closer, I'd buy you a scotch whisky, but I suspect you can find better stuff there, anyway.

I appreciate the help, gents!

John - Chicago, IL

[JCinPA]

Thank you all! Mission accomplished. I learned a lot, too!

Output from my workbook, based on investment returns and other calculations:

Tot Ret 13.10%
Std Dev 22.76%
Beta 1.02
Alpha 6.29%
Sharpe 0.49
T.E. 8.42%
I.R. 0.75

And the formulae you all helped me with counting, returning last values in a column, and using dynamic ranges. I know I could put in a formula in place of the the "Months" cell, but it serves other purposes I found useful.

Tot Ret =RATE(Months/12,0,-1,VLOOKUP(9.99999999999999E+307,F:F,1))
Std Dev =STDEV(B32:INDEX(B32:B631,COUNTA(B32:B631)))*3.4641
Beta =SLOPE(B32:INDEX(B32:B631,COUNTA(B32:B631)),$D32:INDEX($D32:$D631,COUNTA($D32:$D631)))
Alpha =(1+AVERAGE(N32:INDEX(N32:N631,COUNTA(N32:N631))))^12-1
Sharpe =(B23-$E23)/B24
T.E. =STDEV(J32:INDEX(J32:J631,COUNTA(J32:J631)))*3.4641
I.R. =B26/B28
Correlation =CORREL(B32:INDEX(B32:B631,COUNTA(B32:B631)),C32:INDEX(C32:C631,COUNTA(C32:C631)))


Much more better! I'm a lot less worried about this workbook getting glitched up now.

Thanks again, gents!

John

[JCinPA]

Last question, I promise.

I have all data for my charts in contiguous columns, x-axis in column 1, series data in columns 2, 3, etcetera. The first row contains series names. Here is an example from the Select Data dialog box.

Chart data range: =Calculations!$M$31:$P$141

Column M is the x-axis data, columns n, o & p are series data, and series names are in row 31. The anchor will be M31, the lower right corner of the range will go down, to P144 in the next quarter.

Thansk!

[ConneXionLost]

...and the question is...?

[JCinPA]

I'm trying to figure out how to make my chart update automatically as more rows are added. One way I've read is to make a dynamic range name and put that in the chart data source, but it says the chart will not automatically update for some reason without my doing some Basic code which is way over my head.

Right now I have all my formulas modified so they will update automatically and I am delighted. Now I have three charts and I have to go in and move the lower right corner of the range manually, in this case it will be from P141 to P144.

My question is what is the best way to make the chart data source change automatically?

[scottylad2]

You didn't actually post the last question....?

[scottylad2]

I added my last comment before refreshing my page,,,,,schoolboy error!
just confirming it's a dynamic chart you now need to close off? i've attached one, pay attention to the names in the name manager, the sheet name needs to be included in the chart data seletion dialogue and be sure to make the data source area much bigger than you'll ever need. The rows in greem are where to add new data and you'll see how it updates, hope its a pointer in the right direction

[JCinPA]

Good grief!

The my current Chart data range is: M31:P141. When I add three more rows of data the chart will not show it. I will have to go manually change the Chart data range to: M31:P144. How can I make that dynamic so I do not have to manually update the Chart data ranges for all my charts. I can't ask it any clearer than that, sorry. That is the question.

What I did now was create a Dynamic named range called DynamicRangeCht1 here is the formula:

=OFFSET(Calculations!$A$31,0,0,COUNTA(Calculations!$A:$A),3)

This is for a chart with x-axis in column A and data in columns B - C. I am trying to figure out how to use this in the Chart data range: dialog box now. My charts are on separate chart tabs.

[JCinPA]

Ooops, scottylad2, looks like we were posting at the same time. I will read your reply now, thanks ...


John

[JCinPA]

Hmmmm. I created a dynamic range name for the entire array of chart data and you created separate dynamic range names for each series.

Do I need to do it separately like you did? Or will my formula in the Chart data range: box likely work, too?

I'm guessing there is a reason you created three separate dynamic range names, as I am not nearly as clever as you.

I very much appreciate the help you have provided, by the way. This is fantastic for lunks like me.

John

[scottylad2]

The reference in the select data dialogue will throw up an error message when you try refering to anything thats not a single Cell Row orColumn

[JCinPA]

Got it. I am mimicking your structure, trying to port it to my workbook. I really appreciate all your help. I gotta find a course in this stuff!

Will report back on success shortly.

[scottylad2]

Your in one of the best courses on the planet if you want to learn Excel, some of the "teachers" in this forum are truly immense in their Excel knowledge, and if you structure your question the right way there are always happy to offer a solution.

And they do it all for free. Enjoy the class!

[JCinPA]

I am enjoying this, but I am like a monkey mimicking a trainer. I've copied your formulae and am constructing mine in Word right under yours, being sure to follow your example carefully. I'm going to get this project done, but I would be hard pressed to do another one. My level of understanding is pretty low.

I catch on quick, though!

[scottylad2]

Once you have created the dynamic names, a tip to helping you get the syntax correct is to create your chart using the data you have and selecting the data as is. Then go into the select data dialogue, and click edit on one of your data series, where you get the reference to the data thats there now, highlight only the cell ref (leave the sheet name and ! then hit F3 and paste the dynamic named range for that colummn where you just deleted the cell refs

[JCinPA]

Silly question, but I have the named ranges built, but I cannot figure out how to get them into the Series values: dialog!

I've tried with the = without the = with the workbook name, without the workbook name. I keep getting an error message.

I'm tearing my hair out! I am sooooooo close, I know it. >:

[JCinPA]

Scottylad2, here is your formula I copied from your workbook for the first series, QuartOne.

=OFFSET(Sheet1!$E$3,,,COUNT(Sheet1!$E$3:$E$12))

Your data starts in E3, my data starts in A32. Your name is in E2, my name is in A31. I can put in cell A31 for the name reference, but here is my modified formula for my Dynamic named range

=OFFSET(Calculations!$B$32,,,COUNT(Calculations!$B$32:$B$631))

Obviously where you have Sheet1 I have Calculations, but otherwise I'm sure this is correct, since I copied it to Word and edited it there.

The name of the range is DynamicCht1OtherData

I cannot put that into the Series Values to save my life. Constant error message. The named range seems to work properly. I am stumped. So close!

John

[JCinPA]

I missed your post #27, sorry, we're running over each other here, I just spotted that.

[JCinPA]

That was it! Post #27 got me over the goal line!

Thank you sir! I toast you ...

http://www.thebreweryoutlet.com/file..._brilliant.jpg


Thank you all very, very, very much.

Now I have to stop being a monkey and learn about this stuff in depth. But at least my problem is solved for now!

John

[scottylad2]

Glad to help