Basic spreadsheet

Discuss the spreadsheet application
Post Reply
abk4ww
Posts: 2
Joined: Tue Apr 17, 2018 10:12 pm

Basic spreadsheet

Post by abk4ww »

Hello and thank you. I keep track of every receipt and enter it into a calc spreadsheet. Lately I have begun to break expenses down by how much I spend per day, but I do the addition manually and I would hope calc could do this for me. I would also like to have the spreadsheet automatically calculate the costs per day into costs per week, for example.

Example:
Date Note FoodOut Med Groc Gift Transp Misc Rent Hair Clothes Prudent Reserve Debt Pmts TOTAL


=SUM(E4:E82) etc etc etc etc for monthly totals for each category


I hope this will be clear enough. It doesn't really do me that much good to see my totals per day, while horrifying - LOL. I do think the costs per day will help get an average in order to get the daily costs down.
Open Office 4.1.1 on Windows 7
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Basic spreadsheet

Post by RusselB »

As long as the dates are entered in an approved date format (eg: 17-mar) then you can do the addition using the SUMIF or SUMIFS function.
Additionally, for averages, you can use the AVERAGEIF or AVERAGEIFS function.
The SUMIF and AVERAGEIF functions are detailed in the help file. The SUMIFS and AVERAGEIFS functions are not in the help file, but do show a bubble prompt when entering them in order to help get the parameters in the correct order.
Remember that Calc sees dates as numbers, not necessarily how you see the display. For example, you might see 17-Mar-18, but Calc sees 42810
If you want to do comparisons for dates more easily than trying to do the conversion from what you see to what Calc sees, I suggest you take advantage of the DATE function for the comparisons.

If you need more help, I'm going to recommend that you upload a small example of your spreadsheet. The data doesn't matter so long as we can see data that is a possibility.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Basic spreadsheet

Post by FJCC »

It looks like you have your data set up in an inconvenient way. Try arranging it with a column for the date, a column for the week (you can use the WEEKNUM() function to have Calc compute the week from the date), a column for the category (such as food, med, etc.) and a column for the expense amount. You should be able to rearrange your existing data into the new format without too much pain using Copy and Paste. Be sure to make a back up of your file before doing any rearrangement! You can then use a Pivot Table to do your calculations. Highlight the data, including the column headers, then select the menu Data -> Pivot Table -> Create. Accept the Current Selection as the data source. Drag the Fields for Date (or Week) and Category to the area for Row Fields and drag the Expense field into the area for Data Fields. Click More and set the cell in the Results To box to a cell that is next to your columns of data, not at the bottom of the data. Click OK and you will get a table showing the sum of expenses, by category and by date or week.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
abk4ww
Posts: 2
Joined: Tue Apr 17, 2018 10:12 pm

Re: Basic spreadsheet

Post by abk4ww »

WOW! So cool to know I have options! Cramming for end of semester, but will try all of these when I get done. I thought it was probably an IF thing - LOL.
abk4ww
Open Office 4.1.1 on Windows 7
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Basic spreadsheet

Post by robleyd »

More info on the Pivot Table (aka Data Pilot) at https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Post Reply