Variance Report Percentage Error

kzappe

New Member
Joined
Jun 5, 2017
Messages
1
Hello Board,

I am adding a percentage variance column in excel to our monthly P&L.

COL A = Actual
COL B = Budget
COL C = $Variance
COL D = %Variance

My issue comes when I have $0.00 in both COL A and B (no budget or variance in that month), I want a 0% not the 100%. Here is the formula I have:

=ABS(IF(B,C/B,"100%"))

What am I missing to get 100% variance if COL A or B have a $0 but 0% if COL A AND B are $0.

Thanks!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
=ABS(IF(B2,C2/B2,1-(COUNTIF(A2:B2,0)=2)))

Format as %
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,722
You are not really evaluating the value of B
Also, you data types are different. If TRUE, calculate a number, if FALSE use text...

To restate your logic.
"if (COL A = 0 OR Col B =0) then 0 ELSE if (Col A = 0 AND Col B = 0) then 1 ELSE ABS(Col C/Col B)"
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The op is evaluating the value of B. Any non-zero number equates as TRUE, and zero equates as false.

=IF(B2,c2/b2,1) is a valid statement for example. It means if B2 is a non-zero value evaluate C2/B2 else return 1
 

Forum statistics

Threads
1,143,638
Messages
5,719,979
Members
422,254
Latest member
sayedsaid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top