You have a time series graph with first actual figures and then planned figures. The actual and plan needs to be graphically separated, that the audience would immediately see which figures are actuals and which are planned ones.
In the graph there is just one line, first part represents the actual figures and the end of he line, planned figures.
Actual figures and planned figures are in different columns, therefore they can have different headers.
I have value in D18, that is the last actual number, but the graph looks better when we have both values in last actual row.
Activate the range B2:C21 and take insert – charts.
Select one chart template.
Actual figures are in graph.
Select B2:B21;D2:D21, take copy like control + C.
Click right mouse button on top of the graph and select paste special.
These selections are ok.
The result.
Maybe a bright red would be a more striking colour for the planned figures.
Click the planned figures and take color under format data series.
If you want to change chart type from the line, take right mouse button and change chart type.
Select combo from left side. Chart type can be defined per series. If you want to have actuals bars and planned figures as line.
It is possible to have two different chart types like bar and line. However, I prefer the same chart type for actual and planned figures.
Click the chart and press plus button, then select chart title. You can edit the title.
Now we have header for the graph.
The legeds blue actual and red planned would clarify the graph.
Click the graph and press plus in the top right hand corner.
Select legend.
Now the graph looks like what I was planning to have. Actual values are in blue line and planned values are in red line. Legend is clarifying which one is which.
Excel is a tool for presenting not only calculations.
You have a sales report, and you need to count an average. That’s easy. However, there is one exceptionally high value in the report, 200. That is not a normal value, therefore the average is higher than normal, because of one high sales figure.
One option is to exclude extreme values in the bottom and in the top. The very high sales values will not be counted but also the low values are not considered, either.
Instead of AVERAGE function use TRIMMEAN.
Here is a sales report. As you can see, the last value is considerably higher than other values.
The average for this report is 61.
We would like to exclude the lowest and highest 10 % from the report, then the 200 value would not be counted. Also, the lowest values would also be left out.
The function TRIMMEAN is simple, there are only two arguments data array and percentage.
When we leave the highest 10 % and the lowest 10 % out of average, the trimmed average is 56,1. That is lower than 61, the traditional average. Leaving out the highest and lowest 10 %, is lowering the average because of 200 value.
We can check manually the TRIMMEAN. We need to count 10 % limits manually. Which values TRIMMEAN is excluding from the data range.
The old PERCENTILE function does not exist anymore, now we have PERCENTILE.INC and PERCENTILE.EXC. Both the functions have two arguments: array and percentile. If we want the lowest 10 %, then the percentile is 0,1. For the highest 10 %, the percentile is 0,9.
Now we have percentile limits. If we use PERCENTILE.EXC values, we exclude the values less than 23,3 and higher than 88,5.
Percentile means that below 23,3 are 10 % of observations and above 88,5 are also 10 % of observations. When we count average for sales between the percentiles, that is what TRIMMEAN is doing, I suppose.
Now we need to mark the values in the middle and leave percentile values out. We need an IF sentence which checks that the value is higher than lower percentile limit and lower than high percentile limit. Then the average is calculated only for the values with X.
We take PERCENTILE.EXC values as benchmark, the X should be next to the values which are higher than 23,3 and lower than 88,5.
Now we are counting average for the values with X next to the numeric value. Then we are counting the average for the values when highest and lowest 10 % are excluded. The expected result is same as with TRIMMEAN.
The average for selected values we can do at least in two different ways.
=AVERAGE(IF(C3:C24=”X”;B3:B24))
When using traditional AVERAGE, we need to have nested IF sentence to check where X is set and calculate the average with X marked values.
=AVERAGEIF(C3:C24;”X”;B3:B24)
Excel also has a modern AVERAGEIF function, where IF is embedded and nested IF is not needed. The only arguments area range for criteria, criteria, and average values.
AVERAGEIF is simpler than AVERAGE with nested IF, but both the formulas calculate the same result.
Let’s see do we have the same results with TRIMMEAN and AVERAGE(IF) with PERCENTILE.EXC/INC.
In B6 we have TRIMMEAN for data range 56,1.
In C and D columns we have X if the value is not in highest or lowest percentile. The sentence in C3 =IF(AND(B3>$F$3;B3<$F$4);”X”;””) and in D3 =IF(AND(B3>$G$3;B3<$G$4);”X”;””). In C column X is marked if the value in between F3 and F4. Those values are counted with PERCENTILE.EXC function. In D column we have X if the values are between G3 and G4. In turn, G3 and G4 are calculated with PERCENTILE.INC.
Once the high and low percentiles are marked without X, we can calculate the averages for the values with X. AVERAGEIF and AVERAGE+ nested IF are returning the same values. There is no difference between these two options.
TRIMMEAN returns the value 56,1. The average for PERCENTILE.EXC is roughly 56,06 and for PERCENTILE.INC is 56,25. Differences between averages are minor. PERCENTILE.EXC is closer to TRIMMEAN than PERCENTILE.INC. No matter which way you calculated the average for selected values, the results are nearly identical. TRIMMEAN is the simplest option.
My Problem Solving Blog 