Where do I get a Spread Sheet to do the following?

[digiplay]

Your formula includes the row you entered the formula in, as well as the next row. The "ending cell" in that kind of (summation) formula must be at least one row above the row the formula is entered in. So, in your case, in row 29 "=SUM(B2:B28) will work.

That did it, thanks toMACsh!


Last question, and then off to a not fun weekend


How do I make even numbers show with the decimal point?

As I said earlier, $100.00 is showing as $100.



Thanks again,

Jerry

 

[Slydude]

I don't have Open Office installed anymore so I'll give the solution yo you the way it is done in Excel. As I recall Open Office is either identical or so close you should be able to find the right command.

1. Select the numbers that need to have the decimal point and 00
2. Right click the selected area and choose Format cells (or go to the Format menu and choose cells)
3. In the formatting sheet that appears click on the Numbers tab. Then, on the left side of the box choose Currency. In the smaller box on the right choose one of the options with the .00 included then click OK

 

[digiplay]

Hi Slydude

That works, but I will now have to go back and change all the ones that need changing (no problem, btw).

Is there a way to make the change globally, so that every time I have to input an even number, I won't have to go to the Format Window and change the selection to one that has the decimal points?

Thanks!

Jerry

 

[Slydude]

If you select a block of cells and then apply the change it will be applied to all of the cells that are selected. You won't have to change them one by one.

Try the following if it does not work you can always revert to your current file. First save a copy of your current file with a slightly different name. This new file can be used for the experiment and preserves the work already done since you are working on a copy of the file. Now do the following:
1. With one of the files open choose Edit>Select All (Command A).
2. Go to Format>Cells and apply the Currency formatting just as you did before.

This applies currency formatting to All cells. It shouldn't affect the cells that only have text in them though such as the column headings. If it does simply close the file without saving the changes.

If the method above does not work you can select the cells in each column that need the format change and apply the change that way. You don't need to do it cell by cell. I should have made that clearer in my post above.

 

[digiplay]

That did it!!!!

Thanks SO much to everyone that helped me.

Jerry

 

[Slydude]

You're welcome. Glad we could help.

 

[digiplay]

Hello again everyone

Last question.

How do I add all the totals of each column to come up with a Grand Total?


Thanks,

Jerry

 

[Slydude]

I tried the following in Excel and it works should work for you as well. For this demo I am going to assume that you have the data in columns A thru G and the sums for these columns are in row 5 of each column. Do the following:

1. Select the cell which will contain your total.
2. Enter the following formula into that cell =SUM(A5:G5)

In the formula above the letter represents the name of the column where each column total is found and the number represents the row number where the column totals are found. So , If for example, you have 12 columns they are probably A through L. The row numbers will depend upon where the total for each column is stored. So if the total for each column is stored in row 10 the formula would be =SUM(A10:L10)

If that's confusing give this video a quick look. It is for Excel but should work for Open Office and most other spreadsheets as well.

 

[digiplay]

Hi Slydude

Here is my summary of the Spreadsheet:

1) I have Column A as the Date, and the data is on Columns B through O.

2) At the bottom of each column, on Row 407, the total of each column is totaled correctly.

3) On Row 410, on Column B, I inserted the formula =SUM(B407407)


As soon as I do that, I get Err:522

Please note that I'm not putting a Smiley Face in the Formula. For some reason this site is interpreting a : as a Smiley Face.



What am I doing wrong?

Thanks,
Jerry

 

[Slydude]

The formula you used is almost correct. On row 410 in column B try entering the following formula =SUM(B407:O407). Note that in this formula that is the letter o and not the number zero. Bold added for emphasis. The boldface is not needed in the formula.

 

[digiplay]

I copied and pasted your formula =SUM(B407:O407) to row 410, column B, and I still get the Err:522 message.

 

[MacInWin]

Never mind, I read the whole thread and you've been given what I was saying already.

 

[Slydude]

I found this answer on the OpenOffice forum. Does that fix the problem.

 

[digiplay]

For some reason, when I paste the formula on Column A, Row 407........

it works, albeit the total is on Column A, not B

 

[Slydude]

That's a simpler fix than what I was thinking of. I wouldn't have thought of that as a fix.

I'm not as fluent with Excel formulas and spreadsheet in general as I used to be but if this does not solve the problem for you post back and I'll see what I can do.