1 / 65

04. Excel Countif and Vlookup

04. Excel Countif and Vlookup. File -> Open -> 04b-datastart.xlsx. Click on new tab icon to insert new sheet. Conditional Formatting. Select Sheet1. Select Column G. We want to highlight any cells with a P/E greater than 15. Select Home -> Conditional Formatting.

ronia
Download Presentation

04. Excel Countif and Vlookup

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 04. Excel Countifand Vlookup

  2. File -> Open -> 04b-datastart.xlsx

  3. Click on new tab icon to insert new sheet

  4. Conditional Formatting

  5. Select Sheet1

  6. Select Column G

  7. We want to highlight any cells with a P/E greater than 15

  8. Select Home -> Conditional Formatting

  9. Format cells greater than 15

  10. Click OK

  11. Any P/E greater than 15 is highlighted

  12. Challenge • Highlight any cells with a Price less than 50

  13. Count, Sum, AverageMax, Min

  14. Insert new sheet

  15. Want to find summary stats for Market CapType in the following headings

  16. Click Formulas -> AutoSum -> Count Numbers

  17. Inserts the COUNT function

  18. Tell Excel what column you want to count:Go to Sheet 1 and select Column E

  19. Select Column E

  20. Press Enter on keyboard

  21. There are 2496 companies with market capitalisation information in dataset

  22. Select next cell

  23. Select Formulas -> AutoSum -> Sum

  24. Tell Excel what to Sum:Select Sheet1 then Column E and press Enter

  25. Have now calculated the total market capitalisation of all 2496 companies

  26. Challenge • For market capitalisation calculate: • Average • Maximum • Minimum

  27. Countif and Sumif

  28. We sometimes want summary statistics for different groups e.g. countries

  29. Type in these headings

  30. Select Cell D8, for Countif DE

  31. Select Formulas -> More Functions ->Statistical -> COUNTIF

  32. For Range choose Sheet1 Column D to count the number of observations per country

  33. Click Column D

  34. Click Criteria

  35. For Criteria choose the cell with the country name

  36. Click OK

  37. There are 683 companies from Germany (DE)

  38. For cell E8 select Formulas -> Math & Trig -> SUMIF

  39. For Range choose Sheet1 Column D

  40. Select Column D

  41. Click Criteria

  42. For Criteria choose the cell with the country name

  43. Click Sum_range

  44. For Sum_Range choose Sheet1 Column E as it contains market capitalisation

  45. Select Column E

  46. Click OK

  47. Calculates the total market capitalisation of companies based in Germany

  48. Challenge • For France, Britain and Ireland use: • Countif to calculate number of companies in each country • Sumif to calculate the total market capitalisation of these companies in each country

  49. Vlookup

  50. Select Sheet2

More Related