PDA

View Full Version : Where do I get a Spread Sheet to do the following?



digiplay
06-16-2017, 09:40 PM
Hi everyone :)


I have a mid-2012 MacBook Pro, and I need a Spreadsheet to do the following:


1) At the top, I need about 10 columns that I can assign a different name to each column(Date, Amex, Advanta, Discover, Chase, MasterCard, etc).

2) I want to put the date at the beginning of every row, and then go to the appropriate column, and put a number in (5/16/17 Discover $245.00, Advanta $288.32, MasterCard $566.32, etc.

3) I want each column to keep a running total somewhere at the bottom.


When I'm through putting in all my numbers, ideally I would want no space between the last entry in the column and the running total for that column.


Lastly, I don't want to spend too much money, as this is truly a one time thing.


Any suggestions will be gladly appreciated :)


Thanks,
Jerry

Raz0rEdge
06-16-2017, 10:12 PM
If you have a Gmail account, use Google Sheets. You can also use Numbers (https://itunes.apple.com/us/app/numbers/id409203825?mt=12) on your Mac which is the spreadsheet app from Apple.

digiplay
06-16-2017, 10:26 PM
Hi RazOrEdge :)


So there is a Template already made that will have multiple columns that I can put the name of each Credit Card at the top of each column, and every input on a row (except the Date column) will have a running total of the row at the bottom?

If so, will Google Sheets have that Template already made?

Thanks!
Jerry

pigoo3
06-16-2017, 10:41 PM
Lastly, I don't want to spend too much money, as this is truly a one time thing.

If you can't find anything already made that fits your needs...make your own spreadsheet.:) From what you describe needing...it's a fairly elementary spreadsheet to setup.:)

- Nick

digiplay
06-16-2017, 11:12 PM
Hi Mr. Nick :)

But I have NEVER made ANY Spreadsheet, so how will it be a fairly elementary to setup a spreadsheet up if I have no idea how to do so?


That's why I asked if there is one that is setup already so that all I have to do is write a name at the top of each column, and put date on the first column, and then put a number in the correct CC column.

That being said, what the heck do I do, I have to have this done before Monday (a 15 year tally of Credit Card charges to about 6 different Credit Cards)?


As an aside, I did it the old fashioned way, and I have all the info on paper already (except the total for each Credit Card), but the person who wants these numbers wants all the info in a spreadsheet format, and he wants me to email him a spreadsheet.


But as I initially said, I have NEVER made ANY Spreadsheet :)

Thanks,
Jerry

Raz0rEdge
06-16-2017, 11:18 PM
What you want is very simple to do..open up Numbers and you'll see the rows and columns. Type the names you want on top.

Enter the information you want on each row and then at the bottom of each column, use the formula "=SUM(B2:B30)" assuming you have 29 rows of data. If you have more or less, change the B30 to be less or more. Once you've gotten the first column done, move the selection cursor over that total column and hit CMD+c to copy and then move to the next column (C) and hit CMD+v to paste it and the formula will automatically update to C and repeat this for every column and it will automatically update.

digiplay
06-16-2017, 11:27 PM
Now THAT I can do :)

Thank you (and everyone else as well)!!!!!!

Jerry

pigoo3
06-16-2017, 11:32 PM
But I have NEVER made ANY Spreadsheet, so how will it be a fairly elementary to setup a spreadsheet up if I have no idea how to do so?

The reason why I said this sort of thing is elementary...is that it's probably one of the first lesson's you are taught in a class working with spreadsheets. If you & I were sitting at the same computer...I could probably show you what to do in only 5-10 minutes. Most of the work & time is the data entry for the 15 year tally of the credit card charges.

Of course now you're in a time crunch (Monday) to get this done. Would have been nice if you had the chance to open a spreadsheet app and mess around with it in the last 15 years (before having a time crunch). Knowing how to use word processing & spreadsheet apps are two very common computing skills almost everyone should be able to do. I've been using spreadsheet & word processing apps since the mid-1980's...and I am not a computing professional.

Almost everyone from the minimum wage worker to presidents of large corporations should know how to use spreadsheet & word processig apps. After you get this project done...I would highly recommend "playing around with" a spreadsheet app.:)

In any case...you should be able to find a pre-made spreadsheet that fits your needs somewhere on the internet. By the way...if you've never made any spreadsheets before...and therefore may not be familiar with using a spreadsheet app. What spreadsheet app are you going to be using for this project...and how are you going to go about entering the data if you've never worked with a spreadsheet before?

- Nick

digiplay
06-16-2017, 11:33 PM
Stop the Presses!

My MBP is used for recording Music (LPX), I have 10.8.5, and EVERYTHING works fine as it is, thank you very much :)

That being said, I tried to download it and got the following Message:

Numbers can’t be installed on “Mac” because OS X version 10.12 or later is required.


Crap, what do I do now?


Thanks,
Jerry

pigoo3
06-16-2017, 11:35 PM
Now THAT I can do :)

That's what I meant by "elementary"!:) I think that the person that wants this data...simply want's it to be in a digital format (and entered into a spreadsheet)...so that they don't have to use a lot of time entering all the 15 years of data themselves.

Of course revisit this thread if you run into any issue's.:)

Good luck Jerry,

- Nick

pigoo3
06-16-2017, 11:38 PM
Numbers can’t be installed on “Mac” because OS X version 10.12 or later is required.

Crap, what do I do now?

Try downloading & using the free app called "Open Office". It contains a spreadsheet app...and the download works with OS 10.7 and higher::)

https://www.openoffice.org/download/index.html

- Nick

digiplay
06-16-2017, 11:39 PM
What spreadsheet app are you going to be using for this project...and how are you going to go about entering the data if you've never worked with a spreadsheet before?

- Nick


That's why I asked my initial question, I have no idea which spreadsheet to use.

As far as entering the data, that why I said what I wanted to do (put a different CC at the top of each columns/put a date on the first row of each column, and then simply type a number on the corresponding column row).

digiplay
06-16-2017, 11:40 PM
Try downloading & using the free app called "Open Office". It contains a spreadsheet app...and the download works with OS 10.7 and higher::)

https://www.openoffice.org/download/index.html

- Nick

And use the same running total formula?

pigoo3
06-16-2017, 11:46 PM
That's why I asked my initial question, I have no idea which spreadsheet to use.

That wasn't the question I asked.;) I asked "What spreadsheet application" you were going to use? This is a totally different question from what sort of spreadsheet you were going to use. You need to have a spreadsheet application first...before working with a spreadsheet.:)

Some common spreadsheet apps are:

- Apple's "Number's"
- Microsoft's "Excel"
- "Open Office" that I mentioned above.

- Nick

pigoo3
06-16-2017, 11:47 PM
And use the same running total formula?

Almost all spreadsheet applications work the same way. What you need to do is very simple & easy...so yes...Open Office, Numbers, or Excel will work the same way.:)

- Nick

digiplay
06-17-2017, 12:05 AM
Nick, I'm 99% there :)

I downloaded Open Office, named the first column (A) Date, the second column (B) Advanta, and the third column (C) Discover.

On the first row, I put a date, and as a test, put $44,22 on the Advanta column.

On the second row, I put a different date, and put $100.00 on the Advanta column.

I then went to row 29 of column B (the Advanta column), and attempted to paste "=SUM(B2:B30)" to the Advanta column, but I get Err522.

What am I doing wrong?


Added today at 03:34 AM:

I pasted the formula at the bottom of the date column (column A), and it puts the correct total of column B on the bottom of column A.

I copy and paste the formula to the next column, it gives the correct total, but one column to the left, not the correct column.

Also, if I input $100.00, it reads back as $100. How do I make it keep the extra 0's?

Lastly, I'm sure I will email the finished spreadsheet as a pdf, but the person I'm sending it to only has a PC.

Will he be able to open it (it says the file is an ods)?

digiplay
06-17-2017, 04:08 AM
Last questions tonight :)

1) Will a PC be able to open an ods file created n my Mac?
2) My brother has a HP Computer. Would it be better to do a spreadsheet on it.
3) He uses the HP just for surfing the net, so he won't have Excel, Word, etc. on it. Is there a free spreadsheet for PCs, or possibly a free Excel software that I can use for my aforementioned one task of documenting Credit Card charges?

Thanks in advance,

Jerry

Slydude
06-17-2017, 05:05 AM
ods files are an open document format that Open Office uses to save spreadsheets in.

1. A PC should be able to open the files
2. Probably not necessary for the kind of simple spreadsheet you are describing. It should work fine without being created on a PC.
3. There is a version of Open Office for the PC. The spreadsheet module is called Calc. You can find the download at http://www.openoffice.us.com

If the person you will be sending the file to does not need to edit the information, sending a PDF will be fine. If they have Excel or a similar spreadsheet, they should be able to edit the file.

toMACsh
06-17-2017, 12:15 PM
Nick, I'm 99% there :)


I then went to row 29 of column B (the Advanta column), and attempted to paste "=SUM(B2:B30)" to the Advanta column, but I get Err522.

What am I doing wrong?

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.

DianeVan
06-17-2017, 12:39 PM
It's kind of basic, but you COULD use tables in Word to do the same thing, if you are familiar with Word. Or even if you have it written out by hand you could scan it and use Adobe Acrobat to copy and paste it into a Word table or a spreadsheet, then just correct the scanning errors rather than typing in the whole thing. 26623

If it was my project, I would use Adobe Acrobat and scan the written copy then Excel, but since you are unfamiliar with spreadsheets if you know Word, you could try that too.

EDIT: sorry a little late since you have already started with OpenOffice.

digiplay
06-17-2017, 02:13 PM
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
06-17-2017, 06:10 PM
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
06-17-2017, 06:33 PM
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
06-17-2017, 07:20 PM
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
06-18-2017, 02:01 PM
That did it!!!!

Thanks SO much to everyone that helped me.

Jerry

Slydude
06-18-2017, 02:21 PM
You're welcome. Glad we could help.

digiplay
06-23-2017, 07:04 PM
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
06-23-2017, 07:49 PM
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 (https://www.youtube.com/watch?v=UgeEeEESJxE&ab_channel=eHowTech) a quick look. It is for Excel but should work for Open Office and most other spreadsheets as well.

digiplay
06-23-2017, 09:10 PM
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(B407:P407)


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
06-23-2017, 09:23 PM
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
06-23-2017, 09:52 PM
I copied and pasted your formula =SUM(B407:O407) to row 410, column B, and I still get the Err:522 message.

MacInWin
06-23-2017, 10:06 PM
Never mind, I read the whole thread and you've been given what I was saying already.

Slydude
06-23-2017, 10:18 PM
I found this answer (https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=8672#p40665)on the OpenOffice forum. Does that fix the problem.

digiplay
06-23-2017, 10:55 PM
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
06-23-2017, 11:48 PM
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.