LRS.org



RIPL 2016Excel Tips and TricksFormulasAverage, Median, Minimum and MaximumUseful to understand expenditures, service levels, etc. to plan. Average Formula - click on formula from drop down, or enter the formula manually=AVERAGE(first cell:last cell)QuartilesUseful to establish benchmarks. Quartile formulas – quartile.inc and quartile.excExample uses “quartile.inc” for the formula. "Inclusive" presents the percentile cap (25th, etc.) in the numeric array.Using "quartile.exc" locates the position of the first, second, third and fourth quartiles in the numeric array.If use for this service exceeds Quartile 1 (either percentile cap or specific quartile number), increase resources for service delivery. Quartiles are calculated by dividing the array by one of each quartile (1,2,3,4). -QUARTILE.INC(first cell:last cell, quartile number)=QUARTILE.INC(A2:A12,1)=QUARTILE.EXC(first cell: last cell, quartile number) =QUARTILE.EXC(A2:A12,1)SumsUse to tabulate a column or row of numbers. =SUM(first cell:last cell)=SUM(A2:A11)Changes in Values – Numeric, PercentageCalculating change is important for understanding services and finances. Changes in values can be used to track trends for year to date, same month in the prior year, etc. Changes can be displayed as numeric, currency, or percentage.EXPENSEFY 15-16APPROVEDBUDGET (MAY)FY 15-16 FINAL BUDGETCHANGE Increase/(Decrease)COUNTY/CITIES FUNDSalaries and Benefits 7,740,000 7,743,000 3,000 0.0%Services and Supplies 2,535,700 2,410,700 (125,000)-4.9%Materials/Books 2,820,000 2,820,000 - 0.0%Capital Projects 870,000 1,060,000 190,000 21.8%Deferred Maintenance 50,000 50,000 - 0.0%Cost Allocation (60%) 7,804,320 7,918,320 114,000 1.5%TOTAL? $ 21,820,020 $ 22,002,020 $ 182,000 ?0.8%Inserting CommentsUseful to keep notes about cells. To insert a comment, click on a cell, go to Review tab in navigation banner, Add new comment Trend LinesWhen you want to add a trendline to a chart, you can choose any of the several different trend/ regression types. The type of data you have determines the type of trendline you should use.Start by inserting a line chart from your data. Be sure your data includes an empty column for the future (2016 in this ex.). Right click on the line, and Add Trendline. Trendline reliability: A trendline is most reliable when the R-squared value is at or near 1.00. When you fit a trendline to your data, the graph automatically calculates its R-squared value. This value can be displayed on your chart.Linear A linear trendline is a best-fit straight line that is used with simple linear data sets. A linear trendline usually shows that something is increasing or decreasing at a steady rate.Logarithmic A logarithmic trendline is a best-fit curved line that is most useful when the rate of change in the data increases or decreases quickly and then levels out. Polynomial A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analyzing gains and losses over a large data set. Click box next to “Display R-squared value on chart” as an option ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download