Multimedia-Introduction-to-Business-Analytics-with-Excel-1_-Ren_-Justin_-Z-Business-Analytics-with-E

["(6) Absolute reference vs. relative reference: the difference of a \u201c$\u201d. Properly referencing cells in Excel is one of the essential skills in Excel analytics works. When you copy a cell which contains a formula and paste it to another location, Excel by default adjusts automatically the formula in that cell based on its current location. This is both good and bad. It is good in the sense that in many cases where your spreadsheet follows a predictable pattern, you do not need to type in a new formula for each new cell. That is also where bad things can happen \u2013 i.e., when you do not want some part of the formula to change. We illustrate this with the following example. Example (Quick Price Table): A retailer sells four types of products. There are three types of discounts: 7% (tier 1), 15% (tier 2), and 30% (tier 3). The seller wants to makes a table where he could quickly look up the final prices after discount. The table should look like the following: goo.gl\/uf76nu goo.gl\/pT2bFv Enter the table in Excel, and calculate the price using just one formula.","Solution After we enter the empty table into Excel, what formula should we enter in cell D4 so that it can be readily copied and pasted to other cells in the table and product the right results? The answer lies in how Excel fixes the location of columns and rows in a formula. It is done by putting a dollar sign ($) in front of the row or column index of a cell (and this has nothing to do with the American currency denominations!). For example, in cell D4, enter =$C4*(1-D$3). When you copy the cell and paste it to cell E4, you will find that the formula changes to be =$C4*(1-E$3). This is the way we want, because we want Excel to use price information in column C only (i.e., absolute referencing column C), and use quantity information in row 3 only (i.e., absolute referencing row 3). In contrast, if you does not use $ signs, the results will be wrong, big time. Try it yourself, and you will see. Tip: Keyboard Shortcut for quickly changing referencing of a cell. \u25cf For Mac: Command (\u2318 ) key + T \u25cf For PC: F4 function key They both are a four-way switch. As an example, suppose with the formula \u201c=B10\u201d you press this shortcut. You will get: Press once: =$B$10 Press again: =B$10 Press 3rd-time: =$B10 Press 4th-time: =B10 Finally, recall the \u2318 (Ctrl)-Enter trick we just talked about. To fill the 12 cells in the table in one scoop, select the whole range (D4:F7), just type =$C4*(1- D$3), and then press Ctrl-Enter.","The idea behind the formula is from the following observation: the three types of discounts are in row 3, and their row indices should not be changed while their column indices can. Likewise, the unit prices of the four products are in column C, and their column indices should never change while their row indices can. Hence, in referring a unit price, instead of referring cell C4, it should be $C4. Likewise, in referring a discount such as the one in D4, instead of D3, it should be D$3.","Report Issues (goo.gl\/y3kJmv)","","Chapter 2. Organizing Data In this Chapter (1) IF, AND, OR (2) VLOOKUP and HLOOKUP \u25cb Exact Match \u25cb Approximate Match (3) MATCH and INDEX","(1) IF, AND, OR Today we just have too much data. Most of the time we have to search for the right information hidden within heaps of data. The same is true for Excel spreadsheets. There can be a large quantity of data, and users need to equip themselves with skills and tools to select and identify the information they are looking for. IF statements, combined with logical operators like AND and OR, are essential for this purpose. Let\u2019s look at two examples. Example (Stock Markets Movement, Part I) In studying financial markets, it is helpful to identify periods when the stock markets move in a certain fashion. Take the Nasdaq composite index, for example. Suppose we download its monthly data for the last twenty year (from July 1992 to July 2012). We can compute its monthly changes during each month. If the combined change of the current month and its previous two months (i.e. three- month total) exceed 300 points, then we call the market is \u201cupbeat\u201d. If the three-month total drops more than 300 points, then we call the market is \u201cdownbeat\u201d. Identify those upbeat and downbeat months.","goo.gl\/9GF19p goo.gl\/YMH4yT Solution After we downloaded monthly data for the Nasdaq composite index (ticker: ^IXIC), the first step is to calculate the month-over-month change. This is done in column G (Columns B-F are hidden from view in the above picture because they contain irrelevant information such as open price, close price, and volume). In order to label each month as \u201cUpbeat\u201d or \u201cDownbeat\u201d, we need to compute the three-month-combined change, and then use it in an IF statement. For example in cell I3, we enter =IF(H2+H3+H4>300,\\\"Upbeat\\\", IF(H2+H3+H4<-300,\\\"Downbeat\\\",\\\"\\\")) Note that this is a nested IF statements with two IF statements. The first IF decides if the three-month-combined changes is above 300 points. If it is, then it prints an \u201cUpbeat\u201d in column I. But if not, Excel goes on to the second IF statement to see if the combined downward drop is above 300 points. If it is, then it returns a \u201cDownbeat\u201d. Finally, we can use a COUNTIF statement to see in the past 20 years, there are how many upbeat and downbeat months. They are recorded in cell K10 and K11, respectively, with the following formula: =COUNTIF($I$2:$I$241,\\\"=Upbeat\\\"), =COUNTIF($I$2:$I$241,\\\"=Downbeat\\\"). It turns out that there are 21 upbeat months and 22 downbeat months, according to the way we define upbeat and downbeat.","Report Issues (goo.gl\/y3kJmv)","","(2) VLOOKUP and HLOOKUP Lookup functions are extremely useful in extracting useful information from data. In fact, one cannot be called Excel-Analytics-Proficient if he or she does not know how to use VLOOKUP and HLOOKUP. Their applications are numerous, but here we introduce two major one. The First one is to use lookup functions to merge useful information into one table. The second one is about processing data using a set of criteria. Exact Match for Merging Data Often we find useful information being scattered in multiple places, and the goal is to extract useful information from each of them and combine into one new table. As long as there is a shared link among the various sources of information (such as ID number or names), lookup functions can provide a convenient solution. Here we illustrate using VLOOKUP. VLOOKUP and HLOOKUP provides the same functionality except that the former is done vertical-wise, and the latter horizontal-wise. Example 1: In a small company with six employees, everyone\u2019s phone extension is stored in a table, but everyone\u2019s job function is in another table. Combine this two table together using VLOOKUP.","goo.gl\/PnXt8w goo.gl\/dTPFFY Solution In the two tables shown below, we notice that the orders of records are different, so we cannot just cut the first table and paste it to the second (In fact, even if the order of names is the same, it is still a bad idea to simply copy and paste. This is because you would never know if the two lists are exactly the same or differ slightly. Secondly, should either of the tables be updated, your new table would not be updated automatically). One can, of course, first sort both tables before putting the two together side by side and then double check if the two lists are identical, but this involves quite a bit manual work, and is generally not advised. Instead, we can use VLOOKUP to solve this problem handily. Let\u2019s take the first table with phone extensions (in column C). How to add their job function correctly by its side in column D? In cell D4 of the table below, for example, enter =VLOOKUP(B4, $G$4:$H$9, 2, FALSE). What this does it to get the content from B4, which is \u201cTom\u201d, go to the other table in $G$4:$H$9 and see if it could find an exact match -- the last FALSE in the above means that an exact match is required. If there exists an exact","match, it finds that row, and return the content in the 2nd column - that is the \u201c2\u201d in the above formula about - in that row. So in this example, for \u201cTom\u201d, Excel finds it in the last row of the table and returns \u201cAccounting\u201d, which is what we want. Since we used proper cell referencing in the formula, we can just use it on all other cells in column D. Mission accomplished. Approximate Match for Processing Data according to a Set of Criteria When selection criteria become numerous, nested IF can become quite cumbersome. We also need a better way of searching through a long list. Here we provide two example to illustrate how LOOKUP functions in Excel can help us do that. Example (Stock Markets Movement, Part II) In the Nasdaq example discussed earlier, suppose we are interested in a more interesting way of visualizing the monthly changes, which range widely between -700 to more than 700. In particular, we want Excel to create a new column and use the following symbols to represent different magnitude of changes.","goo.gl\/MKb5tb goo.gl\/tQut9X This way, one does not have to read the numbers of monthly changes and get a quick sense of how the market is moving (Some may realize conditional formatting can do this much more nicely within each cell. That is true. This exercise merely illustrate what lookup functions can do.) Solution We could use IF functions - but imagine the number of IFs there will be in the formula! The completed formula will look something like this: =IF(I3>=800,\u201d|********\u201d, IF(AND(I3>=700, I3<800),\u201d|*******\u201d, IF(AND(I3>=600, I3<700),\u201d|******\u201d, IF(AND(I3>=500, I3<600),\u201d|*****\u201d, IF(AND(I3>=400, I3<500),\u201d|****\u201d, IF(AND(I3>=300, I3<400),\u201d|***\u201d, IF(AND(I3>=200, I3<300),\u201d|**\u201d, IF(AND(I3>=100, I3<200),\u201d|*\u201d, IF(AND(I3>=0, I3<100), \u201d|\u201d, IF(AND(I3>=-100, I3<0),\u201d|\u201d, IF(AND(I3>=-200, I3<-100),\u201d*|\u201d, IF(AND(I3>=-300, I3<-200),\u201d**|\u201d, IF(AND(I3>=-400, I3<-300),\u201d***|\u201d, IF(AND(I3>=-500, I3<-400),\u201d****|\u201d,","IF(AND(I3>=-600, I3<-500),\u201d*****|\u201d, IF(AND(I3>=-700, I3<-600),\u201d******|\u201d, IF(AND(I3<-700), \u201d*******|\u201d, \u201d\u201d) )))))))))))))))) Imagine typing that into a formula box! And imagine checking how many \u201c)\u201d there should be at the end of that formula! Clearly, IF function does not work here. We need something better. Fortunately, Excel has presciently built into functions called VLOOKUP and HLOOKUP to deal with such situations. The usage of VLOOKUP involves quite a few parameters: VLOOKUP(lookup value, where to lookup, column index,[TRUE- approximate match, FALSE - exact match) To get a better sense of how to use it, let us apply it to the problem at hand here. But before we can plug it in a formula, we need to do some prep work. In particular, we need to set up a lookup table, which looks like the following: It has just two columns. The first are the breakpoints, and the next their associated symbols. Notice that we need to sort the breakpoints in ascending","order. This is required if we want to properly assign values to each monthly change. Now we are ready to use VLOOKUP, as shown: Take cell I3 as an example. The monthly change in cell H3 is 4.47. The formula calls for: =VLOOKUP(H3,$K$4:$L$20,2,TRUE). It looks a bit cryptic, so let me explain. In this formula, Excel first takes value in H3 (4.47), and compare it with the table contained in $K$4:$L$20. There it does not find an exact match, so it tries to find an approximate match -- this is what the \u201cTRUE\u201d in the last parameter is about. 4.47 lies between 0 and 100, so which one will Excel pick? Here the rule is to find the largest value that is below the lookup value. So 0 is the answer. Now what? Excel will find, in the same row as 0, the value that is in the 2nd column (as dictated by the \u201c2\u201d in the third parameter of the formula). So it","returns whatever that is there: a \u201c | \u201c. Done. The resulting column I is exactly what we want. You can now with a casual look know if a particular month is up or down and roughly how large the change is. Preview: As just mentioned, Excel has a very nice built-in tool in visualizing data just like the example we show here. It is called Conditional Formatting, which we will discuss extensively later in another part of this series. Quick Summary: VLOOKUP(lookup value, where to lookup, column index, [TRUE- approximate match, FALSE - exact match)","Report Issues (goo.gl\/y3kJmv)","","(3) MATCH and INDEX When Excel finds the cell that fits your search criterion, MATCH function is used to refer to its location so the cell could be used in other calculation. INDEX function does things the other way: Given the location information, INDEX will produce the corresponding content. This can be very convenient in finding the right information in a large data set. Example (When does the stock jump the most?) As a stock analyst, Ava is following the stock of Google (GOOG). She has in front of her the weekly price history for the stock. She wants to find out when the stock experienced the largest price jump. goo.gl\/Uuj8NK goo.gl\/tqNt80 Solution We break down this task into several steps. (0) Download data, and compute week-over-week return (column C).","(1) We use MAX function, which we talked about earlier, to tell us among all the weekly return numbers, which one is the highest. This is done in cell F4, with the formula: =MAX(C4:C418) Excel finds that the maximum change is 19.65%. (2) Use MATCH function to record the location of this maximum value in column C. MATCH function has the following structure: MATCH(lookup value, where to look,,[1-the largest value that is <= lookup value, 0- the first value that is exactly matching lookup value, -1 -the smallest value that is >= lookup value]) Here we want to find the exact match, so we enter in cell F5: =MATCH(F4,C4:C418,0) Excel returns 407. That is, the maximum return is at the 407th position column C. (3) Use INDEX function to return the date. INDEX function has the following arguments: INDEX(range,row number,column number) So we enter in cell F6: =INDEX(A4:C418,F5,1) That is, in the whole range of A4: C418, find the value in the 407th row (stored in cell F5) and the 1st column (that is where the date information is). Somewhat surprisingly, Excel returns something unexpected: 38278. What date is that?! Don\u2019t panic. The answer is correct. It is just that Excel\u2019s default","format for a date is a series number. To convert it to a mm\/dd\/yy format, we need to take one more step. In cell F7, enter: =DATE(YEAR(F6),MONTH(F6),DAY(F6)) This will tell us that 38278 is 10\/18\/2004, during which week the largest stock price jump of Google happened. Quick Summary: MATCH(lookup value, where to look, [1-the largest value that is <= lookup value, 0- the first value that is exactly matching lookup value, -1 -the smallest value that is >=lookup value]) INDEX(range, row number, column number)","Report Issues (goo.gl\/y3kJmv)","","Chapter 3. Basic Mathematical and Statistical Functions In this Chapter (1) Summation: SUM, SUMPRODUCT, SUMIF (2) Counting: COUNT, COUNTA, COUNTIF (3) Summary Statistics: AVERAGE, MEDIAN, MODE, MIN, MAX (4) Covariance and Correlation: CORREL and COVAR (5) Ranking: RANK, PERCENTILE, QUARTILE Excel has a vast reservoir of functions: financial, statistics, date\/time, lookup\/reference, etc.. Here we introduce some of the most often used functions in business analytics.","(1) Summation: SUM, SUMPRODUCT, SUMIF Summation is perhaps one of the most used operations. For straight summation, use SUM function. You can add any ranges, as long as you separate them by comma. Often we need to multiply numbers in two columns (or tables) and sum up the products. SUMPRODUCT provides a handy way to achieve that. SUMIF provides a handy way to selectively sum up numbers in a range that meet a certain criterion. We illustrate the usage of these functions with a simple example. Example (Restaurant ratings) A travel guidebook is updating its restaurant ratings in a town, which has 10 restaurants. It has collected numeric data, on a 0-100 scale on food quality, service quality, and dining environment of each of the dining places. Moreover, the weights for each of the dimensions are 40%, 30%, and 30%. In addition, some restaurants have explicit agreed to be included in the guidebook. Set up a spreadsheet to calculate their weighted scores and count, among those who have opted in the book, how many restaurants have a combined score of 80 or more.","goo.gl\/UepOhv goo.gl\/3V6Zwe Solution As you can see the original data are in range A3-E13. Those who agree to be included in the guidebook is marked by a \u201c1\u201d in column E. The weights are in B14-D14. First we need to compute the weighted score for each restaurant. This is achieved by SUMPRODUCT in cells B17-B26. In cell B17 for restaurant #1, for example, the formula to enter is =SUMPRODUCT(B4:D4,$B$14:$D$14). Note that we used absolute referencing of the weights in range $B$14:$D$14 so that we can copy the formula to other cells without altering the weights.","Now that we have the weighted score for each restaurant, we can use SUMIF to count how many of them, among those opted in, have a total weighted score of higher than 80. Here, since all the opted-in restaurants are marked with a \u201c1\u201d, we can just use SUMIF to select those whose scores are over 80 ans sum up their \u201c1\u201ds to get the total number. The formula would be: =SUMIF(B17:B26,\\\">80\\\",E4:E13) Excel returns the result of 4 -- there are a total of 4 restaurants that have earned combined scores of higher than 80. Quick Summary: SUM(range) SUMPRODUCT(range1, range2) SUMIF(range, criteria, sum range)","Report Issues (goo.gl\/y3kJmv)","","(2) Counting: COUNT, COUNTIF, COUNTA Excel spreadsheets often involve counting. COUNT function count how many numeric numbers there are in a range you specify, whereas COUNTA counts the number of cells in a range as long as they are not empty. COUNTIF is useful in that it counts the number of cells that satisfies a certain specified criterion. We illustrate this with the following example. Example (Survey results) A marketing firm has sent out a survey to potential customers to gauge the purchase intention of a product., which is scored between 0 (no interest) to 100 (definitely buy). All copies of the surveys are tracked with a unique ID. As the table below indicates, some of the surveys are not returned and are marked as \u201cn\/a\u201d. Use COUNT and COUNTA to compare the number of surveys received against the total numbers recorded. Then use COUNTIF to calculate how many customers gave out scores higher than 80.","goo.gl\/VgfQz9 goo.gl\/fJXtgn Solution When we apply COUNT towards the range of cells (B4:B23) Excel returns 19, but when we apply COUNTA we get 20. The difference is caused by the \\\"n\/a\\\" entry in B8, since COUNT will only count numeric values. To count the number of scores that are over 80, we use the following command: =COUNTIF(B4:B23,\u201d>80\u201d), and Excel will return 3. Here the criteria (>80) needs to be enclosed in quotation marks A better way of structuring this is to dedicate a separate cell","for the cutoff - here, put 80 in cell B28. And then use the following command: =COUNTIF(B4:B23,\u201d>\u201d&B28) to get the same answer. Note the use of \u201c&\u201d here: it allows the cutoff to be changed relatively easily without messing with the formula here. For example, if you are interested in know how many gave score over 70 instead of 80, you just change the number in cell B26, and you will get your answer right away. You can even use Data Table to see the number of scores over 40,50, 60, ..., all without changing any formula! We will talk more about Data Table later (If you cannot wait, check out Chapter 3 now). Quick Summary: COUNT(range) COUNTA(range) COUNTIF(range, criteria)","Report Issues (goo.gl\/y3kJmv)","","(3) Summary Statistics: AVERAGE, MEDIAN, MODE, MAX, MIN, STDEV In understanding a statistical distribution, we often need to calculate its summary statistics such as average, median, standard deviation, mode, maximum and minimum. These are readily available in Excel. Example (Daily Returns of stocks) Facebook (FB) and LinkedIn (INKD) are two social network companies that went public. Take the daily stock price data for the two months since their IPO (LinkedIn on May 19, 2011, and Facebook on May 18, 2012, almost exactly one year apart). Then calculate their daily return (i.e., changes in price over previous day in terms of percentages), and assess such summary statistics as average, median, mode, max, min, and standard deviation of daily return. goo.gl\/NOMngA Solution There are multiple ways to import data such as stock prices from external sources (the box below details how you can do so within Excel). Extra (for PC only): Download Data from the Internet within Excel Excel has built-in advanced capabilities to connect to external databases and download data from webpages. Here we connect to Yahoo Finance's historical data on stock prices, and download them. Suppose you are interested in using Excel tracking the daily stock prices of IBM(ticker: IBM). Under Data menu, click on Get External Data (the first one from the left), and then From Web. A windows pops up which looks very much like Internet Explorer --in fact it is a simplified version of IE. There is an address bar in the browser. This is where you enter the web address from which to begin your data importing process. Enter www.finance.yahoo.com, and you will see the homepage of Yahoo Finance (you might see some error message popping up: Do not worry. It is just because the browser is not that sophisticated as a full-fledged one). There you can enter your stock ticker, and click Get Quotes in the webpage.","Then click on the Historical Prices, which is in the left panel. There you will be able to enter the desired time range of stock prices, as well as frequencies of data (daily, weekly, monthly, or dividends only). Then click Get Prices. Notice that there are little arrows (highlighted in yellow) displayed in various areas of the page. These are the areas the Excel find data and will be import data from. Once you move your mouse over the arrows, a box will appear outlining the boundary of the table which it can import. Here we click on the little arrow on the stock price table, and then click on the Import button on the bottom of the page. It will ask you where to put the data. Just accept the default (or you can specify your desired location), and click OK. After a few moments, you will see your data show up in your spreadsheet!","The advantage of importing data this way is that they are linked dynamically with the source page. Therefore, changes in the original content can be automatically refreshed in Excel, without the need to manually import again. A word of caution, though. Not all web content will be importable through this application. Web authoring tools are constant evolving. So there is no guarantee you can always import a certain web site with Excel. In here, we use a separate worksheet for each stock. After daily stock prices are imported, we can compute daily returns with the following formula: Daily return = (Day 2 price - Day 1 price)\/Day 1 price Note that on the day of IPO (the last row in the dataset), there is no daily return to be computed because it is the first trading day. In fact, Excel will have a \u201c#DIV\/0!\u201d error (i.e., division by 0) if you apply the formula. You should delete the content in that cell and leave it empty. To format all the daily returns into percentages, instead of the default number format with decimal points, select all numbers in column H, and press","Ctrl+Shift+5 (It is easy to remember because the % sign is on the 5 key). Once we have all the return numbers, we can get all the summary statistics, as below. As we can see from the summary statistics, the two stocks behave quite differently during the first two months (42 trading days to be precise) since IPO. FB on the average is losing its value (-0.6%), while LNKD is increasing (0.3%). On the other hand, LNKD has higher variability in terms of standard","deviation (5.2% vs. 4.0%). For extreme movements, LNKD has a higher maximum daily increase (12.0% vs. 6.1%) and FB has a steeper decrease (-11.0% vs. -8.5%). The two has about the same median daily movement of -0.5%. Extra: From the above example, you might ask yourself: To what extent does the earlier stock movement matter to companies\u2019 long term prospect? It is an interesting, and important, question. We do not attempt to answer it rigorous here. Instead, we present a picture from the Wall Street Journal below - You can do this analysis by yourself, by the way, using Excel. All you need are data on stock prices. Source: WSJ, May 20, 2011. \u201cLinkedIn IPO Soars, Feeding Web Boom\u201d, online interactive graphics. From the above, it seems that stock price performance during earlier periods does serve as a signal for its later performance. However, a strong word of caution here: It is just a hypothesis. To empirically validate this, one need to use a much larger sample. Can you use more stock data and test whether that hypothesis is true?","","(4) Covariance and Correlation: CORREL and COVAR In business analytics, we often need to study the relationship between multiple variables. For example, we often ask, \u201cIs there any relation between prices of two stocks?\u201d, or \u201cIf we spend this amount of marketing dollars, do we see an increase in sales?\u201d Covariance and correlation are statistical functions to quantify such co-movements among multiple variables. Example (Stock price co-movements, Part I) Do the stocks of Google (GOOG) and McDonald\u2019s (MCD) tend to move in the same direction? Solution This may be a somewhat odd question, but we can empirically calculate the correlation between the two stock prices. To do that, we first download historical weekly close data (you can choose daily data if you like; It will many many more points) for the two stocks for the last 8 years (Google is a relatively young company, starting in year 2004). we can normalize them by calculating week-over-week returns, which are the percentage changes in prices over a week, similar to the daily return we did in an earlier example (Again, be careful of the last row: it does not have a valid value, a \u201c#DIV\/0!\u201d error. )","goo.gl\/fbrVY4 goo.gl\/D2smne Once we put the prices for the two stocks side by side, we can then run the correlation coefficient between the two by entering: =CORREL(C2:C408,E2:E408) The result shows a positive but not-so-strong correlation coefficient of 25.89%. Quick Summary: CORREL(column1, column2) COVAR(column1, column2)","Report Issues (goo.gl\/y3kJmv)","","(5) Ranking: RANK, PERCENTILE, QUARTILE In addition to quickly identify the largest or smallest number from a list using MAX or MIN, Excel can also let users know the exact rank of each number. This is achieved by the RANK function. Example (Analyzing Marketing Demand, Part I) In launching a new hand- held device, a company has collected estimated market demand information from a number of potential markets (see table below; all numbers in thousands). The company decides to enter each market according to their estimated demand size. Use Excel to compute the ranking of each market in terms of demand. goo.gl\/cBUV5x goo.gl\/NL2NMq","Solution RANK function has the following structure: RANK(which number, within a range, [0-descending, 1- ascending]) For example, for market 1 with a demand of 31, we can assess its ranking by entering, in cell D4, the following: =RANK(C4,$C$4:$C$23,0) Note that we use absolute referencing here with \u201c$\u201d signs so that we can reuse this formula and apply it to all other markets. The \u201c0\u201d at the last position indicates that we want to the ranking to be or descending order. This is because we want the larger number to be ranked with a lower number (e.g., the largest number will be ranked #1) to determine the order of entry. Excel can also pinpoint where each quartile of a distribution is. Quartiles, by definition, are the 25%, 50%, and 75% percentiles of a distribution. Let\u2019s look at an example. Example (Analyzing Marketing Demand, Part II) In the previous example analyzing market demand, the company also want to divide the 20 markets into four tiers, according to the quartiles in market demand. Calculate the four quartiles of market demand.","goo.gl\/gNEHYc goo.gl\/w2egyR Solution The function QUARTILE is straightforward to use. It just has two parameters: QUARTILE(column, which quartile 0-4) Quartile 0 is the minimum number, while quartile 4 is equivalent to the maximum number. Quartile 1, 2, and 3 divide the whole data set into four groups: Quartile 1 being the 25% percentile, quartile 2 the 50%, and quartile 3 the 75%. As shown in the figure, any markets with demand between 31 and 44 belong to quartile 1, between 44 and 49 quartile 2, between 49 and 52 quartile 3, between 52 and 55 quartile 4. In fact, Excel can be very precise in pointing out where the x% of a distribution is. This is made possible by using the PERCENTILE function. Its usage is similar: PERCENTILE(column, x% percentile)","In the example above, suppose we are interested in finding out the 90% percentile of market demand, we just enter: =PERCENTILE($C$4:$C$23,90%), and we get the answer of 54 right away. Quick Summary: RANK(which number, within a range,[0-descending, 1- ascending]) PERCENTILE(column, which percentile 0%-100%) QUARTILE(column, which quartile 0-4)","Report Issues (goo.gl\/y3kJmv)"]