Solved

Using macro to add individual trendline and description per item level in excel for analysis

Posted on 2011-09-16
27
614 Views
Last Modified: 2012-05-12

See attached for detailed instructions and illustrations.

(I was told that MS excel 2003 is unable to perform such macro for trendline embedded per item level in each cell while MS excel 2010 has this capability. is this true ?)

 Trendline-in-excel-EE.xls
0
Comment
Question by:ceneiqe
  • 15
  • 6
  • 4
  • +1
27 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Hello,

In Excel 2010 you can use Sparklines to create the in-cell mini-charts.

First you need to save the file as an Excel 2010 file, with a xlsx or xlsm (if you have macros in the file) extension.
Then close the file and open it again. It will now no longer display in Compatibility mode.

Select cells B2 to E6 and click Insert > Line (in the Sparklines panel).
In the following dialog box select M2 to M6 as the target address for the Sparklines and hit OK.

The result is attached.

Could that be a starting point?

cheers, teylyn
Trendline-in-excel-EE.xlsm
0
 

Author Comment

by:ceneiqe
Comment Utility
how about in excel 2003 ? can it be done ?

sorry i don't have 2010 version so i can't test out your file.
any tip?
0
 
LVL 2

Expert Comment

by:jan24
Comment Utility
I think it can be done in Excel 2003 - you would do it by writing a macro that manipulates the drawing objects on the sheet.  I'll give it a go!
0
 
LVL 50

Accepted Solution

by:
teylyn earned 200 total points
Comment Utility
There are several tools available that can create in-cell Sparklines in Excel 2003.

Bissantz, a German outfit, I believe, offers the SparkMaker
http://www.bissantz.de/sparklines/download.asp

Fabrice Rimlinger came up with the SparkLines for Excel tool years ago
http://sparklines-excel.blogspot.com/2008/11/release-323-for-excel-2003.html

Both are excellent tools to achieve what is now native to Excel 2010.

cheers, teylyn
0
 
LVL 2

Expert Comment

by:jan24
Comment Utility
Ok - well as that's free there's no point rewriting it myself!
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
'zactly!

On top of that, I think this might be a bit more complex than you had anticipated, Jan.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 50 total points
Comment Utility
A bit of trivia, so no points for this.

These add-ins exploit one of the few exceptions to the "UDF cannot manipulate the UI" rule: UDFs can in fact delete/create Shapes on the worksheet, which is how these "sparklines" are being created and managed.

Another resource:

http://www.dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/
0
 

Author Comment

by:ceneiqe
Comment Utility
Hi matthewspatrick,

I went to download "Excel 2003 Add-in " in http://www.dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/

and i have  "Sparklines" add-on in the toolbar.

so i clicked on "LineChart" and then the Function Arguments dialog box pop up.

Function Arguments:
Points1
Points2
Mini
Maxi
HLine

So  for Points1= B2:E2 and then i click OK.

1. What does
Points2
Mini
Maxi
HLine

mean then ? I click on "Help on this function" but it is not available.

2. It works when i select "LineChart" and then select the cells for Points1. (see worksheet "trial")
BUT when i copy the formula from M2 and drag down, I get overlapped lines, then again the formula is right.

2a)How can I get rid of this overlap lines ?  (see worksheet "trial (2)" ) does it mean i have to repeat the :"LineChart" select cells -action over and over again for each line ? This will be too time consuming.

2b) How do i get rid of the green arrows at the top left hand corner ?


Trendline-in-excel-EE-1.xls
0
 

Author Comment

by:ceneiqe
Comment Utility
teylyn/jan24, any idea?
0
 
LVL 2

Expert Comment

by:jan24
Comment Utility
Hi ceniqe,
I followed the link that you sent, but it just seems to be a long blog page.  I'm afraid I can't find any link on it to an "Excel 2003 Add-in".  Can you say exactly where on that link the add-in is which you downloaded?
Thanks
0
 

Author Comment

by:ceneiqe
Comment Utility
0
 

Author Comment

by:ceneiqe
Comment Utility
0
 

Author Comment

by:ceneiqe
Comment Utility
How? anyone ? points willl be allocated.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Have you tried the Bissantz or the Fabrice Rimlinger links I posted?
0
 

Author Comment

by:ceneiqe
Comment Utility

for
Fabrice Rimlinger came up with the SparkLines for Excel tool years ago
http://sparklines-excel.blogspot.com/2008/11/release-323-for-excel-2003.html

see my comment in id : 36556481 and 36564421

for

Bissantz, a German outfit, I believe, offers the SparkMaker
http://www.bissantz.de/sparklines/download.asp
,

0
 

Author Comment

by:ceneiqe
Comment Utility
for

Bissantz, a German outfit, I believe, offers the SparkMaker
http://www.bissantz.de/sparklines/download.asp
,

in process of trying.
0
 

Author Comment

by:ceneiqe
Comment Utility
Bissantz, a German outfit, I believe, offers the SparkMaker
http://www.bissantz.de/sparklines/download.asp

Add-in for Microsoft Office 2000, XP, 2003, 2007, 2010. Requires Microsoft .NET 2.0

i don't have  Microsoft .NET 2.0 so i can't download this.


Actually i am very near to what i am asking. cos i am already getting the trendlines by downloading "Sparklines for excel " via  http://sparklines-excel.blogspot.com/2008/11/release-323-for-excel-2003.html

as long as the questions in  ID: 36556481 are answered, it would be solved.
0
 

Assisted Solution

by:ceneiqe
ceneiqe earned 0 total points
Comment Utility
Refering to my own comments - ID: 36556481, i have just managed to solve qn 1) and 2).

1) i actually refer to Sparklines User Manual in "http://sparklines-excel.blogspot.com/2008/11/release-323-for-excel-2003.html"

2)
a)
just press the "Refresh" button on the sparklines toolbar or "Redraw Sparklines on sheet" in the sparklines menu. This will delete inappropriate shapes

b)
Green arrow is used by Excel for error checking. Excel considers that there is an incoherence between the successive formulas and warns you with this green arrow.
Deactivate it via the menu : Tools > Options > Error Checking and uncheck "Enable background Error Checking"


NOTE: Thanks to Fabrice Rimlinger himself for answers 2a) and b) - via my email to him.

0
 

Author Comment

by:ceneiqe
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 200 points for teylyn's comment http:/Q_27313288.html#36553643
Assisted answer: 50 points for matthewspatrick's comment http:/Q_27313288.html#36554134
Assisted answer: 0 points for ceneiqe's comment http:/Q_27313288.html#36581277

for the following reason:

B because :<br />&quot;B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution. May assign if you needed a bit more information to complete the task.&quot;<br />- Teylyn provides good links but i need to search for further information to complete the task (overcome those overlapped lines after running &quot;Line chart&quot;).<br /><br />Accept my own comments because:<br />it answers my questions completely.
0
 

Author Comment

by:ceneiqe
Comment Utility
sorry, i just saw that there are some unanswered questions. pls cancel request to close the comment
0
 

Author Comment

by:ceneiqe
Comment Utility
when the formula "LineChart (ref.....) " is copied to the remaining cells, the whole excel file hang.
(note: i have about 20,000+ lines)

I can reduce it to about 6000 lines.
I tried copying 1500+ cells and it went ok. and beyond 2000 cells the system would hang.

Is there a way to overcome the "hanging" part ?

0
 

Author Comment

by:ceneiqe
Comment Utility
but the solution is not complete. does that count ?
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
Three different tools were suggested that can create sparklines in Excel 2003. These tools have been around for a long time and have successfully been used by a wide audience.

These tools were not written by the experts who suggested them, so if you need support in applying these tools, it's not the experts here who are responsible to give that support to render your question as "answered".

I uphold my opinion that your initial question has been answered.

cheers, teylyn
0
 
LVL 2

Expert Comment

by:jan24
Comment Utility
ceneige,
I'm very happy to help, but I'm afraid I'm a bit confused as to what is still outstanding.  If you're still stuck just start another thread as teylyn suggests and I'll do what I can to help you fix it.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
ceneige,

in addition to your original question, you tacked on four more questions in your comment http:a#36556481

Please have another look at the EE guidelines for asking questions, especially about asking multiple questions in one thread. http://www.experts-exchange.com/help.jsp?hi=23

Why don't you just accept the solution you received for your original question and then post a related question to work out the rest? You will find that experts are much more willing to help on a new question than dragging this one out until it wears thin.

You have unlimited points to give. Why not use them?

Thanks,

0
 

Author Closing Comment

by:ceneiqe
Comment Utility
as per comments in:
23/09/11 01:36 AM, ID: 36581669
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

14 Experts available now in Live!

Get 1:1 Help Now