Solved

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

Posted on 2011-09-16
27
625 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 6
  • 4
  • +1
27 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36553260
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
ID: 36553600
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
ID: 36553618
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 200 total points
ID: 36553643
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
ID: 36553655
Ok - well as that's free there's no point rewriting it myself!
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36553726
'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
ID: 36554134
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
ID: 36556481
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
ID: 36560340
teylyn/jan24, any idea?
0
 
LVL 2

Expert Comment

by:jan24
ID: 36562388
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
ID: 36564421
0
 

Author Comment

by:ceneiqe
ID: 36564425
0
 

Author Comment

by:ceneiqe
ID: 36578334
How? anyone ? points willl be allocated.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36579057
Have you tried the Bissantz or the Fabrice Rimlinger links I posted?
0
 

Author Comment

by:ceneiqe
ID: 36580667

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
ID: 36580671
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
ID: 36580752
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
ID: 36581277
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
ID: 36581669
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
ID: 36581670
sorry, i just saw that there are some unanswered questions. pls cancel request to close the comment
0
 

Author Comment

by:ceneiqe
ID: 36597132
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
ID: 36597280
but the solution is not complete. does that count ?
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36597290
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
ID: 36598250
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:Ingeborg Hawighorst
ID: 36598295
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
ID: 36895835
as per comments in:
23/09/11 01:36 AM, ID: 36581669
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

749 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