Solved

how to make area chart go vertically from current value to 0?

Posted on 2014-03-12
5
217 Views
Last Modified: 2014-03-16
hi guys, i'm using an area chart and i would like the value to go from 50 to 0 in a vertical drop, not 50 then slide down to 0 over the span of an interval. how can i do this?

perhaps this is better illustrated in this picture below.excel area chartGraphs.xlsx
0
Comment
Question by:developingprogrammer
  • 2
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39922801
Hi,

You can do it by using Named Range

See rngInitiation

OFFSET(D3;0;0;1;COUNTA(Graphs!$D$3:$AA$3))

Open in new window

for reference
http://support.microsoft.com/kb/183446/en-us

Regards
Copy-of-GraphsV1.xlsx
0
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 250 total points
ID: 39924104
Rgonzo1971,

There is a small error on your dynamic range formula.

Have to change from
OFFSET(D3;0;0;1;COUNTA(Graphs!$D$3:$AA$3))

Open in new window

to
OFFSET(Graphs!$D$3,0,0,1,COUNTA(Graphs!$D$3:$AA$3))

Open in new window

to lock the reference range of D3; otherwise, it will keep hopping all over the place.

Try to download your own uploaded file and see what had happened to the graph. The dynamic range reference is hopping all over the place and the graph keeps getting strange ranges and will not show proper series.
0
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 39924190
@Harry Lee

Tks sometimes I forget to correct due to my localization

What do you mean by hopping all over the place? When I downloaded the file it was OK.

Regards
0
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 250 total points
ID: 39924542
When you have a unlocked Offset reference in Named Range, the reference range will keep changing depends on where your active cell (the cursor) is, when you open the Name Manager or refresh the chart.

I think it's a bug of Excel but I'm not so sure.

When I download the file and open with Excel, the content looks like the attached image.

The cursor is at C30, and the Named Range formula is like
=OFFSET(Graphs!D22,0,0,1,COUNTA(Graphs!$D$3:$AA$3))

Open in new window

When I move to cursor to Y12, and open the Name Manager window again, the formula will change to
=OFFSET(Graphs!Z4,0,0,1,COUNTA(Graphs!$D$3:$AA$3))

Open in new window

(Please See the attached Word Document.)

That's what I mean by hopping all over the place. The only way to prevent it happening is to lock the offset range using $.
Copy-of-GraphsV1-3.pdf
Dynamic-Named-Range-Hopping-Samp.docx
0
 

Author Comment

by:developingprogrammer
ID: 39933363
Hi Rgonzo and Harry, thanks for your help! and apologies for the slow reply.

i've used the offset formula before for graphs but i put it in a separate table and i only used it to offset a fixed sized cell range - i've not used it before to expand and contract the cell range. this is my first time doing so and definitely i've learnt = )

i was thinking that if i could once again expand and contract the range in a separate table on the worksheet that may be easier as i can see the values instead of using a named range, but then i realised that the graph needs to be set to either a range of cells or a named range as you shared, and only a named range can have its size expanded and contracted like you showed. so definitely named ranges are the only way to go for solving this problem.

thanks once again guys, very much appreciated!

P.S. and thanks Harry for pointing out the locking of the reference cell = )
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
First Blank Cell in a range 7 35
macro for each dropdown 15 46
Excel callender with date slider 5 27
Excel Hyperlink Question 4 31
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now