You are here:

Excel/In need of Trendlin analysis

Advertisement


Question
Hi Nathan,

I am in need of a trend analysis for below listed criteria where the "Collections %" should be used as Trendline. Please advise me how to go about this.

Months   Charges   Collections   Collections
October  750,000 350,000   36
November 650,000 400,000   32
December 700,000 450,000   29


Thanks in advance,
Gary

Answer
Gary:

You will need to perform several steps to add this "trendline".  Please note Excel has a built-in trendline function; however, it is not the same kind of trendline you are talking about.

1# First step is to add a column to calculate the Collections %.  I placed this in column E and a sample formula is as follows:

=C2/B2

It takes the collections $ amount and divides it by the total charges $ amount.

2# The second step is to create a graph of your choice.  I selected a bar graph showing the months along the bottom with the collections and charges as two elements being shown.

3) The third step is to plot the data in the Collections % column along a secondary axis.

a) Using the chart functions add a new series of data.  Select column E.  Once this is added it will appear to be in the chart but you won't actually see anything #because the amount is so small compared to the larger collection and charge values#.
b) From the chart tools tab, layout, select the "series collection %" from the drop-down box #upper left-hand corner of the menu bar#.  This will select an "invisible" area on your charge.
c) Select format selection from the menu options.
d) Select plot series on secondary axis.
e) There will now be three large bars in the middle of your graph.  Right-click on one of those bars and choose change series chart type.
f) I opted for a line.

You will now have a "trendline" showing the percentage of collections.

I have uploaded my sample file to the following location:

https://www.dropbox.com/s/1yvgof3qgpamqqj/ForGaryTaylor.xlsx?m

If this answers your question please remember to donate.  I spent about 30 minutes on this answer!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Nathan

Expertise


This expert requires a donation of $5 for questions answered (satisfaction guaranteed). Isn't $5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released.

Education/Credentials
Certified Public Accountant (CPA)

©2016 About.com. All rights reserved.