Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Ignore negative number in a formula

I have a sheet with my inflow and outflow for a small business.

I input income in a column called 'Pay'

I then calculate my GST (tax here in canada) with this formula - Pay × 0.05

When I have outflow, I use a negative number IE: -$54.44

I would like the above formula (Pay × 0.05) to ignore any negative numbers in the Pay column.


Can you help?

Posted on Jul 31, 2015 11:58 PM

Reply
Question marked as Best reply

Posted on Aug 1, 2015 2:22 AM

Hi Timothy,


The IF function is your friend.

User uploaded file

Formula in B2 (and Fill Down)

=IF(A2<0,0,A2×0.05)


If, then, else

If Pay is less than 0, then 0, else Pay x 0.05


Regards,

Ian.

4 replies

Aug 1, 2015 7:08 AM in response to Timothy B Hewitt

In addition to what Ian suggested, you can also use:


1) =max(0, <cell>)


where <cell> is the cell you want to make positive


2) a method SG suggested using conditional formatting:

User uploaded file

3) There is also another option (I think SG suggested) using a custom format:

select the cells you want to format, then open the Cell formatter and select "Create Custom Format...". I named the format "Only positive", add a rule

User uploaded fileUser uploaded file

User uploaded file





Ignore negative number in a formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.