Using the OFFSET and COUNT functions

Topic

New Reply

I’ve used Excel for some time, but I’ve only used the basic spreadsheet functions (e.g., add, sum, etc.).

First, let me say I’m struggle with the technicalities a bit, but would really appreciate any help explaining the effectof a formula. Based on some research, I’mu sing and OFFSET and COUNT functions. Here’s the formula I have in cell D2 =OFFSET(A1,COUNT(B:B),1). See attached worksheet.

I get the results I’m looking for, I want the last value entered in Column B. Let’s say 12. Then assume on 06/01/2011 I enter a value of 18, then the results in D2 would be 18. The formula works perfect.

Here’s where my issues comes in. I needed to insert a couple of rows for a header, so my Date (Column A) and Value (Column B) were moved to row 3. This changed my results in cell D2.

Why would inserting the 2-rows caused myformula to stop 2-rows above my last value in Column B since my formulaincluded all the rows in Column B (B:B).

It took a little time to figure out, but it was an easy fix; I just adjusted my starting point in the OFFSET from A1 to A3.

I’m just struggling to understand why it would matter if my formula looks at all the values in Column B (B:B)? Why would inserting 2-row cause this formula to stop 2-rows before the last value? I was curious, so I inserted a third row and my formula result moved up another row (the value in D2 is now 10; there are three value after 10 in Column B)?

I’m sure there is an easy explanation and I would appreciate any enlightenment.

Thank you.

Reply | Quote

Replies

The quick answer is that COUNT only counts cells with numbers – not blank cells, or cells with text – just numbers. You might have had numbers in B1 to B20, so COUNT(B:B) equals 20. Now you’ve inserted two rows with B1 and B2 now being blank or containing text and your last value pushed down to B22. But COUNT(B:B) still equals 20 because those first two cells don’t contribute – they presumably don’t contain numbers.

Reply | Quote

bjulien,

Thank you for your comments. I understand the COUNT function; however, in my formula (OFFSET AND COUNT) I’m not counting the number of cells with numbers, I’m getting the value of the last cell in Column B. I can have 50 cells with numbers, but if the last cell (let’s say B51) has a value of 200, then is returned. When I insert the rows, my formula moves up to cell B49.

Reply | Quote

in my formula (OFFSET AND COUNT) I’m not counting the number of cells with numbers[/quote]
Yes you are – the COUNT part does that and the OFFSET uses that as the number of rows to go down starting from from A1. As explained, if you then insert two rows above your data and you don’t have numbers in them, then you don’t move down the correct number of rows from A1. Adjusting the start cell to A3 corrects this.

Reply | Quote

bjulien supplied the explanation. here is a solution. Instead of starting the offset at A1 start it at A3. Now if you insert more rows above A3, the formula will still work (unless of course you add numbers in those inserted rows…):

=OFFSET(A3,COUNT(B:B),1)

Steve

Reply | Quote

FYI, if you want the last number in column B, then:
=LOOKUP(9.99E+307,B:B)
will work regardless of text or title rows.

Reply | Quote

Can you explain what the 9.99E+307 represents in the formula?

Reply | Quote

Can you explain what the 9.99E+307 represents in the formula?

Here is a b it technical explanation:

http://www.mrexcel.com/forum/showthread/?t=310278

Reply | Quote

9.99E+307 is a very large number. Excel continues to Lookup the large number and can’t find any larger so it eventually will give up when it gets to the last value in the column.

Steve

Reply | Quote