Skip navigation

How to associate a fixed $ value to a date cell?

209 Views 3 Replies Latest reply: Sep 1, 2013 8:55 PM by Barry RSS
chick.pea Calculating status...
Currently Being Moderated
Sep 1, 2013 8:20 PM

I'm trying to keep track of payments made, according to date.

 

I have one column where I enter dates.

 

Can I associate these date cells to a fixed $ value, so that each time I enter a new date the SUM cell will automatically update?

 

I hope so...

 

Thanks!

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Sep 1, 2013 8:42 PM (in response to chick.pea)

    Maybe this is what you are thinking?:

     

    Screen Shot 2013-09-01 at 10.39.12 PM.png

    C3=COUNTA(A)*C2

     

    this is shorthand for in cell C3 type the text "=COUNTA(A)*C2" without the double quotes

     

    this counts the number of cells in column A with something (doesn't even have to be a date), then multiplies that count times a fixed payment amount in cell C2

     

    In my example I did not format the the amount and total cells as currency.  You can do this by selecting those cells then use the cell inspector to format as currency:

    Screen Shot 2013-09-01 at 10.42.37 PM.png

  • Jerrold Green1 Level 7 Level 7 (28,215 points)
    Currently Being Moderated
    Sep 1, 2013 8:45 PM (in response to chick.pea)

    CP,

     

    "Can I associate these date cells to a fixed $ value...?"

     

    If I'm reading this question correctly, you would count the number of dates entered and multiply the count by a fixed dollar figure to calculate the total. Is this what you had in mind?

     

    If so, a sample total calculation might be:

     

    =COUNT(date-entry-range)*500.00 and format this total cell as Currency.

     

    In this example you would have a result of $500 times the number of entries in the date-entry-range of cells.

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Sep 1, 2013 8:55 PM (in response to chick.pea)

    HI cp,

     

    By "associate these cells to a fixed $ value," I assume you mean that fixed value will be added to the sum as each date in sentered.

     

    Here are two examples, one showing the fixed amount in the adjacent column when the date (or anything) is entered in column A, the other showing only the total.

     

    Both use a second table to hold the fixed value, making it easy to edit, if necessary.

    Screen Shot 2013-09-01 at 8.42.47 PM.png

    Formulas:

    (Method 1)

     

    B2, and filled down to B10: =IF(LEN(A)>0,Fixed value :: A$2,"")

     

    LEN(A) returns the length (in character) of the entry in the cell in this row of column A. If anything is entered, the IF condition is TRUE, and IF returns the value in A2 of the smaller table. If nothing has been entered, the condition is FALSE, and IF returns a null string to the cell.

     

    B11: =SUM(B)

     

    Row 11 is a Footer row, and is not included in the cells to be summed.

     

    (Method 2)

     

    D11: =Fixed value :: A2*COUNTA($A)

     

    COUNTA counts the number of entries in column A of the Main table, then multiplies the fixed value in the small table by the result of the count and returns the result.

     

    This formula may be placed in any cell.

     

    (Both methods)

     

    The cell reference to Fixed Value::A2 (or Fixed Value::A$2) may be replaced with the actual value, and the second table eliminated, if desired.

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.