How to have excel automatically detect the raw number in formula?

**kunimunt** · 08-23-2009, 05:15 AM

Hi guys!

OK, I have no idea how to explain this, but here is my situation:

1. I got a bunch of data sets containing tons of data. Each set has a different number of data
2. I would like to copy and paste all these raw data into Microsoft Excel so that I can calculate some stuff
3. One of the calculations I am trying to do requires me to grab the LAST 12 numbers of the set
4. Each dataset is independent

Now here is my problem. Since each data set has a different number of entries, I cannot specify the cells when I am trying to grab last 12 numbers. I cannot just copy and paste the last 12 digits manually since some numbers before that actually play some roles later on in the calculation.

My intuitive attempt to solve this was:

1. Count the total number of entries in a dataset using "=COUNT()"
2. Then, here comes my crazy equation which was intuitive, but of course did not work. Let's say I am trying to sum the last 12 numbers, and all data are in the column A. Maximum number of entries in each data set is 1000, and the number starts from cell A1:

If all dataset had exactly 1000 entries, then that's just super simple. It'd be like:

=sum(A989:A1000)

BUT!! Since the total number of entries is different for each dataset, I decided to incorporate the count function... Like this mess below:

=sum(A(count(A1:A1000)-11):A1000)

Since I do not know which cell exactly the last number will be placed, I did some mess like that above. All I want to do is to have excel automatically figure out the correct starting row.

Can you guys understand what I'm trying to do here...? PLEASE HELP!!

**DonkeyOte** · 08-23-2009, 05:23 AM

So are you saying you have blanks interspersed amongst the numbers - or are the numbers consecutive only the dataset may not have 1000 values, eg: only A1:A200 is populated ?

If the numbers are consecutive then you're on the right lines... eg:

=SUM(INDEX(A1:A1000,COUNT(A1:A1000)-11):A1000)

You can leave the end as A1000 given you know your starting point is ensuring only 12 numbers are being included (ie remainder are blank and won't impact summation)

If you have blanks interspersed this become more complex of course.

On an aside, if the values are consecutive (as assumed above) you could also use OFFSET, ie

=SUM(OFFSET(A1,COUNT(A1:A1000)-12,,12,1))

but although perhaps simpler to follow OFFSET is a Volatile function and so when used in large volume or with large data ranges can bring unwanted calculation overheads.