I have collected data on educational level (1-4) in the household, and need a function that extract the highest value in the family. In case of missing values (999), the formula should only report values from the actual educational range 1-4, but still be able to handle 999 without transforming it to a space or similar.
Use the COUNTIF function to find how many 999's there are and then use the LARGE function to return the first value that is not 999:
=LARGE(range,COUNTIF(range,999)+1)
LARGE is a generalization of MAX that allows to find the kth largest feature in a list. The k in this case is the first thing that is not 999, which happens to be the number of 999s + 1.
Hope that helps.
Just in case it might help, here's a slightly simplified formula following the opposite strategy (i.e. it counts the number of values inferior to 999, and then selects the highest (or actually the "least small") value correponding to that rank). Given that you only want the values between 1-4 to count, you might consider setting the threshold at "
- Badges
- Science topic