Scott F
Level 1 (74 points)
Mac OS X

Q: ytd

Mac OS 10.11.6 (15G1004)

Numbers 3.6.2

Screen Shot 2016-09-23 at 10.06.49 AM.png

I want a calculation I can enter in the YTD Profit cell that works for all cells in the row.

 

I tried SUM but I can’t make the beginning of the range absolute and the end relative.

 

I tried adding the above Profit value with the previous cell value, but that returned an error since the previous cell is text.

 

Currently I have a hidden column with a 0 in B8. It works, but it feels sloppy.

Posted on Sep 23, 2016 10:08 AM

Close
Scott F

Q: ytd

  • All replies
  • Helpful answers

  • by Barry,

    Barry Barry Sep 23, 2016 3:32 PM in response to Scott F
    Level 7 (32,714 points)
    iWork
    Sep 23, 2016 3:32 PM in response to Scott F

    Hi Scott,

     

    So with expenses totalling 766.64, and revenue totalling 27, your profit for September 28 is $793.74. Wow!

     

    But your question is specifically about the calculations in row 8, showing YTD profit.

     

    My assumption is that you want a running total, with each cell in Row 8 showing the total of all cells in row 7 up to and including the current column.

     

    Using the + operator will not work, because + will throw an error if presented with text values.

     

    SUM(), though, treats text as having a numerical value of zero.

     

    Here are two examples, both using SUM Both skip column A in their calculations:

    Screen Shot 2016-09-23 at 3.12.32 PM.png

    B8 uses this basic formula:

     

    B8: =SUM($B7:B7)

    Filled right, the first B7, set to Preserve column by the $ operator, remains pointed to B7, while the second B7, a relative reeference, increments by one column for each column filled right to remain pointed at the cell above the one containing that copy of the formula. Results are seen in row 8 (yellow fill).

     

    B9 uses the same basic formula, wrapped in an IF statement that acts as a switch, substituting a null string ( "" ) for the calculation result when the cell in row 7 does not contain an entry.

     

    B9: =IF(LEN(B7)<1,"",SUM($B7:B7))

     

    Since the 'empty' cell result shown in row 9 (green fill) will only appear when the content of the cell in row 7 displays less than one character, it requires those cells to display no characters until their formula has data to work with, OR it requires a different test to determine whether the YTD amount should be displayed. Other tests may be used, either in the YTD calculation or in the Profit calculation to control this, but determining the best one to use requires more detail from you.

     

    Regards,

    Barry