Formúlan til að reikna út prósentu frávik innan Excel virkar fallega í flestum tilfellum. Hins vegar, þegar viðmiðunargildið er neikvætt gildi, brotnar formúlan niður.
Ímyndaðu þér til dæmis að þú sért að stofna fyrirtæki og búist við að tapa fyrsta árið. Svo þú gefur þér fjárhagsáætlun upp á neikvæð $10.000. Ímyndaðu þér nú að eftir fyrsta árið þitt hafir þú í raun og veru þénað peninga og þénað 12.000 $. Með því að reikna út prósentu frávik milli raunverulegra tekna þinna og áætlaðra tekna myndi gefa þér –220%. Þú getur prófað það á reiknivél. 12.000 mínus –10.000 deilt með –10.000 jafngildir –220%.
Hvernig geturðu sagt að prósentu frávik þitt sé –220% þegar þú græddir greinilega? Jæja, vandamálið er að þegar viðmiðunargildið þitt er neikvæð tala, snýr stærðfræðin niðurstöðunum við, sem veldur því að tölur líta asnalegar út. Þetta er raunverulegt vandamál í fyrirtækjaheiminum þar sem fjárhagsáætlanir geta oft verið neikvæð gildi.
Lagfæringin er að nýta ABS aðgerðina til að afneita neikvæða viðmiðunargildinu:
=(C4-B4)/ABS(B4)
Myndin notar þessa formúlu í reit E4 og sýnir mismunandi niðurstöður sem þú færð þegar þú notar staðlaða prósentuafbrigðisformúluna og bættu prósentuafbrigðisformúluna.
ABS fall Excel skilar algildi fyrir hvaða tölu sem þú sendir til hennar. Að slá inn =ABS(-100) í reit A1 myndi skila 100. ABS fallið gerir í raun hvaða tölu sem er að óneikvæðri tölu. Notkun ABS í þessari formúlu dregur úr áhrifum neikvæða viðmiðsins (neikvæð 10.000 fjárhagsáætlun í dæminu) og skilar réttu prósentu fráviki.
Þú getur örugglega notað þessa formúlu fyrir allar þarfir þínar prósenta frávik; það virkar með hvaða samsetningu sem er af jákvæðum og neikvæðum tölum.
Skiptu um útreikninga fyrir prósentubreytingar með neikvæðum tölum
Hér eru nokkrar leiðir til að prófa hvort neikvæð tala sé til staðar og gefa upp aðra niðurstöðu.
Aðferð #1: Engin niðurstaða fyrir neikvæðar
Það fyrsta sem við getum gert er að athuga hvort önnur hvor talan sé neikvæð og birta síðan texta til að segja lesandanum að ekki væri hægt að reikna út prósentubreytingu.
Eftirfarandi formúla gerir þetta með IF falli og MIN falli.
=IF(MIN(gamalt gildi, nýtt gildi)<=0,"--",(nýtt gildi/gamalt gildi)-1)
Svona virkar formúlan:
Rökrétt próf IF fallsins (MIN(gamalt gildi, nýtt gildi)<=0) finnur lágmarkið af tveimur gildum og prófar hvort gildið er minna en eða jafnt og núll. Niðurstaðan verður annað hvort TRUE eða FALSE.
Ef niðurstaðan er SÖNN, þá er neikvæð tala (eða núll) til. Í þessu tilfelli getum við birt texta til að segja lesandanum. Þetta getur verið allt sem þú vilt. Ég notaði bara tvö strik „–“. Þú gætir líka látið það skila N/A villu með NA() fallinu eða öðrum texta sem lætur lesandann vita að ekki væri hægt að reikna út prósentubreytinguna.
Ef niðurstaðan er FALSE, þá er prósentubreytingarformúlan notuð til að skila prósentubreytingunni á jákvæðu tölunum tveimur.
Þessi formúla sér einnig um deilingu með núll (#DIV/0!), svo við þurfum ekki að vefja hana inn í IFERROR fallið.
Aðferð #2: Sýndu jákvæða eða neikvæða breytingu
Wall Street Journal leiðarvísirinn segir að tekjuskýrslur þess sýni „P“ eða „L“ ef það er neikvæð tala og fyrirtækið skilaði hagnaði eða tapi.
Við gætum notað sömu aðferðafræði til að segja lesendum okkar hvort breytingin væri jákvæð (P) eða neikvæð (N) þegar annað hvort gildi er neikvætt.
Eftirfarandi formúla prófar þetta með IF-aðgerð til viðbótar.
=IF(MIN(gamalt gildi, nýtt gildi)<=0,IF((nýtt gildi - gamalt gildi)>0,"P","N"),(nýtt gildi/gamalt gildi)-1)
Svona virkar formúlan:
Við byrjum á sama rökrétta prófinu til að ákvarða hvort neikvætt gildi sé til með því að nota MIN fallið.
Annað EF-fall er síðan notað til að ákvarða hvort breytingin frá gömlum í ný sé jákvæð eða neikvæð.
IF((nýtt gildi – gamalt gildi)>0,"P","N")
Þessi IF-yfirlýsing skilar „P“ fyrir jákvæða breytingu og „N“ fyrir neikvæða breytingu.
Ef báðar tölurnar eru jákvæðar er prósentubreytingaformúlan notuð til að sýna niðurstöðuna.