Formeln för att beräkna procentuell varians i Excel fungerar utmärkt i de flesta fall. Men när referensvärdet är ett negativt värde bryts formeln ner.
Föreställ dig till exempel att du startar ett företag och räknar med att gå med förlust det första året. Så du ger dig själv en budget på negativa $10 000. Föreställ dig nu att du efter ditt första år faktiskt tjänade pengar och tjänade 12 000 $. Att beräkna den procentuella skillnaden mellan dina faktiska intäkter och budgeterade intäkter skulle ge dig –220 %. Du kan prova det på en miniräknare. 12 000 minus –10 000 dividerat med –10 000 är lika med –220 %.
Hur kan du säga att din procentuella avvikelse är –220% när du klart tjänat pengar? Tja, problemet är att när ditt referensvärde är ett negativt tal, inverterar matematiken resultaten, vilket gör att siffror ser galna ut. Detta är ett verkligt problem i företagsvärlden där budgetar ofta kan vara negativa värden.
Fixningen är att utnyttja ABS-funktionen för att negera det negativa riktmärket:
=(C4-B4)/ABS(B4)
Figuren använder den här formeln i cell E4, och illustrerar de olika resultaten du får när du använder standardformeln för procentvarians och den förbättrade procentuella variansformeln.
Excels ABS-funktion returnerar det absoluta värdet för alla tal du skickar till den. Att skriva in =ABS(-100) i cell A1 skulle returnera 100. ABS-funktionen gör i princip vilket tal som helst till ett icke-negativt tal. Om du använder ABS i den här formeln förnekas effekten av det negativa riktmärket (den negativa budgeten på 10 000 i exemplet) och returnerar den korrekta procentuella variansen.
Du kan säkert använda denna formel för alla dina procentuella variansbehov; det fungerar med alla kombinationer av positiva och negativa tal.
Här är några sätt att testa om det finns ett negativt tal och ge ett alternativt resultat.
Det första vi kan göra är att kontrollera om någon av siffrorna är negativa och sedan visa lite text för att tala om för läsaren att en procentuell ändringsberäkning inte kunde göras.
Följande formel gör detta med en OM-funktion och MIN-funktion.
=OM(MIN(gammalt värde, nytt värde)<=0,"--",(nytt värde/gammalt värde)-1)
Så här fungerar formeln:
Det logiska testet av OM-funktionen (MIN(gammalt värde, nytt värde)<=0) hittar minimum av de två värdena och testar om värdet är mindre än eller lika med noll. Resultatet blir antingen TRUE eller FALSE.
Om resultatet är SANT finns det ett negativt tal (eller noll). I det här fallet kan vi visa lite text för att berätta för läsaren. Det här kan vara vad du vill. Jag använde bara två streck "–". Du kan också få den att returnera ett N/A-fel med funktionen NA() eller någon annan text som låter läsaren veta att den procentuella förändringen inte kunde beräknas.
Om resultatet är FALSK, används formeln för procentuell förändring för att returnera den procentuella förändringen på de två positiva talen.
Den här formeln hanterar också divideringen med noll (#DIV/0!), så vi behöver inte slå in den i IFERROR-funktionen.
Wall Street Journal-guiden säger att dess resultatrapporter visar ett "P" eller "L" om det finns ett negativt tal och företaget redovisar vinst eller förlust.
Vi skulle kunna använda samma metod för att berätta för våra läsare om förändringen var positiv (P) eller negativ (N) när något av värdet är negativt.
Följande formel testar detta med en extra OM-funktion.
=OM(MIN(gammalt värde, nytt värde)<=0,OM((nytt värde - gammalt värde)>0,"P","N"),(nytt värde/gammalt värde)-1)
Så här fungerar formeln:
Vi börjar med samma logiska test för att avgöra om det finns ett negativt värde med hjälp av MIN-funktionen.
En andra OM-funktion används sedan för att avgöra om förändringen från gammalt till nytt är positivt eller negativt.
IF((nytt värde – gammalt värde)>0,”P”,”N”)
Denna IF-sats returnerar ett "P" för en positiv förändring och ett "N" för en negativ förändring.
Om båda siffrorna är positiva används formeln för procentuell förändring för att visa resultatet.
Hur man blockerar Microsoft Word från att öppna filer i skrivskyddat läge på Windows Microsoft Word öppnar filer i skrivskyddat läge, vilket gör det omöjligt att redigera dem? Oroa dig inte, metoderna finns nedan
Så här åtgärdar du fel vid utskrift av felaktiga Microsoft Word-dokument Fel vid utskrift av Word-dokument med ändrade teckensnitt, röriga stycken, saknad text eller förlorat innehåll är ganska vanligt. Men gör det inte
Om du har använt pennan eller överstrykningspennan för att rita på dina PowerPoint-bilder under en presentation, kan du spara ritningarna till nästa presentation eller radera dem så att nästa gång du visar den börjar du med rena PowerPoint-bilder. Följ dessa instruktioner för att radera ritningar med penna och överstrykningspenna: Radera linjer ett på […]
Stilbiblioteket innehåller CSS-filer, XSL-filer (Extensible Stylesheet Language) och bilder som används av fördefinierade mallsidor, sidlayouter och kontroller i SharePoint 2010. För att hitta CSS-filer i stilbiblioteket på en publiceringswebbplats: Välj Webbplatsåtgärder→Visa Allt webbplatsinnehåll. Innehållet på webbplatsen visas. Style-biblioteket ligger i […]
Överväldiga inte din publik med gigantiska siffror. I Microsoft Excel kan du förbättra läsbarheten för dina instrumentpaneler och rapporter genom att formatera dina siffror så att de visas i tusentals eller miljoner.
Lär dig hur du använder SharePoints sociala nätverksverktyg som låter individer och grupper kommunicera, samarbeta, dela och ansluta.
Julianska datum används ofta i tillverkningsmiljöer som en tidsstämpel och snabbreferens för ett batchnummer. Denna typ av datumkodning tillåter återförsäljare, konsumenter och serviceagenter att identifiera när en produkt tillverkades och därmed produktens ålder. Julianska datum används också i programmering, militären och astronomi. Annorlunda […]
Du kan skapa en webbapp i Access 2016. Så vad är en webbapp egentligen? Tja, webben betyder att den är online, och appen är bara en förkortning för "applikation". En anpassad webbapp är en onlinedatabasapplikation som nås från molnet med en webbläsare. Du bygger och underhåller webbappen i skrivbordsversionen […]
De flesta sidor i SharePoint 2010 visar en lista med navigeringslänkar i snabbstartsfältet till vänster på sidan. Snabbstartsfältet visar länkar till utvalt webbplatsinnehåll som listor, bibliotek, webbplatser och publiceringssidor. Snabbstartsfältet innehåller två mycket viktiga länkar: Länken Allt webbplatsinnehåll: […]
För enkla problem hittar Solver i Excel vanligtvis snabbt de optimala Solver-variabelvärdena för objektivfunktionen. Men i vissa fall har Solver problem med att hitta Solver-variabelvärdena som optimerar objektivfunktionen. I dessa fall visar Solver vanligtvis ett meddelande eller ett felmeddelande som beskriver eller diskuterar problemet som […]
