Gavin.mc

Q: if then formula with cell format

image.png

Hey,

im doing some contract work with my business and working formula to get understanding on finacial positions.

as you can see I have set up a income at the top, expenses through the middle and then surplus and debt at the bottom.

 

I am now trying to create a system where I can work out the remaining left to be paid as I pay and expense and mark it in red.

So am wanting to auto sum the expense Coolum and the have the red formatted cells omitted from that calculation.

 

is this a if then formula?

Posted on Sep 14, 2016 5:40 PM

Close

Q: if then formula with cell format

  • All replies
  • Helpful answers

  • by Wayne Contello,

    Wayne Contello Wayne Contello Sep 14, 2016 6:18 PM in response to Gavin.mc
    Level 6 (19,282 points)
    iWork
    Sep 14, 2016 6:18 PM in response to Gavin.mc

    Number cannot sum based on formatting.  You could write an applescript to perform this task if you want and you would have to remember to cause the script to run.

     

    Never mind... I missed the fact that you are using Numbers on iOS.  I do not think you can run Applescript on Numbers for iOS.

     

    Here is an alternate way.  Place an underscore"_" in front of the value (or any other non-numeric character) to make the content of the cell a string rather than a number.  In that case the numeric value will be zero:

    Screen Shot 2016-09-14 at 8.17.23 PM.png

     

    you can even add conditional formatting to highlight red when the cell contains an underscore:

    Screen Shot 2016-09-14 at 8.18.01 PM.png