Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-16
27
Medium Priority
?
637 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
26 Comments
 
LVL 50
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 800 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
ID: 36553726
'zactly!

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

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 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: 36578334
How? anyone ? points willl be allocated.
0
 
LVL 50
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
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
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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.

879 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