real statistics data analysis tool in excel

This example uses both techniques in the same formula. When using the SUMIFS and COUNTIFS functions, the criteria must be entered as text or as a cell reference. In this example, we count the number of sales from the region entered into cell E3 that have a value of 200 or more.

=COUNTIFS(criteria range 1, criteria 1, …) It, therefore, does not require a sum range like SUMIFS. The COUNTIFS function will count the number of values that meet specified criteria. And although not mentioned as part of the 12 most useful Excel functions for data analysis, there are also AVERAGEIFS, MAXIFS, and MINIFS functions. It is very similar to the SUMIFS function. The COUNTIFS function is another mega function for Excel data analysis. It is an extremely useful Excel function. It is definitely worth exploring the SUMIFS function in more detail.

In this example, we are summing the values in column C for the region entered into cell E3. =SUMIFS(sum range, criteria range 1, criteria 1, …) The function asks you for the range of values to sum, and then each range to test and what criteria to test it for. So you can essentially ignore SUMIF as SUMIFS is a superior function. It sums values that meet specified criteria.Įxcel also has a function named SUMIF which does the same task except it can only test one condition, while SUMIFS can test many. SUMIFS is one of the most useful Excel functions. In this example, we have displayed the word “Yes” if the delivery date in column C is more than 7 days later than the order date in column B. =IF(logical test, value if true, value if false) The IF function asks you for the logical test to perform, what action to take if the test is true, and the alternative action if the result of the test is false. With IF, we could get Excel to perform a different calculation or display a different value dependent on the outcome of a logical test (a decision). This function means we can automate decision making in our spreadsheets.

Real statistics data analysis tool in excel