About the Tutorial

Advanced Excel Functions

About the Tutorial

This Microsoft Excel tutorial teaches you how to create and use Excel functions. You can use Excel functions to perform various mathematical, statistical, logical calculations. This tutorial takes you step-by-step through the process.

Audience

This tutorial is intended for people who use Excel but are intimidated by the concept of formulas and functions.

Prerequisites

This tutorial assumes your familiarity with basic fomulas for calculations in Excel.

Copyright & Disclaimer

Copyright 2016 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at contact@

i

Advanced Excel Functions

Table of Contents

About the Tutorial ............................................................................................................................................ i Audience........................................................................................................................................................... i Prerequisites..................................................................................................................................................... i Copyright & Disclaimer ..................................................................................................................................... i Table of Contents ............................................................................................................................................ ii

COMPATIBILITY FUNCTIONS........................................................................................................1

1. Compatibility Functions ? Overview ......................................................................................................2 2. BETADIST Function ................................................................................................................................5 3. BETAINV Function..................................................................................................................................6 4. BINOMDIST Function .............................................................................................................................8 5. CEILING Function .................................................................................................................................10 6. CHIDIST Function .................................................................................................................................12 7. CHIINV Function ..................................................................................................................................13 8. CHITEST Function.................................................................................................................................15 9. CONFIDENCE Function .........................................................................................................................17 10. COVAR Function ..................................................................................................................................19 11. CRITBINOM Function ...........................................................................................................................21 12. EXPONDIST Function ...........................................................................................................................22 13. FDIST Function.....................................................................................................................................24 14. FINV Function ......................................................................................................................................26 15. FLOOR Function ...................................................................................................................................28 16. FTEST Function ....................................................................................................................................30 17. GAMMADIST Function.........................................................................................................................32 18. GAMMAINV Function ..........................................................................................................................34 19. HYPGEOMDIST Function ......................................................................................................................36 20. LOGINV Function .................................................................................................................................38 21. LOGNORMDIST Function .....................................................................................................................39 22. MODE Function ...................................................................................................................................40

ii

Advanced Excel Functions

23. NEGBINOMDIST Function ....................................................................................................................42 24. NORMDIST Function ............................................................................................................................44 25. NORMINV Function .............................................................................................................................46 26. NORMSDIST Function ..........................................................................................................................48 27. NORMSINV Function ...........................................................................................................................49 28. PERCENTILE Function...........................................................................................................................50 29. PERCENTRANK Function ......................................................................................................................51 30. POISSON Function ...............................................................................................................................53 31. QUARTILE Function..............................................................................................................................55 32. RANK Function ....................................................................................................................................57 33. STDEV Function ...................................................................................................................................59 34. STDEVP Function .................................................................................................................................61 35. TDIST Function.....................................................................................................................................63 36. TINV Function......................................................................................................................................65 37. TTEST Function ....................................................................................................................................67 38. VAR Function.......................................................................................................................................69 39. VARP Function.....................................................................................................................................71 40. WEIBULL Function ...............................................................................................................................73 41. ZTEST Function ....................................................................................................................................75

CUBE FUNCTIONS ..................................................................................................................... 77

42. Cube Functions ? Overview .................................................................................................................78 43. CUBEKPIMEMBER Function .................................................................................................................79 44. CUBEMEMBER Function ......................................................................................................................81 45. CUBEMEMBERPROPERTY Function ......................................................................................................83 46. CUBERANKEDMEMBER Function .........................................................................................................85 47. CUBESET Function................................................................................................................................87 48. CUBESETCOUNT Function ....................................................................................................................89

iii

Advanced Excel Functions

49. CUBEVALUE Function...........................................................................................................................90

DATABASE FUNCTIONS .............................................................................................................93

50. Database Functions ? Overview...........................................................................................................94 51. DAVERAGE Function ............................................................................................................................96 52. DCOUNT Function................................................................................................................................98 53. DCOUNTA Function ...........................................................................................................................100 54. DGET Function ...................................................................................................................................102 55. DMAX Function .................................................................................................................................104 56. DMIN Function ..................................................................................................................................106 57. DPRODUCT Function..........................................................................................................................108 58. DSTDEV Function ...............................................................................................................................110 59. DSTDEVP Function .............................................................................................................................112 60. DSUM Function..................................................................................................................................114 61. DVAR Function ..................................................................................................................................116 62. DVARP Function ................................................................................................................................118

DATE & TIME FUNCTIONS ....................................................................................................... 120

63. Date & Time Functions ? Overview....................................................................................................121 64. DATE Function ...................................................................................................................................123 65. DATEDIF Function ..............................................................................................................................125 66. DATEVALUE Function.........................................................................................................................127 67. DAY Function.....................................................................................................................................129 68. DAYS Function ...................................................................................................................................131 69. DAYS360 Function .............................................................................................................................133 70. EDATE Function .................................................................................................................................135 71. EOMONTH Function ..........................................................................................................................137 72. HOUR Function ..................................................................................................................................139 73. ISOWEEKNUM Function.....................................................................................................................140

iv

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download