Kilka dni temu przeglądając wpisy na grupach Goldenline natrafiłem na bardzo ciekawą formułę, która pozwala obliczyć średnią po odrzuceniu kilku wartości skrajnych.
Dwa Sposoby
Formuła na pierwszy rzut oka wydała mi się fantastyczna. W rozwiązaniu, o którym mowa, funkcja ŚREDNIA.WARUNKÓW łączyła się z funkcjami MIN.K i MAX.K. Zadaniem tych dwóch ostatnich było właśnie wyeliminowanie n wartości skrajnych.
Dzisiaj postanowiłem przyjrzeć się bliżej tej formule i zestawić ją z wbudowaną funkcją ŚREDNIA.WEWN, która pozwala na odrzucenie określonego procenta wartości skrajnych.
Screen
Rysunek poniżej bardzo ładnie pokazuje różnice i pozwala zapoznać się z algorytmem działania obu formuł.
Kluczową sprawą jest tutaj fakt, że w naszym zbiorze mamy dwie liczby, które są liczbami maksymalnymi (40). Chcemy odrzucić po jednej wartości skrajnej.
ŚREDNIA.WARUNKÓW
Za pomocą kombinacji funkcji ŚREDNIA WARUNKÓW + MIN.K + MAX.K w pierwszej formule (komórka C8) odrzuciliśmy wartości minimalne i maksymalne.
Specjalnie napisałem „wartości”, ponieważ odrzucamy tutaj dwie liczby 40 i najmniejszą cyfrę 9. W efekcie uzyskaliśmy średnią dla zakresu A2:A8… Nie zawsze jest to rozwiązanie o jakie nam chodzi, ponieważ faktycznie usunęliśmy jedną wartość z dolnego zakresu i dwie (takie same) wartości z górnego zakresu. Ale też patrząc na sprawę nieco inaczej – usunęliśmy wartości minimalne i maksymalne. Wszystko zależy od celu i punktu widzenia.
Druga formuła (C9), zwraca nam taki sam wynik co ŚREDNIA(A3:A8), czyli de facto usuwa dwie wartości z dolnego i górnego zakresu. Oznacza to, że funkcja MAX.K z drugim argumentem ustawionym na 2, usuwa dwie 40-ki (zostawia 35). Tak naprawdę w górnym zakresie nic się nie dzieje – nadal eliminowane są dwie liczby. Zmiana dotyczy natomiast zakresu dolnego – usunęliśmy liczbę 11, dlatego średnia podskoczyła nam z 22,86 na 24,83.
Trzecia formuła (C10) usuwa trzy wartości skrajne po obu stronach i zwraca ten sam wynik co ŚREDNIA(A4:A7). Średnia bez czterech wartości skrajnych to tak naprawdę średnia z dwóch liczb (24 + 28).
ŚREDNIA.WEWN
Nieco inaczej działa funkcja ŚREDNIA.WEWN, która usuwa określony procent wartości skrajnych. Przyznam, że w praktyce częściej potrzebuję wyeliminować tyle samo liczb z dolnego i górnego zakresu, dlatego korzystam z niej częściej niż z poprzedniej konstrukcji.
Pierwsza formuła (C13) zwraca średnią dla całego zakresu, czyli tak naprawdę nie eliminuje żadnych liczb. W przypadku większego zakresu wyeliminowanych zostałoby 5% liczb z zakresu dolnego i 5% liczb z zakresu górnego. Dla liczb od 1 do 100 ta formuła zwróciłaby wartość 50,5 (eliminacja liczb od 1 do 5 włącznie i od 96 do 100).
Druga formuła (C14) zwraca już taki wynik, o który mi chodzi – czyli 25. Eliminuje po jednej wartości skrajnej z dołu i z góry.
Trzecia formuła (C15) zwraca ten sam wynik, ponieważ nie jesteśmy w stanie wyeliminować 15% liczb z jednego i 15% liczb z drugiego zakresu (oba mają po 5 liczb).
Drugi argument ustawiony na 40% (C16) pozwala nam jednak na usunięcie dwóch wartości skrajnych z każdego z dwóch zakresów. W rezultacie otrzymujemy taki sam wynik co dla średniej A3:A8.
Analogicznie, drugi argument ustawiony na 60% (C17) eliminuje w sumie sześć wartości skrajnych (zwraca ten sam wynik co dla średniej A4:A7).
