OFFSET - Joined or combined ranges

**new22excel** · 08-23-2010, 10:35 AM

I have ranges such as

rrr1 = Sheet1!$a$1:$a$10

rrr2 = Sheet1!$a$21:$a$30

rrr = range(rrr1,rrr2)

ppp = offset(rrr,0,1)

When I do
sum(rrr1), I get correct answer
sum(rrr2), I get correct answer
sum(rrr), I get correct answer

but, when I do sum(ppp), I don't get a correct answer

Is there something wrong with the way I am defining ppp ? The offset function does not seem to work if I have a break in the range. The range "ppp" is not getting defined correctly. Please help..

**new22excel** · 08-23-2010, 11:18 AM

Please help.. I am new to excel, and it is really frustrating.. to spend so much time on some simple tricks..

All I want is to make this damn OFFSET function to work on a range of non-adjacent cells.

**Andy Pope** · 08-23-2010, 11:42 AM

The help on the OFFSET function states.

OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.

**new22excel** · 08-23-2010, 11:50 AM

I was able to find a way around this as follows:

ppp = (offset(rrr1,0,1),offset(rrr2,0,1))

Now a new question is :

If I have say 5 ranges, (rrr1,... rrr5), I define combined range (rrr) as,

rrr = (rrr1,rrr2,rrr3,rrr4,rrr5)

And if say, rrr4, rrr5 are blank.. How can I ignore these ranges while defining rrr

This is because if I keep the expression as it is, while plotting rrr, I get some redundant zeros as the ends of the plots, which represent "blank" ranges rrr4, rrr5..

thank you !!

**Andy Pope** · 08-23-2010, 11:53 AM

It would probably help if you tried explaining what you are trying to achieve.

At a guess I would say you are trying to construct a chart series from multiple sets of non contiguous data. If so you may be better of trying to build a contiguous set of data for chart.

**new22excel** · 08-23-2010, 12:06 PM

Andy, yes I am trying to build a chart from non continuous data

I try to enter the row numbers of the data points between which I want the charts

For example: I enter the row numbers on some cells on a sheet as:

1 20
44 110
214 402
435 654
760 952

Then I create ranges aaa1,aaa2,... aaa5 that read these numbers and creates them.. Then I create range aaa=(aaa1,aaa2,...,aaa5).. and create bbb = (offset(aaa1,0,1),...offset(aaa5,0,1))

Then the excel should plot data between rows 1-20, 44-100, 214-402, 435-654, 760-952 on the same plot, let's say a line plot.

Its doing fine if I have all 5 ranges (row numbers) defined

But, I want to be able to plot data in "up to" 5 discontinuous rows.

Next time, if I enter the row numbers as:
10-400
502-650

Then excel reads blank for ranges aaa3,aaa4,aaa5, and plots zeros instead of not plotting anything.

**Andy Pope** · 08-23-2010, 12:09 PM

pretty hard to tell without example file but why not make the component ranges dynamic.

So rather than setting to a fixed range you would use the OFFSET and COUNT function to return only those with values.

**new22excel** · 08-23-2010, 12:15 PM

The component ranges are dynamic..

The individual ranges.. aaa1, aaa2 are created by using OFFSET and COUNT functions by reading values 1, 20 and 44, 110 respectively.

The problem comes when there are blank cells in the rows that read data for say range aaa3... It plots one point at zero, instead of not plotting anything.

**new22excel** · 08-23-2010, 01:23 PM

Rephrasing the question,

Can I define a dynamic range, that can also be a blank in certain situations?

**Andy Pope** · 08-24-2010, 04:14 AM

If the cell is truely empty then the chart setting for Plot empty cells as will be honoured.
If the cell contains a formula then for non values the formula must return #N/A via the NA() function. This will be an interpolated data point with no marker if a line chart.
Everything else will be treated as zero.