Home

Delta % calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-898785%22%20slang%3D%22en-US%22%3EDelta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898785%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anybody%20help%20me%20im%20desperate!!%3C%2FP%3E%3CP%3Ecolumn%20A1%20has%20a%20total%201500%3C%2FP%3E%3CP%3Ecolumn%20A2%20is%200%3C%2FP%3E%3CP%3Ecolumn%20A3%20is%20the%20delta%20(1500%20-%200)%3C%2FP%3E%3CP%3Ecolumn%20A4%20shows%20the%20percentage%20as%20%23DIV%2F0!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecannot%20remember%20how%20this%20formula%20works%3C%2FP%3E%3CP%3EHELP%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-898785%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898909%22%20slang%3D%22en-US%22%3ERe%3A%20Delta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898909%22%20slang%3D%22en-US%22%3E%3CP%3EHI%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421916%22%20target%3D%22_blank%22%3E%40JHill69%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDelta%20function%20in%20excel%2C%20basically%20tests%20whether%20two%20values%20are%20equal.%20It%20returns%201%20if%20both%20values%20are%20equal%20and%20returns%200%20otherwise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20just%20compare%20values%20in%20A1%20%26amp%3B%20A3%20you%20can%20use%26nbsp%3B%3CSTRONG%3E%3DDELTA(A3%2CA1)%20%3C%2FSTRONG%3Eor%20elaborate%20your%20requirements%20with%20example%20in%20excel%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898959%22%20slang%3D%22en-US%22%3ERe%3A%20Delta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898959%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20already%20got%20the%20delta%20thank%20you%2C%20i%20need%20to%20undertsand%20how%20to%20correctly%20formulate%20the%20%25%20of%20the%20delta%20without%20getting%20the%20DIV%2F0%20error%20when%201%20cell%20is%20on%20zero%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898992%22%20slang%3D%22en-US%22%3ERe%3A%20Delta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421916%22%20target%3D%22_blank%22%3E%40JHill69%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EA1%20would%20be%20the%20current%20total%3CBR%20%2F%3EA%3C%2FFONT%3E2%20the%20previous%20total%3C%2FP%3E%3CP%3EA3%20is%20the%20change%20from%20the%20previous%20value%3C%2FP%3E%3CP%3EA4%20(the%20percentage%20change)%20is%20the%20delta%20divided%20by%20the%20previous%20total.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20calculation%20is%20correct%20but%20for%20very%20small%20or%20negative%20prior%20values%20A2%2C%20it%20is%20just%20not%20very%20useful.%26nbsp%3B%20To%20suppress%20the%20error%20you%20could%20use%20a%20formula%3C%2FP%3E%3CP%3E%3D%20IF(%20A2%26gt%3B0%2C%20A3%2FA2%2C%20%22not%20available%22%20)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-899683%22%20slang%3D%22en-US%22%3ERe%3A%20Delta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-899683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20it%20depends%20on%20business%20logic%20behind.%20If%20I%20had%20-%2420%20loss%20on%20previous%20year%20and%20have%20%24100%20this%20one%2C%20difference%20is%20%24120%20and%20formula%20shows%20%22not%20available%22%20for%20%25%20difference.%3C%2FP%3E%0A%3CP%3EAnother%20point%2C%20if%20both%20A1%20and%20A2%20are%20zeros%2C%20usually%20%25%20is%20shown%20as%20zero.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithout%20knowing%20the%20content%20formula%20is%20a%20bit%20abstract.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900017%22%20slang%3D%22en-US%22%3ERe%3A%20Delta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20advantage%20of%20%25change%20is%20that%20it%20is%20scale%20invariant%3B%20it%20does%20not%20matter%20whether%20the%20denomination%20is%20%24%20or%20%24M.%26nbsp%3B%20Your%20delta%20of%20%24120%20may%20or%20may%20not%20be%20significant%3B%20a%20change%20from%20%241%2C000%2C000%20to%20%241%2C000%2C120%20is%20a%20drop%20in%20the%20ocean.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20other%20hand%2C%20in%20your%20example%20where%20the%20base%20value%20is%20-%2420%20(a%20loss)%20the%20percentage%20change%20is%20-600%25.%26nbsp%3B%20To%20be%20honest%2C%20that%20means%20very%20little%20to%20me%20and%20I%20would%20sooner%20view%20the%20actual%20amounts.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-900066%22%20slang%3D%22en-US%22%3ERe%3A%20Delta%20%25%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESure%2C%20%24%20I%20took%20only%20to%20be%20bit%20more%20close%20to%20real%20life%20with%20example.%3C%2FP%3E%0A%3CP%3EWish%20you%26nbsp%3B%3CSTRONG%3E%2B%3C%2FSTRONG%3E600%25%3C%2FP%3E%3C%2FLINGO-BODY%3E
JHill69
New Contributor

Can anybody help me im desperate!!

column A1 has a total 1500

column A2 is 0

column A3 is the delta (1500 - 0)

column A4 shows the percentage as #DIV/0!

 

cannot remember how this formula works

HELP

 

 

thank you

 

6 Replies

HI@JHill69 

 

Delta function in excel, basically tests whether two values are equal. It returns 1 if both values are equal and returns 0 otherwise.

 

If you want to just compare values in A1 & A3 you can use =DELTA(A3,A1) or elaborate your requirements with example in excel sheet.

 

Thanks

Tauqeer

Hello @tauqeeracma 

 

i already got the delta thank you, i need to undertsand how to correctly formulate the % of the delta without getting the DIV/0 error when 1 cell is on zero

 

can you help me?

@JHill69 

A1 would be the current total
A
2 the previous total

A3 is the change from the previous value

A4 (the percentage change) is the delta divided by the previous total.

 

The calculation is correct but for very small or negative prior values A2, it is just not very useful.  To suppress the error you could use a formula

= IF( A2>0, A3/A2, "not available" )

@Peter Bartholomew 

IMHO, it depends on business logic behind. If I had -$20 loss on previous year and have $100 this one, difference is $120 and formula shows "not available" for % difference.

Another point, if both A1 and A2 are zeros, usually % is shown as zero.

 

Without knowing the content formula is a bit abstract.

@Sergei Baklan 

The advantage of %change is that it is scale invariant; it does not matter whether the denomination is $ or $M.  Your delta of $120 may or may not be significant; a change from $1,000,000 to $1,000,120 is a drop in the ocean.  

 

On the other hand, in your example where the base value is -$20 (a loss) the percentage change is -600%.  To be honest, that means very little to me and I would sooner view the actual amounts.

@Peter Bartholomew 

Sure, $ I took only to be bit more close to real life with example.

Wish you +600%

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies