Variance with IF

[jdg347]

Hi! I have a problem with calculating the variance of 2 columns.

Column B has a list of values already in it and column A is empty. What I want to do is as a value has been entered in column A, I want to calculate the percent difference between columns A and B. BUT only the % difference between the total of values entered in A and their corresponding B value total, NOT the difference between whats been entered in A and ALL of B. I also need to know if the total of what is entered in A is currently above or below the corresponding value total entered in B (ie, + or - %).

I tried using an if with sum, but didn't have any sucess. Here is the syntax I tried.

=IF(qryCableRoutingSchedule!B:B<> "", IF(SUM(qryCableRoutingSchedule!R2:R351)>A2,(SUM(qryCableRoutingSchedule!R2:R351)-A2)/(SUM(qryCableRoutingSchedule!R2:R351)),-1*(SUM(qryCableRoutingSchedule!R2:R351)-A2)/(SUM(qryCableRoutingSchedule!R2:R351))),0)

 

[Andrew Poulsom]

To return the sum of B2:B11 where A2:A11 is not blank:

=SUMIF(A2:A11,"<>",B2:B11)

Hope that puts you on track.

 

[LxQ]

I tried withe a couple of cells.. this calculates the percent difference from values in A and their corresponding values in B

=(SUM(A2:A7)-SUMIF(A2:A7,">0",B2:B7))/SUMIF(A2:A7,">0",B2:B7)

 

[jdg347]

Thanks to all! Was able to make it work. Cheers!