Problems in Graph using offset !!

**themaverick2209** · 11-12-2012, 08:19 AM

Hello All,

I have created a horizontal bar graph which has 5 bars in descending order. It is suppose to be a dynamic chart and the number of bars will vary.

In excel, I am facing this problem wherein, the the bar appears in descending order in excel only if they are arranged in ascending order in the excel.

Here is the problem : In excel, I need to arrange the values in cells in ascending order so if my values count reduced from 5 to say 4, I need to make the chart dynamic using offset but in the opposite direction.

To be more precise: for example -

=OFFSET(C2,2,0,count(C2:C10),1)

here, instead of '2' i need to move it in the opposite direction but what should i mention & how. i have tried negative value but it isn't working?

many thanks in advance!

**taps** · 11-12-2012, 09:14 AM

Hi,

Suppose, your chart data is starting in A1 cell (ie Header1) and B1 contains Header2.

Then, create a name range for Value range like this...

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

Say the name of the range is YAxis.

Now, Edit the series data.
Type "=Book1!YAxis" in Series value (here Book1 is the workbook name)

Regards
taps

**themaverick2209** · 11-12-2012, 10:34 AM

Dear Taps,

Many thanks for your reply.

I still have slight confusion as to whether I was able to explain the problem correctly or not!

What I have done is I have attached an excel so that I can explain the problem better. May I request you to either answer the following question or else work on the excel (if possible) to provide a solution.

Anyone in the forum is welcome to work on the excel attached and answer the following questions:

1. I have to make sure that my bars are in descending order.

2. How do I make sure that using offset, the bars will still be in descending order as shown in the right graph ?

**Andy Pope** · 11-12-2012, 10:45 AM

CHTLABEL: =OFFSET(Sheet1!$N$1,COUNTA(Sheet1!$N$2:$N$6)-COUNT(Sheet1!$O$2:$O$6)+1,0,COUNT(Sheet1!$O$2:$O$6),1)
CHTDATA: =OFFSET(CHTLABEL,0,1)

revised series formula.
=SERIES(,'Offset in the reverse direction.xlsx'!CHTLABEL,'Offset in the reverse direction.xlsx'!CHTDATA,1)

**themaverick2209** · 11-12-2012, 02:10 PM

Hi Andy,

Thanks for your time!

I didn't understand where to use the following formula and also how is it possible to use "=series" (I know this is a silly question

: but it would be really kind of you to clarify this)

revised series formula.
=SERIES(,'Offset in the reverse direction.xlsx'!CHTLABEL,'Offset in the reverse direction.xlsx'!CHTDATA,1)