20080323 need help with an excel formula (with too many arguments!)
 

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)

20080323 need help with an excel formula (with too many arguments!
 

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)