When calculating averages based on multiple conditions, Excel provides a function called AVERAGEIFS. This is great for calculating the mean of a set of numbers. However they do not provide an equivalent for calculating the median and mode using multiple conditions.
To achieve this we will need to create an array formula by nesting the IF function within the median and mode functions.
For this example we will be using a list of real estate prices.
Conditional Median Formula
The MEDIAN function is used to calculate the middle value from a set of values. The formula below, entered in cell H3, calculates the median real estate price for a specific town.
The IF function is used to apply a condition. The condition in this case being that the town must be equal to the contents of cell G3, currently St Ives.
When creating an array formula you need to press Ctrl + Shift + Enter as opposed to just Enter. This will place curly braces at each end of the formula. You do not type these curly braces, Excel will put them in.
The conditional median formula returns £230,000.
This is because the values 190,000; 195,000; 210,000; 220,000; 240,000; 240,000; 245,000; 300,000 meet the condition of being in the town of St Ives.
There are 8 values, an even number, and the mean of the middle two is 230,000.
(220,000 + 240,000)/2 = 230,000
Conditional Mode Formula
The MODE function is used to return the value that occurs most frequently within a set of values. The formula below, enter in cell I3, calculates the modal real estate price for a specific town.
The price that occurs the most is £240,000.
Calculate Median and Mode Using Multiple Conditions
To add further conditions to our median and mode formulas, we would have to enclose each condition within parentheses and separate them with the * (used to determine AND logic between conditions).The formula below was entered in cell I6 to return the median value for the St Ives office (G6) and greater than 3 bedrooms (H6).
Change the function name to mode to return the modal value based on multiple conditions. Test the formula out by changing the town and number of bedrooms.
No comments:
Post a Comment