% Forumla Problem

[masond3]

Hey Guys

This is such a stupid question to ask, but i have hit a mental block

Aim –

If
Target Volume = 200
Received Volume =13
% = 6.5%
However as the received volume is below 100% i want it displayed as -6.5%

If however

Target Volume = 200
Received Volume =205
% = 103%

I want the formula to say +3%

Column A (TARGET VOLUME)
Column B (RECIEVED VOLUME)
COLUMN C (FORMULA)


Looking forward to your helo

Regards
D

[Sam Capricci]

this would be one way...
=ABS(IF((A2/B2)<1,(A2/B2),1-(A2/B2)))

[vamosj]

Adjust cell format accordingly for decimal places and use this formula.


=IF(B2<A2,-B2/A2,(B2-A2)/B2)

[martindwilson]

=if(a1/b1>1,b1/a1,1-b1/a1)*-1

[newdoverman]

This could work for you. Enter in C1 and format as Percentage.

Please Login or Register  to view this content.

[sktneer]

You may try this also...........

Please Login or Register  to view this content.

[martindwilson]

i think it comes down to this cant get any shorter!
=MIN(B1/A1,1-B1/A1)*-1
well maybe
=-MIN(B1/A1,1-B1/A1)

[Kaper]

challenge accepted
2 chrs saved :

Please Login or Register  to view this content.

by the way - one coukd say that 3 chrs, because you can write in

Please Login or Register  to view this content.

excel will automatically add "=" in front of it.

[martindwilson]

too late id already edited it before you posted

[Kaper]

Indeed, you managed on time

So at least next of equal length:

Please Login or Register  to view this content.