Formula procentuālās novirzes aprēķināšanai programmā Excel vairumā gadījumu darbojas lieliski. Tomēr, ja etalona vērtība ir negatīva, formula sabojājas.
Piemēram, iedomājieties, ka sākat uzņēmējdarbību un pirmajā gadā plānojat ciest zaudējumus. Tātad jūs piešķirat sev negatīvu budžetu 10 000 USD apmērā. Tagad iedomājieties, ka pēc pirmā gada jūs faktiski nopelnījāt naudu, nopelnot 12 000 USD. Aprēķinot procentuālo novirzi starp jūsu faktiskajiem ieņēmumiem un budžetā paredzētajiem ieņēmumiem, jūs iegūtu –220%. Varat to izmēģināt ar kalkulatoru. 12 000 mīnus –10 000 dalīts ar –10 000 ir vienāds ar –220%.
Kā jūs varat teikt, ka jūsu procentuālā novirze ir –220%, ja skaidri nopelnījāt naudu? Problēma ir tāda, ka, ja jūsu etalona vērtība ir negatīvs skaitlis, matemātika apvērš rezultātus, liekot skaitļiem izskatīties dīvaini. Tā ir reāla problēma korporatīvajā pasaulē, kur budžeti bieži vien var būt negatīvas vērtības.
Labojums ir izmantot ABS funkciju, lai noliegtu negatīvo etalona vērtību:
=(C4-B4)/ABS(B4)
Attēlā šī formula ir izmantota šūnā E4, kas ilustrē dažādus rezultātus, ko iegūstat, izmantojot standarta procentuālās novirzes formulu un uzlaboto procentuālās novirzes formulu.
Programmas Excel ABS funkcija atgriež absolūto vērtību jebkuram skaitlim, ko tai nododat. Ievadot =ABS(-100) šūnā A1, tiktu atgriezts 100. ABS funkcija būtībā padara jebkuru skaitli par nenegatīvu skaitli. Izmantojot ABS šajā formulā, tiek noliegta negatīvā etalona ietekme (piemērā negatīvais 10 000 budžets) un tiek atgriezta pareiza procentuālā novirze.
Varat droši izmantot šo formulu visām savām procentuālās novirzes vajadzībām; tas darbojas ar jebkuru pozitīvo un negatīvo skaitļu kombināciju.
Alternatīvi aprēķini procentuālās izmaiņas ar negatīviem skaitļiem
Šeit ir daži veidi, kā pārbaudīt negatīva skaitļa klātbūtni un nodrošināt alternatīvu rezultātu.
1. metode. Negatīvām vērtībām nav rezultāta
Pirmā lieta, ko mēs varam darīt, ir pārbaudīt, vai kāds skaitlis ir negatīvs, un pēc tam parādīt tekstu, lai informētu lasītāju, ka procentuālās izmaiņas nevar aprēķināt.
Tālāk sniegtā formula to dara, izmantojot IF un MIN funkciju.
=IF(MIN(vecā vērtība, jauna vērtība)<=0,"--",(jauna vērtība/vecā vērtība)-1)
Lūk, kā formula darbojas:
Funkcijas IF loģiskais tests (MIN(vecā vērtība, jauna vērtība)<=0) atrod minimālo no divām vērtībām un pārbauda, vai vērtība ir mazāka par nulli vai vienāda ar to. Rezultāts būs TRUE vai FALSE.
Ja rezultāts ir TRUE, tad pastāv negatīvs skaitlis (vai nulle). Šajā gadījumā mēs varam parādīt kādu tekstu, ko pastāstīt lasītājam. Tas var būt jebkas, ko vēlaties. Es tikko izmantoju divas domuzīmes “–”. Varat arī likt tam atgriezt kļūdu N/A, izmantojot funkciju NA() vai jebkuru citu tekstu, kas ļauj lasītājam zināt, ka procentuālās izmaiņas nevar aprēķināt.
Ja rezultāts ir FALSE, tad procentuālo izmaiņu formula tiek izmantota, lai atgrieztu procentuālās izmaiņas diviem pozitīvajiem skaitļiem.
Šī formula apstrādā arī dalīšanu ar nulli (#DIV/0!), tāpēc mums tā nav jāiekļauj funkcijā IFERROR.
2. metode: parādiet pozitīvas vai negatīvas izmaiņas
Wall Street Journal ceļvedī teikts, ka tā peļņas pārskatos tiek parādīts “P” vai “L”, ja ir negatīvs skaitlis un uzņēmums ir guvis peļņu vai zaudējumus.
Mēs varētu izmantot šo pašu metodoloģiju, lai pastāstītu saviem lasītājiem, vai izmaiņas bija pozitīvas (P) vai negatīvas (N), ja kāda no vērtībām ir negatīva.
Tālāk norādītā formula to pārbauda ar papildu IF funkciju.
=IF(MIN(vecā vērtība, jauna vērtība)<=0,IF((jauna vērtība — vecā vērtība)>0,"P","N"),(jauna vērtība/vecā vērtība)-1)
Lūk, kā formula darbojas:
Mēs sākam ar to pašu loģisko testu, lai noteiktu, vai pastāv negatīva vērtība, izmantojot funkciju MIN.
Pēc tam tiek izmantota otrā IF funkcija, lai noteiktu, vai izmaiņas no vecās uz jauno ir pozitīvas vai negatīvas.
IF((jauna vērtība — vecā vērtība)>0,"P","N")
Šis IF paziņojums atgriež “P” pozitīvām izmaiņām un “N” negatīvām izmaiņām.
Ja abi skaitļi ir pozitīvi, rezultāta attēlošanai izmanto procentuālo izmaiņu formulu.