Forum Discussion
FORMULA FOR CALCULATING VARIANCE FROM A NUMBER
I WANT TO CALCULATE VARIANCE IN %AGE. LETS SAY A1 HAS NUMBER 10. IN A2 THEIR CAN BE A FIGURE GREATER AND LOWER THAN 10. I WANT TO CALCULATE THE VARIATION FROM A1 IN %AGE AND ALL VARIATION TO BE WITH MINUS WHETHER IT IS BEING COMPARED TO A GREATER OR A LOWER NUMBER
1 Reply
First, please turn off your Caps-Lock key when posting. That's long been established as the visual equivalent of YELLING, which I'm sure you don't mean to be doing. It's also less comfortable on the eyes when it comes to reading.
Second: to answer your question, if A3 is the cell that is to show that desired variance, your formula could be =-ABS((A1-A2)/A1)
The (A1-A2)/A1 determines the percentage of the difference between the two numbers, and it will be positive or negative depending on which is greater.
The ABS determines the Absolute of the (A1-A2)/A1
The leading "-" sign turns it negative regardless of what it was above.
Is that what you were wanting?
