1 formula for various percentages calculations.

**mr_vic** · 04-29-2013, 09:22 AM

All,

I'm hoping that someone can help me create 1 formula to span various percentages.

I have attached a copy of an Actual vs Budget variance report. It is formated in a P/L style and details the variance as a £ value.

I want to add a % formula for various types of variances produced in the report.

In column E of the report is a very basic formula of Variance / Budget. This brings back an array of answers.

In column F is the desired % that I wish to achieve through 1 formula. I don't want to be using multiple formulas in this coulmn. I want to have the ability to just grag this formula down.

Can someone help me with this?

Appreciate all of your help!

mr_vic

**Special-K** · 04-29-2013, 09:48 AM

Your desired result is wrong in F6 it should be +20%

Also your formula in D6
=IF(OR(B6<>0,C6),C6-B6,"")

1. You shouldnt have an OR containing 1 condition, there's no point to it, you're actuallly performing and OR(C6) which kinda looks weird??
2. The formula doesnt make sense.
If B6<> zero result is C6 (fair enough)
If B6 =0 then result is STILL C6 since C6-B6 ie C6 minus zero is STILL C6

You need to edit your formula to decide what you really want

**Special-K** · 04-29-2013, 09:51 AM

Surely the column D formula should simply be C6-B6 ???

and your Desired result should be

=IF(C6<>0, D6/C6*100,"")

**mr_vic** · 04-29-2013, 10:02 AM

Special K,

To clarify, my desired result of -20% in cell F6 is correct. My sales are classed as a negative whilst my expenditure is called as a positive number.

In my attached spreadsheet example my budget sales were 50,000 whilst my actual sales were 40,000, hence i am under my sales target by 10,000 or -20%.

The formula you provided me with does part of the job. I think were close to an answer!

Aprreciate the reply.