Excel Challenge #2
Over the next two email challenges, we will transform the dataset shown in Fig 1 into final data table format shown in Fig 2, and create the focus pivot table and pie chart (visible by scrolling down in the Play Store Apps – DATASET worksheet).
- Create a new column at the right-end of the table which provides the price of the App (if paid for) in GBP.
- Give the column a relevant name.
- Use the exchange rate of £1 = $1.37 to convert to GBP.
- Wherever there is no price paid, show the entry in this column as a blank.
- Set any total in the column to 2 decimal places.
Stuck on the tasks?
+ Reveal Step-By-Step Instructions
Type ‘Price – GBP’ into the cell directly to the right of the ‘Last Updated’ cell. Hit enter, and it should automatically include that column in the table.
We need to implement a formula which will exchange the USD value into GBP but give no output if the answer is blank.
Furthermore, we must set ‘number’ to 2 decimal places. The formula we should use is:
You can copy and paste that straight in, but read on to understand why we use that formula, and how it works.
Ignore IFERROR for a moment, and let us dive straight in from ROUND. Here, the ROUND function is telling Excel to everything to 2 decimal places, using the highlighted parts below:
Next, we use can IF function. If functions syntactically mean the following:
So in our formula, the logical test is whether @[Price - USD]]=0. The value if true, i.e. what will be displayed if Price – USD is equal to 0, is "" – blank. If the cell in Price – USD is NOT equal to 0, we divide by 1.37, in order to convert it to GBP.
So here, the @ sign is being used to pull in the column Price – USD.
Finally, the formula is contained within an IFERROR function, to trap and handle errors. If the formula were to return an error, we make the cell blank, via the highlighted parts of the cell below.
Sharpen your skills with our range of Excel courses designed to turn you from a table trainee to spreadsheet specialist here >
Enter Your Voucher Code Here
Note: We ask for your billing address for our tax records only. We do not ask for card details for pre-paid items.