Quote:
Originally Posted by David Harper
I am building a country ranking model spreadsheet. As part of this project, I am ranking several different macroeconomic indicators and then creating a column which provides an average of each of these ranks. As some countries contain n.a. values for some macroeconomic indicators, i would love to embed in the average formula some kind of function which would automatically return an n.a. value if more than half of the ranked macroeconomic indicators have n.a. values in the column. I would be extremely grateful if anyone knows a formula that would work for this.
Thanks for your help.
David
|
Let's say your country rankings are in column R (for Rank) with Country #1 being in Row 1, Country #2, in row 2 etc. Let's also say you have 10 countries (Rows 1 through 10) and you want to calculate the average ranking in Cell R11. So... you enter the following formula into Cell R11: =average(R1:R10).
Now, let's put some data in. Let's say Country #1 happens to be ranked 1, 2 is ranked 2, 3 is 3 and 4 is 4. But the rest of the countries are not reporting, so "n.a." is entered as text (without the quotes) into cells R5 through R10.
Note that the average of 2.5 is displayed in R11 as it ignores the non-numeric cells containing "n.a."
This is good, but you want more!!
First, let's count how many cells contain "n.a." by entering the following formula into cell S1: =if(R1="n.a.",1,0)
Then click and drag the bottom right corner of that cell and pull it down to (copying it to) Rows 2 through 10. The result should be a zero in each cell from S1 through S4... and a 1 in each cell from S5 through S10.
Now, let's add up the number of "n.a."s by entering the following formula into cell S11: =sum(S1:S10) If all is correct, it should read: 6
I'm going to assume that you know how many countries you are monitoring and ranking -- and in this example that number is 10 (a constant which is used below).
Now that we know how many "n.a."s there are, we can go back to the "average" calculation and make it conditional. Change the formula in cell R11 to read like this: =if(S11.5*10,"n.a.",average(R1:R10))
If everything worked correctly, R11 is now showing "n.a." because 6 countries are ranked "n.a." (6 being MORE THAN HALF of the 10 countries being ranked).
Now change cell R5 to the number 5. Note that cell R11 now computes the average of 1, 2, 3, 4, 5... which is 3. The average is shown because ONLY HALF of the countries are ranked "n.a." It takes MORE THAN HALF to satisfy the test in R11 to display "n.a."
Note: if you want to display "n.a." when HALF OR MORE of the countries are ranked "n.a.", then change cell R11 to read:
=if(S11=.5*10,"n.a.",average(R1:R10))
Final note: replace the constant "10" in R11 with the actual number of countries you are monitoring.