Posts by crabmano

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello, I am starting out with more advanced feature of Excel, and I have been trying to solve some problems.


    I believe that it is mostly about Pivot tables, Vlookup, IF function? Please, see attached EXCEL file with the data to better grasp the problem.
    forum.ozgrid.com/index.php?attachment/71642/
    _________________________________


    Q1: Show in one table the number of merchants per sales specialist. Highlight in a scalable way sales specialist with more than 40 merchants
    - Here I'd use pivot table, which is pretty easy. (not sure how to highlight the 40+ merchants though)



    Q2: Modify the raw data table to add category group information to each line. Clarity of formulas will be evaluated (Hint: Search function, data table)
    - No idea how to classify the data here


    Q3: Modify the raw data table to calculate points for each merchant signed. Show in one table the number of point per sales specialist with a category group break down
    - Not sure what to use here and how to create a function
    Q4: How many listings have been obtained for companies that contain the word 'Software' as part of their merchant name?
    - can be done by search function


    Q5: Show in a chart the number of points that have been obtained by sales specialist for all product lines that belong to the category group Hardlines
    - i tried using pivot table, and then Slicers. Though, I don't know how to create a function to identify the ranges (1-19=0,5 points, 20-149=1 point etc.) and classify the ranges


    Thank you in advance for any help with my learning!
    Robert