- Excel basics (1)
- Creating formulae (5)
- Formatting worksheets (9)
- Basic printing (8)
- Charts (10)
- Absolute references (9)
- Range names (13)
- Conditional formulae (17)
- Conditional formatting (7)
- Basic tables (6)
- Formatting numbers and dates (3)
- Working with dates (1)
- Data validation (6)
- Protection (1)
- Advanced IFs (6)
- Lookup functions (14)
- Advanced lookup functions (9)
- Text functions (1)
- Advanced charts (4)
- Multiple worksheets (1)
- Advanced tables (2)
- Pivot tables (3)
- Advanced pivot tables (3)
- Scenarios (2)
- Data tables (2)
- Array formulae (2)
- Building models (2)
- Masking (2)
- Cashflow calculations (1)
- Investment appraisal (1)
Excel | Advanced lookup functions exercise | Excel 2007 choosing type of celebrity - look up
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
You can learn how to do this exercise if you attend one of more of the courses listed below!
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Different types of celebrity have different earning powers (politicians command less fees than footballers, for instance) - we want to build this fact into our model. As a first step, open the workbook in the folder shown above.
Create a new worksheet listing the different possible types of celebrity and the fee multiplier for each - here is a suggestion:
You can add your own celebrity types if you like ...
Add a new input cell to the Inputs sheet, allowing you to choose a type for your celebrity:
Use data validation to limit the choices to those in the first column of your factors spreadsheet
Amend your revenue calculations to include a celebrity factor:
You need to put a VLOOKUP formula in the cell shown selected
Check that your formula works for different celebrity types, then save and close the workbook.