Tein seuraavanlaisen kaavan =JOSVIRHE(Keskiarvo(X1:X50);"") ja toimii muuten hyvin mutta jos tuolle välille 1 - 50 jonkun solun arvo sattuukin olemaan 0 niin silloin kaava ei enää toimikaan niin kuin pitäisi vaan se laskee myös tuon 0:n mukaan keskiarvoon ja saatu keskiarvo ei pidä paikkaansa.
Onko mahdollista muuttaa tuota kaavaa niin, että se ei huomioisi solun 0 arvoa solujen 1 - 50 välillä vaikka solussa X34 vaan jättäisi sen pois keskiarvolasennasta?
Onko mahdollista muuttaa tuota kaavaa niin, että se ei huomioisi solun 0 arvoa solujen 1 - 50 välillä vaikka solussa X34 vaan jättäisi sen pois keskiarvolasennasta?
Vastaukset 20
Vanhimmat
Keskiarvon laskentaan kannattaa soveltaa .JOS-versiota.
=KESKIARVO.JOS(X1:X50;"<>0")
Pitäisikö nuo 0 solut olla oikeasti tyhjiä, miksi ne muutetn pitäisi jättää huomioimatta? Tyhjät solut jätetään ottamatta huomioon kerkiarvossa.
Kuten totesit, Excel ei laske tyhjää solua mukaan keskiarvoon (ei laske sitä nollaksi), mutta käsinkirjoitettu tai kaavalla saatu nolla lasketaan mukaan vastaukseen, joka muuttaa keskiarvoa.
Jos lukujoukossa on oikeasti nolla se on otettava huomioon keskiarvossa, sillä onhan nolla lukuarvo siinä missä joku muukin. Muuten keskiarvo on väärin. Ykkösen ja nollan keskiarvo ei ole yksi vaan puoli. Sen sijaan, yhden ja tyhjän keskiarvo on yksi.
Jos jokin kaava antaa tuloksen 0 jossain laittomassa tilanteessa, on tuota kaavaa korjattava siten, että se antaa 0:n sijasta tyhjän.
"Pitäisikö nuo 0 solut olla oikeasti tyhjiä, miksi ne muutetn pitäisi jättää huomioimatta? Tyhjät solut jätetään ottamatta huomioon kerkiarvossa."
Siksi tuo 0 pitää jättää huomioimatta, kun tuo kaava laskee pystyrivin keskiarvo ja vaakarivelle saattaa johonkin soluun tulla arvo 0 vaakarivillä olevan kaavan laskemana ja sitä tai useampaa 0:aa ei saa huomioida keskiarvoa laskettaessa vaan solua pitää kohdella niin kuin se olisi tyhjä
"
"Keskiarvon laskentaan kannattaa soveltaa .JOS-versiota.
=KESKIARVO.JOS(X1:X50;"<>0")"
Tuossa pitää olla tuo JOSVIRHE siksi, että alussa pystyrivillä olevat solut ova tyhjiä ja tuohon kohdesoluun tulee virheilmoitus jos pystyrivin soluissa ei ole mitään lukuja, kun haluan myös kohdesolun olevan tyhjänä ennen kuin pysty rivelle lasketaan tuloja.
Kokeilin lisätä tuon ... ;"<>0");"") kaavaan mutta ei laske mitään vaan jättää kohdesolun tyhjäksi?
Voiko johtua siitä, että kun vaakarivi laskee tuloksen 0,00 niin excel ei ymmärrä sitä, kun arvo ei ole puhdas 0?
Tuolla kaavalla lasketaan keskiarvo soluista, joiden arvo ei ole välillä -0,01..0,01:
=(SUMMA.JOS(X1:X50;">0,01") SUMMA.JOS(X1:X50;"<-0,01"))/(LASKE.JOS(X1:X50;">0,01") LASKE.JOS(X1:X50;"<-0,01"))
Toimii hienosti tuollakin kaavalla mutta noissa X1 - X50 soluissa täytyy olla joku luku ja jos ja kun ei ole vaan taulukko on alunperin tyhjä niin keskiarvon laskentasoluun tulee virheilmoitus #JAKO/0!
Siinä tapauksessa pitää tarkastaa ensin vielä yhdellä jossilla, että jakaja ei ole nolla.
Alla oleva versio palauttaa silloin arvon nolla. Kaavan viimeinen 0 ennen viimeistä sulkumerkkiä on siis se paluuarvo, kun X1:X50-alueella ei ole ehdot täyttäviä lukuja. Sen 0:n voi korvata vaikka "":llä, niin solussa ei näy silloin mitään.
=JOS((LASKE.JOS(X1:X50;">0,01") LASKE.JOS(X1:X50;"<-0,01"))>0;(SUMMA.JOS(X1:X50;">0,01") SUMMA.JOS(X1:X50;"<-0,01"))/(LASKE.JOS(X1:X50;">0,01") LASKE.JOS(X1:X50;"<-0,01"));0)
Sinulla "toimisiko", kun tuntuu tuo excel olevan oikein hyvin hallussa niin kysynkin vielä jatkokysymyksen.
Jos haluan myös =SUMMA(X1:X50) yhteenlaskusolun olevan tyhjä, eikä 0 niin kuin se on normaalisti, kun laskentasoluissa ei ole mitään niin miten tuota =SUMMA... solun kaavaa pitäsi muuttaa?
Tarkastetaan ensin, että onko summa jotain muuta kuin 0. Jos on, niin näytetään summa, muutoin "".
=JOS(SUMMA(X1:X50)<>0;SUMMA(X1:X50);"")
Hämmennetään vielä lisää. Mitäs jos SUMMA(X1:X50) onkin 0? Kaava näyttää tyhjää, vaikka sen pitäisi näyttää nollaa.
Yksi tapahan on tehdä oma funktio, tässä esimerkki:
Function OmaSumma(alue As Range)
Dim summa
Dim laskuri As Integer
summa = 0
laskuri = 0
For Each solu In alue.Cells
If IsNumeric(solu.Value) And (Len(solu.Value) > 0) Then
summa = summa solu.Value
laskuri = laskuri 1
End If
Next solu
If (laskuri > 0) Then
OmaSumma = summa
Else
OmaSumma = ""
End If
End Function
Tämä voisi olla yksi vaihtoehto:
=JOS(ONLUKU(X1:X50);SUMMA(X1:X50);"")
Tämä funktio ei toimi vaan jättää laskentasolun tyhjäksi
Solujen summa ei tuossa taulukossani ole 0 muuten kuin alkuvaiheessa ennen kuin soluihin X... on syötetty jotain lukuja
Tämä kaava toimii niin kuin pitääkin ja nyt taulukko on puhdas ennen kuin siihen syötetään lukuja kaavojen laskettaviksi. Hienoa, kun on ihmisiä, jotka osaavat ja myös jakavat osaamistaan!
Kiitos avusta taas uudestaan!
Asetuksista voisi myös ruksata kohdan "Älä näytä nollarvoja" tai soluun "Ehdollinen muotoilu". Jos arvo =0 ,fontin väri pohjan väriseksi.
Voisihan sitä noinkin kikkailla mutta jos asetuksista tai muotoilujen kautta blokkaa tuon 0:n jossain kaavassa ja tuo 0 pitäisikin olla mukana jossain toisessa kaavassa niin silloin taas asetuksista ja muotoiluista ja sitten toisen taulukon kaavat eivät toimi niin kuin pitää eli ei noin vaan tekemällä kaavat oikein
Kokeilin huvikseni ja kaavalla =JOSVIRHE(Keskiarvo.JOS(X1:X50;"<>0");"") ja toimii niin kuin pitääkin.
Kiitos neuvoista. Soveltaen onnistui