There is, sadly enough, no
simple formula for calculating the
IQM in
Microsoft Excel (well there is one way, but that isn't exact, as it strips away the partial numbers, which is no good). However, there is a complicated way :)
The formula
=IF(((COUNT(A1:A1000)/4)-INT(COUNT(A1:A1000)/4))=0,
(AVERAGE(OFFSET(A1,(COUNT(A1:A1000))/4,0,(COUNT(A1:A1000))/2,1))),
((SUM(OFFSET(A1,((COUNT(A1:A1000))/4)+1,0,((COUNT(A1:A1000))/2)-1,1)))+
(((OFFSET(A1,((COUNT(A1:A1000))/4),0,1,1))+(OFFSET(A1,(((COUNT(A1:A1000))/4)+
((COUNT(A1:A1000))/2)),0,1,1))))*(1-((COUNT(A1:A100)/4)-INT(COUNT(A1:A1000)/4))))
/(COUNT(OFFSET(A1,((COUNT(A1:A1000))/4)+1,0,((COUNT(A1:A1000))/2)-1,1))+
((1-((COUNT(A1:A100)/4)-INT(COUNT(A1:A1000)/4)))*2)))
For readability, the code has spaces inserted. If you care to use it, please remove the spaces on the end of each line. This goes for all the code in this w/u.
How to use it
This formula makes certain assumptions:
- That your list of cells for which you want to calculate the IQM starts on cell A1
- That your cells are sorted (ascending or descending does not matter)
- That you have less than a thousand cells in row A
- That you have nothing else but the list of numbers in row A
If you would like to tweak the formula, it is probably easier to split the formula over in two different cells:
In cell B1, add the following formula:
=COUNT(A1:A1000)
Where you want the IQM to show, add the following formula:
=IF(((B1/4)-INT(B1/4))=0,(AVERAGE(OFFSET(A1,(B1)/4,0,(B1)/2,1))),
((SUM(OFFSET(A1,((B1)/4)+1,0,((B1)/2)-1,1)))+(((OFFSET(A1,((B1)/4),0,1,1))+
(OFFSET(A1,(((B1)/4)+((B1)/2)),0,1,1))))*(1-((B1/4)-INT(B1/4))))/
(COUNT(OFFSET(A1,((B1)/4)+1,0,((B1)/2)-1,1))+((1-((B1/4)-INT(B1/4)))*2)))
Now, in B1, you will want to change A1 to the first cell of your list. Change A1000 to a cell that is greater than your list (i.e if you use more than 1000 cells, you want this number to be larger than 1000)
In the long formula, change the references to A1 to whatever is your first cell, and the formula should work just fine.
What the formula does
First, the formula decides which main formula to use.
IF(((COUNT(A1:A1000)/4)-INT(COUNT(A1:A1000)/4))=0,
If the number of cells is divisible by four, the following formula is used:
AVERAGE(OFFSET(A1,(COUNT(A1:A1000))/4,0,(COUNT(A1:A1000))/2,1)),
This formula counts how many cells there are, and then calculates the average of half the cells, starting on the cell one fourth into the list
If the number of cells is not divisible by four, that is where the fun begins...
((SUM(OFFSET(A1,((COUNT(A1:A1000))/4)+1,0,((COUNT(A1:A1000))/2)-1,1)))+
The above section of the formula calculates the sum of the whole cells - i.e the same thing as above, but it does not count the cells that will only count partially towards the interquartile mean.
(((OFFSET(A1,((COUNT(A1:A1000))/4),0,1,1))+(OFFSET(A1,(((COUNT(A1:A1000))/4)+
((COUNT(A1:A1000))/2)),0,1,1))))*
The above section of the formula finds the first and last cell of the selection - in other words, the cells that were omitted from the previous section of the formula. When those values are found, they are added to each other and then...
(1-((COUNT(A1:A100)/4)-INT(COUNT(A1:A1000)/4))))/
... multiplied with the correct fraction of which the two numbers count towards the interquartile mean. This number is added to the other cells, meaning that we now have a sum - the total of all the cells in our middle 50% selection of the list.
(COUNT(OFFSET(A1,((COUNT(A1:A1000))/4)+1,0,((COUNT(A1:A1000))/2)-1,1))+
((1-((COUNT(A1:A100)/4)-INT(COUNT(A1:A1000)/4)))*2)))
Finally, the formula determines how many items there are in the selection, and adds either 0.5, 1.0 or 1.5 items to the list, depending on how many there are in the list.
When it has determined how many items there are in the selection, the sum found earlier is divided by the number of items.
The final result? The interquartile mean!
Note:
I consider it highly likely that there are several ways to tweak my formula so it looks nicer, or perhaps replace it with something different altogether. If you have any ideas as to how, i'd appreciate a message - I've spent a ridiculous amount of time on this, and I would like to get it right :)