% Variance between 2 numbers

mark.gipson27746 · 05/03/19

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 · 05/03/19

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 · 09/16/19

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 · 09/17/19

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 · 09/24/19

Worked Thanks

melody.cooley71056 · 03/24/21

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

Andrew Kuhn · 05/05/21

we have the same question at Melody...

Tessa S · 07/21/22

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.

Formulas and Functions