If you want to calculate the average using data that
includes text and numbers, we have to use the AVERAGEA function. This function
converts a text value into a number value. The conversions are below.
Value
|
Conversion
|
Tree
|
0
|
TRUE
|
1
|
FALSE
|
0
|
10
|
10
For
the data above, the formula would do (0+1+0+10) / 4 = 2.75
We can use Calculated Items to merge multiple row items in a PivotTable. For example, you would use Calculated Items if you had data for North, South, East and West Yorkshire and you wanted to merge the data into one Item called Yorkshire. To create a Calculated Item, select a row item and then go to the Analyse tab. On the ribbon click on Fields, Items, & Sets and then choose Calculated Item. In the Name field, type in the name of your new item In the Formula field, type the formula to sum the multiple fields together. For example, = ‘East Riding of Yorkshire’ + ‘North Yorkshire’ + ‘South Yorkshire’ + ‘West Yorkshire’ A quick way to insert a Field Item is to double click on the field you want to insert from the Items box. Finally click ADD and OK and this will apply your new Calculated Item
If you want to find the lowest value in a data set based on one or multiple criteria, then use the MINIFS function. For example, if you wanted to find Dave’s lowest sales figure for a product that has a product ID of 1.1 then I would use the MINIFS function. The syntax for the MINIFS formula is =MINIFS(min range, criteria range 1, criteria 1, criteria range 2, criteria 2,…) Min range – the range of cells you want to find the lowest value in Criteria range 1 – the range of cells that you want to find the criteria in Criteria 1 – the criteria or condition that you want to find the lowest value for Criteria range 2 – the second range of cells that you want to find the second criteria in (Optional) Criteria 2 – the second criteria or condition that you want to find the lowest value for (Optional)
If your dates are grouped into years, quarters, months, etc. in a PivotTable then you can ungroup them. To do this, right click on your header where you have the dates and select ungroup.
If you want to add or remove headers from your PivotTable, click on your PivotTable and go to PivotTable tools tab. Next, select the Design tab and check the Row Headers and Column Headers boxes. You will now see the headers being applied.
If you want to filter using a broader criterion then you can use question marks in your search. A question mark represents a character and you use them within the search bar on your filter dropdown. For example, if you typed 10?4 into the search bar it would return numbers such as 1004, 1014, 1024, 1034 etc.
If you want to work with macros or add form controls to your spreadsheet then you will need access to the Developer tab. The Developer tab doesn’t appear as a default in Excel, so you have to add it to your tabs. To add the Developer tab, go to the File tab and select Options. From the Excel Options window select Customise Ribbon and tick the Developer box on the right. Finally click OK and you will notice the developer tab at the end of your tabs.
If you want to find the highest value in a data set based on one or multiple criteria, then use the MAXIFS function. For example, if you wanted to find Dave’s highest sales figure for a product that has a product ID of 1.1 then I would use the MAXIFS function. The syntax for the MAXIFS formula is =MAXIFS(max range, criteria range 1, criteria 1, criteria range 2, criteria 2,…) Max range – the range of cells you want to find the highest value in Criteria range 1 – the range of cells that you want to find the criteria in Criteria 1 – the criteria or condition that you want to find the highest value for Criteria range 2 – the second range of cells that you want to find the second criteria in (Optional) Criteria 2 – the second criteria or condition that you want to find the highest value for (Optional)
Previous
Next
|