Today, we want to learn how to add a Target Line to our chart and control it.
How to Add a Target Line to an Excel chart
Sometimes, we want to lower the line, sometimes we want to raise it. But, firs things first.
Let’s start with writing the target. Let’s write 700. Then, we have to create a simple formula without any dollars in the first cell below (Fig. 1)
=C2
Fig. 1 A simple formula
Now, we can copy the formula down. And what’s happened? Each cell is referring to the cell above it. At the end, they are going to the first cell (Fig. 2)
Fig. 2 Each cell refers to the cell above
It means that when we change the value in the first cell, the values in the cells below also change (Fig. 3)
Fig. 3 Values changed
Now, when our data is properly prepared, we can select the whole data, go to the Insert tab (1), then go to Charts and choose the column chart option (2) (Fig. 4)
Fig. 4 Creating a chart
We have a chart but no line. To create one, we have to select the Target series, go to the Insert tab (1), and choose a line chart from the Charts area (2) (Fig. 5)
Fig. 5 Creating a line
When we have the line, we can right-click it and choose the Change Series Chart Type option from the pop-up menu (Fig. 6)
Fig. 6 Changing the chart type
In the window that has appeared, we can change the chart type to any we like (Fig. 7)
Fig. 7 Chart type options
However, we have already chosen the type we wanted. Let’s change the value in the first cell in the Target column. What we can see is that the line has changed its position (Fig. 8)
Fig. 8 Value change
We can also calculate the average of our data using the AVERAGE function (Fig. 9)
=AVERAGE(B2:B13)
Fig. 9 Calculating the average
Now, we have an average line for our data (Fig. 10)
Sometimes, we need to make many statistical operations and use many statistical functions in Excel. How can we do it?
Named ranges and statistics
A fast and quick solution is using name ranges. For example, we have the SUM function that sums up all values from January (Fig. 1)
Fig. 1 SUM function
How can we name ranges and use them for our advantage? Let’s start with a simple example of the SUM function. For the SUM function, we have keyboard shortcut which is Alt + =. This way Excel will put the sum for January, which is a range form B2:B6 (Fig. 2)
=SUM(B2:B6)
Fig. 2 Alt + = to put the sum
In the example above we had numbers. However, let’s go to another sheet where we have no numbers. When we want to sum the numbers from the previous sheet, it’s going to be a very tedious job, as we have to move between those two sheets and write proper values. What’s more, the reference we have while using values form another sheet isn’t the easiest to understand. What we can do is naming the ranges. We just have to select a range in a column and write a proper name in the Name Box (Fig. 3).
Fig. 3 Naming the range
Now, when we are using the Alt + = shortcut under our values, Excel will automatically refer to the January range, which is B2:B6 (Fig. 4)
=SUM(January)
Fig. 4 Automatic reference
As we know, Excel remembers names. It means that we can use the name in calculation on a different sheet. When I start writing “ja” in another sheet, Excel will suggest the range name (Fig. 5). I can just accept it and sum the values from January.
Fig. 5 Excel suggested the range name
As we can see, naming ranges makes our work faster. We can also name a bigger group of columns at one time. Just select the group you want. What’s important, when you’re selecting your data, you have to select the top rows, and then the numbers beneath them. Then, go to the Formulas tab, click on the Create from Selection command and select the Top Row checkbox in the window that appeared (Fig. 6)
Fig. 6 Creating names from selection
Now, after we named our ranges, we can see that the February range is named February, the March range is named March and so on. When we move to another sheet, we can use those names (Fig. 7)
Fig. 7 Excel is suggesting the new names
However, we can go one step further using the reference to names. I have January in cell B1. But, when I refer to cell B1, Excel won’t simply convert the text from cell B1 to a range because it just text. It means that our function won’t add anything. First of all, we have to lock the rows (B$1), then add the INDIRECT function to our calculation. Now, the INDIRECT function changes the text into a reference to the proper range (Fig. 8)
=SUM(INDIRECT(B$1))
Fig. 8 INDIRECT function
Now, when I drag the formula to the right, I will have sums from each individual month (Fig. 9)
=SUM(INDIRECT(F$1))
Fig. 9 Sum from individual months
When I copy the formula one cell lower, I’m just changing SUM into AVERAGE (Fig. 11)
=AVERAGE(INDIRECT(B$1))
Fig. 1 SUM into AVERAGE
After I drag it to the right, I have proper results. I do the same with the remaining rows (Fig. 12)
=MIN(INDIRECT(B$1))
Fig. 12 Changing the remaining names
This way, we can quickly add more statistical operations for many months using proper name ranges and the INDIRECT function.
Today, we are going to count sums and averages with conditions. In our first example, we have a small table where we sell fruit. We want to sum up the whole weight and average weight for a single cell. To sum the values with conditions we can use the SUMIF function. In the first argument, we write the range where we need to check our condition and lock it by pressing the F4 key. In the second argument, which is criteria, we just put a reference to a single cell with the product name. We don’t have to lock the cell because while copying the formula down, we want the fruit names to change. The third argument of the function is the sum range. It is closed in square brackets which means that it is not necessary write it, however we will do it. We want to sum values from the Weight column. Then the F4 key to lock it (Fig. 1).
SUM and AVERAGE with condition — SUMIF and AVERAGEIF functions
=SUMIF($B$2:$B$10,F2,$C$2:$C$10)
Fig. 1 SUMIF function
After all arguments have been written, let’s look at our ranges and try to understand how the SUMIF function works. The function will look at each cell in the Product column and check if they meet the conditions. In the first cell, we have apples, which means that the condition is met. In such a situation, the function will keep on looking for the third argument placed in the same row. If it finds it, it will add the value. If there is another cell that meets the same condition, it will also add the value from the same row (Fig. 2).
Fig. 2 Finding conditions
Our formula looks as follows (Fig. 3).
=SUMIF($B$2:$B$10,F2,$C$2:$C$10)
Fig. 3 SUMIF formula
After entering and copying down, we can see how many apples, pears and oranges we have sold (Fig. 4).
Fig. 4 Results
Now, let’s try to count the average weight of a single sale. We just have to copy our SUMIF formula in the edit mode and paste it into the cell in the Average column. The SUMIF and the AVERAGE functions have the same arguments. We just change the function name. We can see that the name of the third argument changed from sum range into average range, however it is still the same Weight column (fig. 5).
=AVERAGEIF($B$2:$B$10, F2,$C$2:$C$10)
Fig. 5 Name change
After entering and copying it down, we have our results (Fig. 6).
Fig. 6 AVERAGEIF counted
Let’s move on to our second example. We want to check here what would happen if we give the AVERAGEIF or SUMIF function just two arguments. In this example, we just want to count the average sale. We are starting with a simple AVERAGE function without conditions, so without IFs, for the Sales column (Fig.7).
=AVERAGE(C2:C11)
Fig. 7 Simple AVERAGE function
After entering it, we have the average (Fig. 8).
Fig. 8 Average
Now, let’s count the average with a condition. We don’t want to count the value if it equals 0. Let’s write the AVERAGEIF function, select the same range and write the condition. We open double quotes and write <>0 and close it. When we don’t write the third argument in the AVERAGEIF or SUMIF functions, Excel takes the first range as the range in which we are doing our calculations. In this situation Excel doesn’t take empty cells and cells with 0 into calculating the average. After entering it, we can see different results. It’s because our condition was triggered in the AVERAGEIF function, which means that Excel did not count the cells with 0 (Fig. 9).
Fig. 9 Different results
When we delete the value with 0, we can see that the AVERAGE and AVERAGEIF function return the same results. It’s because functions such as AVERAGE, SUMS, MIN and MAX do not consider empty cells (Fig. 10).
Fig. 10 Results without empty cells
However, when we put 0 into the cells, the AVERAGE function will count it which means that the average will go down (Fig. 11).
Fig. 11 Results with zeros
Summing up, a simple AVERAGE function counts cells with 0, and the AVERAGEIF function doesn’t due to the condition.
In Excel, in most situations, empty cells are treated as 0.
