% Variance between 2 numbers

mark.gipson27746

I am trying to figure out the % variance between 2 numbers ($40,190,187 and $40,279,091) I just need the formula can anyone help?

Ezra

Assuming you want the 

 

subtract X from Y to get an absolute value "V"

get the MAX of "X" and "Y" [to get the greater of the two]

"V"/(MAX of "X" & "Y")  [format this column as percentage ---- don't forget to increase your decimal place accuracy]

 

=ABS([Column2]21 - [Column3]21) / MAX([Column2]21:[Column3]21)

 

Jose Munoz

Ezra, 

 I did the calculation, but I get the value 1 instead of 100%, how can I increase the decimal place to show 100 in the formula? 

Thanks for the help?

Ezra

Just change the column or cell to a percentage format (next to currency format in the top toolbar)

.25 = 25%

.5 = 50%

1 = 100%

Jose Munoz

Worked Thanks

melody.cooley71056

This is great; however, what if you needed to show the percentage of increase OR decrease?

Andrew Kuhn

we have the same question at Melody...

Tessa S

To see if it is an increase or decrease remove the abs( ) part of the equation. It is what makes everything positive.

=[Column2]21 - [Column3]21) / MAX([Column2]21:[Column3]21

This will allow the positive and negative values to show.