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


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
ceneiqeAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
ceneiqeAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
jan24Commented:
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
 
jan24Commented:
Ok - well as that's free there's no point rewriting it myself!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
'zactly!

On top of that, I think this might be a bit more complex than you had anticipated, Jan.
0
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
ceneiqeAuthor Commented:
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
 
ceneiqeAuthor Commented:
teylyn/jan24, any idea?
0
 
jan24Commented:
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
 
ceneiqeAuthor Commented:
0
 
ceneiqeAuthor Commented:
How? anyone ? points willl be allocated.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Have you tried the Bissantz or the Fabrice Rimlinger links I posted?
0
 
ceneiqeAuthor Commented:

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
 
ceneiqeAuthor Commented:
for

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

in process of trying.
0
 
ceneiqeAuthor Commented:
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
 
ceneiqeConnect With a Mentor Author Commented:
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
 
ceneiqeAuthor Commented:
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
 
ceneiqeAuthor Commented:
sorry, i just saw that there are some unanswered questions. pls cancel request to close the comment
0
 
ceneiqeAuthor Commented:
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
 
ceneiqeAuthor Commented:
but the solution is not complete. does that count ?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jan24Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
ceneiqeAuthor Commented:
as per comments in:
23/09/11 01:36 AM, ID: 36581669
0
All Courses

From novice to tech pro — start learning today.