Negative financial %'s - IF OR ABS 1

[stoke]

I am creating a financial kpi - actual vs budget.
I need to dispaly the % difference pos / neg between the two and use that outcome to conditional format by colour.

this formula gives kpi column:
=(IF(OR(C45<0,D45<0),((D45/C45)-1),(IF(C45>0&D45>0,((ABS(D45/C45)-1)),ABS((D45/C45)+2)))))

Budget Actual kpi
Septembe 11,521 -4,039 -135%
October -10,577 -12,178 15%
November 3,027 4,405 46%
Decembe -14,159 13,674 -197%

The october scenario of two neg figures needs to give an outcome of -15%, really appreciate guidnace on this last scenario or a simpler solution!

Thanks, Stoke

 

[Lilliabeth]

I don't know if I can help you, but this doesn't look right:

C45>0&D45

 

[GlennUK]

Lilliabeth is right, your formula looks wrong.

And surely the December calc should result in a positive KPI.

So how about:
=(D45-C45)/C45*SIGN(C45)


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Gavona]

What I use is:
=(D45-C45)/ABS(C45)
Slightly shorter than Glenn's.


Gavin

 

[GlennUK]

Looking at my spreadsheets, what I actually use is even longer...
=IF(C45=0,999.99,(D45-C45)/ABS(C45))

This gives 99999% when a budget figure of zero is used.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

Ah, just found another version ( even longer )...

=IF(C45=0,IF(D45=0,0,999.99),(D45-C45)/ABS(C45))

This gives 99999% when a budget figure of zero is used, but gives zero when both budget and actual are zero.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Gavona]

Nice one Glenn, I never quite got around to dealing with those issues (as far as I can recall)


Gavin

 

[stoke]

Champion effort, thank you.
Think i spent too long staring at that one!

Cheers!