# % Variance between 2 numbers

edited 12/09/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?

• âś­âś­âś­
edited 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)

Â

• âś­âś­âś­âś­

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?

• âś­âś­âś­

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

.25 = 25%

.5 = 50%

1 = 100%

• âś­âś­âś­âś­

Worked Thanks

• âś­âś­âś­

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

• âś­

we have the same question at Melody...

• âś­

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!