Hope someone can help me he

let's say I have table like the following with different situations of
'Target' and 'Result' and I want to calculate deviation from 'Target'
in an Excel sheet:

Target Result Deviation
30 40 33,33
40 21 -47,50
0 23,5 23,50
0 0 0,00


In order to do so, I would like to construct a formula which can check
out the figures and return wtih the deviation result in %. The first
formula below can be copied and covers easily line 1; 2 and 4, but not
line 3 (as you cannot divide anything with 1% of '0'!)

=IF(C2=A2;0;(C2-A2)/(A2/100))

In order to calculate line 3, I need another formula like:

=IF(C4=A4;0;(C4)/(100/100))

It would help me immensely, if the two formulas could be combined to
only one. I have tried to do do, but EXCEL returns with an error
message telling me that there are too many arguments in the formula.

However - my logic tells me it SHOULD be possible to combine the two
to one. CAN ANYBODY HELP?

Best regards,

jesper (in Denmark)

Jesper:

You need to combine the two formulas with another if :

=IF(C2=A2;0;if(a2<0,(C2-A2)/(A2/100),C2))

Note: it is only C2 and not c2/(100/100) as 100/100 = 1.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

Hope someone can help me he

let's say I have table like the following with different situations of
'Target' and 'Result' and I want to calculate deviation from 'Target'
in an Excel sheet:

Target Result Deviation
30 40 33,33
40 21 -47,50
0 23,5 23,50
0 0 0,00


In order to do so, I would like to construct a formula which can check
out the figures and return wtih the deviation result in %. The first
formula below can be copied and covers easily line 1; 2 and 4, but not
line 3 (as you cannot divide anything with 1% of '0'!)

=IF(C2=A2;0;(C2-A2)/(A2/100))

In order to calculate line 3, I need another formula like:

=IF(C4=A4;0;(C4)/(100/100))

It would help me immensely, if the two formulas could be combined to
only one. I have tried to do do, but EXCEL returns with an error
message telling me that there are too many arguments in the formula.

However - my logic tells me it SHOULD be possible to combine the two
to one. CAN ANYBODY HELP?

Best regards,

jesper (in Denmark)