12 Replies Latest reply: Jul 24, 2008 2:07 PM by John Gorman
John Gorman Level 1 Level 1 (25 points)
I have my template the way I want it. In the different columns I want to add a $ amount each month of the year and at the end of the year I would like the Total to be cumulative for the year. How do I learn to add the formula for that?

iMac 500 G3, Mac OS X (10.4.11), iBook 500 G3 OS 10.4.10
  • 1. Re: How to add a column of $ numbers
    Barry Level 7 Level 7 (29,180 points)
    Hi John,

    It would be nice if you described the situation in more detail. From what you've said, here's what I see as your situation:

    You have the months of the year listed in a column. Leaving a couple of rows at the top, let's say January is in cell A3, February in A4, and so on to December in A14.
    In the next column, you want to enter a dollar amount for that month.

    In cell B15 you want to calculate the total dollar amount for the year.

    If that's correct, the easiest way to build the formula for B15 is to Click on B3, hold the mouse button down and drag to B15, then release the mouse button.

    With these 13 cells now selected, go to the button bar and click on the sigma ( ∑ ) button.

    AppleWorks will enter the following formula into cell B15, and will display the result.

    =SUM(B3..B14)

    The result will be automatically updated when you enter each month's dollar amount.

    Note: Do not include the dollar sign when you enter the amount.
    If you want AppleWorks to display the amount with the dollar sign, select the cell(s) where you want the sign to show, press shift-command-N (or go Format > Number) to open the Number format dialogue, then choose Currency from the pull down menu, leave Precision set to 2, and click OK.
    To display the dollar amount as a number with two digits after the decimal, but no currency sign, use the steps above, but choose Fixed rather than Currency.

    Regards,
    Barry
  • 2. Re: How to add a column of $ numbers
    John Gorman Level 1 Level 1 (25 points)
    Thanks for the answer, Barry. Now I think I can describe my template better.

    In column A1, I have the MONTH, in columns B1 thru F1 I have names of stores. In rows A2 thru A13 I have the months and A14 is TOTAL. I have entered the formula, ran a test amount in each month, but ne Total. Should I move all the months down one row?

    John
  • 3. Re: How to add a column of $ numbers
    Level 8 Level 8 (41,760 points)
    Is it what you want?



    in G2 the formula is:
    =SUM(B2..F2)

    use fill down from G2 thru G14.

    in B14 the formula is:
    =SUM(B2..F2)

    use fill right from B14 thru F14.

    Yvan KOENIG (from FRANCE mercredi 23 juillet 2008 20:39:27)
  • 4. Re: How to add a column of $ numbers
    John Gorman Level 1 Level 1 (25 points)
    Your picture of a SS is exactly what I want and where my template is at the moment. The rest of your post confuses me. I almost know know what you want me to do but, since this is my first try at doing a SS, I don't know how you want me to do the formulas. I assume that this is something that you have done often but I need point A to point Z instructions on how to do what you did with the formulas. If there is written instuctions in the Help viewer I will look for them. Please don't give up on me yet!

    Thanks

    John
  • 5. Re: How to add a column of $ numbers
    Barry Level 7 Level 7 (29,180 points)
    Hi John,

    Using Yvan's diagram, and expanding on his instructions:
    (Also correcting one formula)

    +in G2 the formula is:+
    =SUM(B2..F2)

    +use fill down from G2 thru G14.+

    Enter the formula as shown in cell G2.
    (Click on cell G2, then type the formula as shown. When doen, press enter, tab, or return to confirm the entry.)
    Then select cells G2..G14.
    (Click and hold on cell G2. Drag the mouse down to G14. Release the mouse button in cell G14. Cells G2..G14 should now be highlighted.
    Go to the Calculate menu and choose Fill Down (or press command-D )

    +in B14 the formula is:+
    =SUM(B2..B13)

    +use fill right from B14 thru F14.+

    Enter the formula as shown in cell B14. Then select cells B14..F14.
    Press command-R (or go to the Calculate menu and choose Fill Right.


    Regards,
    Barry
  • 6. Re: How to add a column of $ numbers
    Barry Level 7 Level 7 (29,180 points)
    In B14, the formula should read:

    =SUM(B2..B13)

    The asterisks in the version above were intended as fromatting keys to make this change from Yvan's posted version bold. I didn't notice they'd been read by the forum as literal characters until it was too late to edit. Sorry.

    Barry
  • 7. Re: How to add a column of $ numbers
    John Gorman Level 1 Level 1 (25 points)
    Thank you Yvan and Barry.

    I have put the formulas in the cells specified. And there they sit. Are they supposed to disapper evetually or do I need to do something else. I tried adding numbers to a column, to see what would happen, but there were no calculations. What did I miss?

    John
  • 8. Re: How to add a column of $ numbers
    Barry Level 7 Level 7 (29,180 points)
    Hi John,

    What, exactly, do you have entered in B14 and G2?

    Copy the following two lines of this post, then Paste them into your reply.

    B14:: ||

    G2:: ||

    In your spreadsheet, Click on B14, press command-C to Copy, then click between the two vertical lines in the B14 line (pasted in your reply), then Paste.

    In your spreadsheet, Click on G2, press command-C to Copy, then click between the two vertical lines in the G2 line (pasted in your reply), then Paste.

    What I need to see is exactly what's in those cells, and the only way to see that is to have you copy it by copying the cell. If you type it here, it's quite likely that you'll inadvertently leave out something that's actually there (or add something that is not there).

    As an alternate, you could email me a copy of the spreadsheet as an attachment. My address is available by clicking my name in blue at the upper left of this post.

    Regards,
    Barry
  • 9. Re: How to add a column of $ numbers
    Level 8 Level 8 (41,760 points)
    My guess is that you entered one (or several) space(s) character before the = character in your formulas.

    Yvan KOENIG (from FRANCE jeudi 24 juillet 2008 09:07:00)
  • 10. Re: How to add a column of $ numbers
    John Gorman Level 1 Level 1 (25 points)
    Thanks Barry, I'll try this first.

    B14::|=SUM(B2..B13) |

    G2::|=SUM(B2..F2) |

    After that I did the fill routines.

    John

    John
  • 11. Re: How to add a column of $ numbers
    Barry Level 7 Level 7 (29,180 points)
    Hi John,

    The pasted version of each formula shows an extra space following the formula, but that should be ignored by AppleWorks and have no effect on whether the formula works. When pasted and filled into a new sheet here, both formulas produced the expected results.

    When you did the fill routines, what was displayed in cell F14? The two most likely answers, if the cell is showing a formula, are

    =SUM(B2..B14) or =SUM(F2..F14)

    The first would support Yvan's guess--there's a space or other invisible character before the = sign causing AW to see the formula as text, not a formula. Click on the cell (B14), then without moving the mouse, retype the formula and press return. What you type will replace everything in the cell. Repeat for G2, then do the fills as before.
     
     
    The second indicates the formula was correctly entered and recognized as a formula. When filled right, AppleWorks automatically adjusted the cell references to the appropriate column.
    My guess in that case would be that the spreadsheet is set to show Formulas. Go to the Options menu, choose Display... and uncheck the Formulas checkbox.

    Regards,
    Barry
  • 12. Re: How to add a column of $ numbers
    John Gorman Level 1 Level 1 (25 points)
    Thanks Barry and Yvan. I redid the last collum on the right and it now works. You guys have been very patient and thurough in your help. This was my first attempt at a spreadsheet. I will now gather all my data and enter it and smile because it works like I want it too.

    Thanks again

    John