Variance % formula

[marilynfebruary]

Hi,
I've compiled a variance spreadsheet to track the changes from one month to another.
I'm trying to express these changes as a %, where there's no change, it should return 0% change.
But where there is a value in the variance spreadsheet which divides by zero in the previous month, the answer shows "#DIV/0". How do I express this as a 100% change?
=IF(VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)=0,0,VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)/VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0))

 

[ganeshpoojary05]

Try this:

=IF(VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0)=0,100,IF(VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)=0,0,VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)/VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0)))

 

[marilynfebruary]

Thanks for the quick response, but it's not quite right: I'll try to explain...
Aug Variance = August demand - July demand
So if Aug demand is 0 and July demand is 0, then Aug Variance should reflect 0%.
Its only if Aug demand = 5 and July demand =0, that Aug variance would =5
So the variance % is Aug variance /July demand (=5/0) and I need this answer to be 100% (change from 0 to something)

 

[marilynfebruary]

Thanks for the quick response, but it's not quite right: I'll try to explain...
Aug Variance = August demand - July demand
So if Aug demand is 0 and July demand is 0, then Aug Variance should reflect 0%.
Its only if Aug demand = 5 and July demand =0, that Aug variance would =5
So the variance % is Aug variance /July demand (=5/0) and I need this answer to be 100% (change from 0 to something)

 

[GlennUK]

So the variance % is Aug variance /July demand (=5/0) and I need this answer to be 100% (change from 0 to something)

That is not the correct answer. Any change from zero would be a percentage change of infinity. In all departments where this kind of calculation was required, I was requested to report this kind of change as 999%. Do you understand what I mean, and is that acceptable?

 

[marilynfebruary]

Hi Glenn, yes that is acceptable...
how should this be included in my current formula?
=IF(VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)=0,0,VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)/VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0))

 

[GlennUK]

The type of formula I've been using would be:

=IF(VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)=0,IF(VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0)>0,-1,0),IF(VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0)=0,9.99,MIN(VLOOKUP($C4,'Aug Variances'!$C:$AC,F$1,0)/VLOOKUP($C4,'July Demand Master'!$C:$AC,F$1,0)-1,9.99)))

 

[marilynfebruary]

Hi Glenn, that works, but instead of showing "-2%", it now shows "-102%"... how can I format it to show just -2% please?

 

[marilynfebruary]

Hi.. hope I'm not frustrating you

Any chance this formula could be rewritten to express the answers for a reduction as -2% instead of -102% and for an increase as 7% instead of -93%??

Thanks
MF

 

[shemayisroel]

Hi.. hope I'm not frustrating you

Any chance this formula could be rewritten to express the answers for a reduction as -2% instead of -102% and for an increase as 7% instead of -93%??

Thanks
MF

I think what you want is to add -1 at the end of yoru formula.

So =(yourformula)-1