Med Excel för att beräkna ett medelvärde baserat på kriterier - dummies

Du kan använda Excel för att beräkna ett medelvärde. Bild här: Du måste beräkna ett medelvärde från en lista med siffror, endast med siffror i listan som matchar ett tillstånd. Med andra ord, beräkna medelvärdet med siffror om de matchar ett givet kriterium. Excel är full av överraskningar, och för det här tricket kan du dra ut en bra funktion som heter AVERAGEIF.

Följande bild visar en lista över anställda, tillsammans med de år då de anställdes, hur många konton som de har öppnat hittills 2016 och (för vissa) ett meddelande som säger att de gör ett bra jobb.

Överst i kalkylbladet öppnas det genomsnittliga antalet konton per anställd 2015. Nedan visas det förväntade antalet konton som ska öppnas 2016. Medelvärdet 44 för 2015 är bara ett statiskt nummer. 2015 är över, och det här är det sista genomsnittet. Det är så - förra året! Vad sägs om 2016? Det är här det blir intressant.

För att beräkna det förväntade antalet konton som ska öppnas 2016 används en beräkning för att härleda numret som ett medel baserat på antalet konton som har öppnats hittills för året. Detta är en teknikledning som kan användas för att öka konkurrensen bland anställda. Med andra ord, istället för att bara fastställa ett förväntat antal, såsom 48 (vilket skulle vara en tillväxt på 10 procent över 2015), beräknas det förväntade antalet konton som öppnas baseras på hur anställda har utfört hittills för året.

I cell C4 används AVERAGEIF-funktionen. Så här är det inmatat:

= AVERAGEIF (C8: C16, ">" och C3)

Vad gör det är genomsnittet av öppna konton från listan över konton per anställd, endast med de siffror som är större än 44 öppnade år 2015. Detta bestäms av det andra argumentet i funktionen (">" & C3), som anger att medelvärdet använder endast värden som är större än 44, som finns i cell C3.

Till exempel har Julie hittills öppnat endast 40 konton, så hennes räkning används inte i genomsnittet. Å andra sidan har Yolanda öppnat 48 konton, så hennes räkning används för att beräkna genomsnittet.

När du bara medger siffrorna större än 2015 års 44, returneras ett högre antal för de förväntade öppningarna för 2016. Det beräknade värdet för 2016 vid denna punkt är 54. Vid sidan av varje anställds namn, meddelandet Great Job! visas om den anställde slår genomsnittet. Endast de som har öppnat minst 54 konton mottar meddelandet.

Hur? Genom att använda IF-funktionen! I varje cell där meddelandet visas IF-funktionen, jämförs huruvida antalet konton som anställda har öppnat hittills är lika med eller större än det genomsnitt som visas i cell C4.Här är formeln i cell D4 som visar att Harrison gör ett bra jobb:

= IF (C8> = $ C $ 4, "Great Job!", ")

IF-funktionen testar ett tillstånd. Det är sant, det första svaret visas. Om villkoret inte är sant, visas det andra svaret.

Så långt, så bra! I det här företaget belönas de aggressiva säljarena, och laggarderna får inte något beröm.

Det är dags att jämföra spelplanen. En sak som inte beaktas i det här exemplet är uthyrningsåret. Vissa anställda anställdes 2016, så i rättvishet måste antalet konton som öppnats hittills 2016 inte jämföras med den höga Medelvärdet.

Följande bild visar en uppdatering av arbetsbladet. Den enda IF-funktionen ersätts med en uppsättning nestade IF-funktioner: Den yttre IF tar hänsyn till om hyran år 2016 (det faktiska tillståndet är att se om hyresåret är större än 2015), och i så fall testar den inre IF-funktionen om arbetstagarens antal öppnade konton f eller året hittills är lika med eller större än hälften av det totala genomsnittet.

Nu är tre möjliga resultat möjliga för meddelandet:

Great Job! Fortsätt spricka! Året är inte över än! Inget meddelande

The Keep cracking! Meddelandet visas för de anställda 2016 som har öppnat ett antal konton som är lika med eller större än hälften av genomsnittet - det är ett givande budskap för dem som är nya och gör ett bra jobb. Resten av anställda är antingen superstjärnor eller behöver hjälp!

Formeln i Cell D8 (och för varje anställdrad i kolumn D) är strukturerad så här:

= IF ((A8> $ A $ 3), IF (C8> = ($ C $ 4/2) "Great Job!", "Fortsätt spricka! Året är inte över!"), IF (C8> = $ C $ 4, "Great Job!", "))