Varaince Calculation

[robbydogg]

Variance Calculation

Hello,
I have a bit of a difficult one for you (or maybe not). I have been asked to produce a query for around 200 records which calculates a variance as a % of plan vs actual
I understand the standard calculation for this is: (Actual-Plan)/Actual but unfotunately it seems to fall over at certain measures.

an example of the data is:
Actual Plan1000905050-15230-2300030-50-6850-68
if i use the standard calc - it works fine with numbers which are both positive or both negative (kind of) but when they vary it seems to be inaccurate. can anyone help and gain my admiration??

Thanks
Rob

 

[ajetrumpet]

rob,

the crosstab query wizard in access produces a variance for you. you may want to check that out. it is one of the options. you can use crosstabs just like simple queries too...

 

[GUIDO22]

Your data example - can you explain in alittle more detail what the string of letters and number actually is. Is this the result / the figures you have to apply calculations too....?????