Help with negative number please

[Eamon]

Hello,

Appreciate any help please

For example In O10:O1000 I have numbers such as...
56.50
100.00
-100.00
324.66
etc!
In Q10 I have the number in O10 less 5%
In Q11 I have =+IF(O11="","",Q10+O11/100*95)
to keep a running total of the numbers in column "O" less 5%
This is working fine until I come to a negative number in column "O"
i.e. -100.00, when the number is negative I don't want to deduct 5%, how can
I change the formula to do this please.

Regards,

Eamon

 

[Guest]

=IF(O11<0),O11,IF(O11="","",Q10+(O11/100)*95))

Dave

 

[Eamon]

Dave,

Thanks for your reply but am getting error in formula?

Eamon

 

[Guest]

What error are you getting?

 

[Eamon]

Dave,

When I copy and past =IF(O11<0),O11,IF(O11="","",Q10+(O11/100)*95)) into Q11
I get the message the formula you typed contains an error and when I click
OK "<0" the "0" is highlighted in black.

Hope this helps.

Eamon

 

[David Biddulph]

Your parentheses don't match, & the error being highlighted is that you
don't have enough arguments for the first IF statement.

To cure that, try
=IF(O11<0,O11,IF(O11="","",Q10+(O11/100)*95))
or
=IF(O11<0,O11,IF(O11="","",Q10+O11*95%))

But that may not be what you want. If you're trying to produce a cumulative
total, you may want something more like
=IF(O11<0,Q10+O11,IF(O11="",Q10,Q10+O11*95%))

 

[Guest]

Re the error only, it looks like the if got "closed" early. Try this:

=IF(O11<0,O11,IF(O11="","",Q10+(O11/100)*95))

 

[Eamon]

David,

Just tried =IF(O11<0,Q10+O11,IF(O11="",Q10,Q10+O11*95%)) and works perfect
many thanks.
Thanks to David F as well.
Your efforts are much appreciated. Thanks again.

Best regards,

Eamon

 

[Eamon]

Kevin,

Thanks as well for your help, but have it solved with David's formula.

Best regards,

Eamon