RIPL 2016Excel Tips and Tricks

Formulas

Average, Median, Minimum and Maximum

Useful 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)

Quartiles

Useful to establish benchmarks. Quartile formulas – quartile.inc and quartile.exc

Example 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)

Sums

Use to tabulate a column or row of numbers. =SUM(first cell:last cell)=SUM(A2:A11)

Changes in Values – Numeric, Percentage

Calculating 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.

EXPENSE / FY 15-16
APPROVED
BUDGET (MAY) / FY 15-16 FINAL BUDGET / CHANGE Increase/(Decrease)
COUNTY/CITIES FUND
Salaries 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 Comments

Useful to keep notes about cells. To insert a comment, click on a cell, go to Review tab in navigation banner, Add new comment

Trend Lines

When 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