JUMIA ASSIGNMENT

To find the missing in rating/review
Highlight the review column, click CTRL+H. Find what leave it blank and on the replace option write 0. For the rating column follow the same formula but on the replace option write Not Provided.
On the out of rating column, use find and replace ,CTRL+H out of 5 in the find what then leave blank in the replace option.

• For the old and current price, change them to currency... highlight the columns, head to numbers in the home section turn them to currency.
  ##TO REMOVE THE NEGATIVE SIGN FROM REVIEW
  Use absolute function ABS(cell range of review) or use the if function =IF(e2<0,-e2,e2)

  TO REMOVE DUPLICATES FROM THE DATA

  Click anywhere in the cell, go to data option on the ribbon, click remove duplicates, a box will appear to select the columns to find the duplicates and if its the whole data select all. tick with the header option in the dialogue box then click ok.

  ADD THREE COLUMNS

1. DISCOUNT
   Which is the subtraction of the old price and new price

2. RATING CATEGORY
   Use the formula IF function, =IF(I2="Not provided", "N/A" IF(I2<3,"Poor", IF(I2<=4.4, "Average", "Excellent"))) I2 is the cell name

3. DISCOUNT CATEGORY
   =IF(cell name>40%,"High", IF(cell name>20%, "Medium", "Low"))
   Then find the average of current Price, old price , discount percentage, rating.
   Then find max and min of the current price, old price, discount percentage, rating.
   For the average rating and discount category, create a pivot table; discount on the row, count of rating change on the value beside the rows then change the field value to average
   To find what products are used? form a pivot table of rating(row) then the count of product on the value

   RELATIONSHIPS

   Create a pivot table between Discount%(row) and number of review(values). select the first cell on the pivot table, right click, go to the group option the select by 0.1 which is 10%