Offset Function and Blank Cells

**excel-help** · 08-23-2011, 11:02 AM

I have the following simple offset formula:
=SUM(OFFSET(K14,0,0,1,-MIN($D14,COUNT($G14:K14))))/$D14

This formula works great for me as long as I do not add blank columns to my spreadsheet. Once I do, -MIN($D14,COUNT($G14:K14)) includes the blank cell in its count when determining the width to sum and messes up the formula.

Is there any way to adjust this formula such that if blank columns are added, the offset formula will not count the blank cell when determining the width to sum?

(Note: I am trying to calculate depreciation, where row 14 contains the CAPEX and D14 is the useful life.)

**MarvinP** · 08-23-2011, 11:45 AM

Hi excel-help and welcome to the forum,

It could be simple like taking the dollar sign off he last (or both) $D14s.

You might also need to improve the last argument in your Offset formula. Try

=SUM(OFFSET(K14,0,0,1,    MAX(Match(1000,$14:$14,1))      ))/$D14

hth.

**excel-help** · 08-23-2011, 12:31 PM

Thanks MarvinP.

Unfortunately, your suggestion doesn't seem to work for me. Maybe that's because I wasn't explaining myself too clearly. I've attached a file that hopefully explains better what I am trying to accomplish.

**MarvinP** · 08-23-2011, 01:40 PM

Ok - you got me scratching my head.

Try the attached that does't use an offset function. It simply adds them separately. Will this work for you?

**excel-help** · 08-23-2011, 02:06 PM

I wish it would, but it's not dynamic. I don't necessarily need to use the offset function though. Maybe it would help if I asked for a more general formula (which I can then customize on my own, as needed)...

Is there an excel formula which sums the last XX non-blank cells in a specified range (where XX is a number I specify in another cell)... but if there are fewer than XX cells in the range, it would instead sum all the cells in the range?

For example, say the range is F5:K5. All the cells in the range have a value of 2, except for J5 which is blank. Say XX happens to be 3. In this example, the last 3 non-blank cells would be K5, I5 and H5 and the answer would be 6.

**MarvinP** · 08-23-2011, 03:52 PM

Hi,

There are a lot of options for Dynamic Named Ranges that may do what you need. You might also be able to get away with a Named Range that has non toucing cells.

We still need a sample. Perhaps a more general one that the last. Show many of the different kinds of problems with the summing.

**excel-help** · 08-23-2011, 06:02 PM

Thanks for sticking with me. Hopefully this file makes it easier to understand what I need?

**daddylonglegs** · 08-23-2011, 06:39 PM

Try this "array formula" in Q6

=IF(A6="","",SUM(INDEX(C$3:Q$3,LARGE(IF(ISNUMBER(C$3:Q$3),COLUMN(C$3:Q$3)-COLUMN(C$3)+1),MIN(COUNT(C$3:Q$3),A6))):Q$3))

confirmed with CTRL+SHIFT+ENTER and copied down