Office for Mac ..... Excel question

[williamkhall]

I am using EXCEL to keep and manage our household expenditure; each time we spend money, the DATE, category CODE, AMOUNT and which family PERSON are stored on an EXCEL row of the EXPENDITURE sheet.

I am wanting EXCEL to do the following ....

1. sort the data on DATE, then CODE and finally on PERSON .... I have been able to do this using the SORT command.
2. for each CODE, I want the subtotal calculating ..... EXCEL has to find which rows are applicable, then sum the AMOUNTs ....
2a. I can use MATCH function to calculate the first occurrence of each CODE;
2b. then use COUNTIF to determine how many rows each CODE are on;
2c. then use AGGREGATE to sum the AMOUNTs for each CODE.
3. BUT the final function, AGGREGATE needs the start and ending reference numbers AND I can not get EXCEL to automatically do 2a and 2b to automatically do 2c.

I have attached part of the EXCEL file as a .txt file .... hopefully it can be converted back into EXCEL!!

Any suggestions will be gratefully accepted

williamkhall

 

[balamw]

Which version of Excel are you using?

B

 

[williamkhall]

Apologies .... Microsoft Excel for Mac 2011, v.14.1.0

 

[balamw]

Try this:

Data -> Subtotals



B

 

[williamkhall]

great thanks ..... however, I will be continuously updating this Expenditure sheet ... say every day ... then re-sorting the data and then recalculating the subtotals weekly.

Am I able to have the subtotals not in the data, but say, under the last row of the data? I do not want to have to delete the subtotals rows each week, before re-sorting.

??

 

[DeltaMac]

There's quite a few fixes in Excel if you update. Just open Excel, and run Check for Updates from the Help menu. The update (do it until no more updates appear) will take you to v. 14.3.9

 

[exegete77]

One of the problems with your approach is that you have to “touch” manipulate the data. Never a good idea.

Rather than do all that, why not use Pivot Tables. They can be rerun anytime, and you never have to touch the data, putting in a different worksheet if you want.

 

[balamw]

Yeah, a simple Pivot Table would work fine.

Row Label -> Codes
Values -> Sum of Amount.

B

 

[williamkhall]

hi folks ..... thanks for your suggestions .... although I have, in the past, programmed in VB6 in Excel, I have always shied clear of pivot tables .... only because I really didn't understand why or how they worked . Could you suggest a good learning, easy-to-understand, internet teaching site for pivot tables, please.

could you also please explain what you meant by "touch" manipulated the data .... did you mean "human sorting" or something? It would be nice to have all the work automated, but ......

I will try what has been earlier suggested .... for the short term, but this exercise might be a good example to learn about pivot tables.

Again, many thanks ...

 

[balamw]

http://pivottabletips.com is one such link found via the Wikipedia page on Pivot Tables.

Overall though, I think this is one of those cases where Excel may not really be the right tool. You're trying to use it more like a database, and Excel can do those kinds of things, but poorly.

B

 

[williamkhall]

I think that you are right ..... the exercise is only for household expenditure, so as long as it works EASILY, I'm happy!

thanks

 

[balamw]

You'll be fine with a pivot table as long as you don't have more than two aspects you want to summarize at once.

B

 

[onekerato]

Highly recommend pivot tables in Excel. Watch a few video tutorials (at Lynda.com or YouTube) to get a jump start on this technology. Your data is already arranged correctly for using Pivot Tables.

You can drag multiple fields into a pivottable "row" to summarize by those fields. You can also reorder. For example, first sum by person, then by code.

You can even keep the data outside Excel in a CSV file, and have the Pivot Table read directly from the CSV. This makes it easy to separate out the analysis from the data. Excel provides "refresh" and "change source" commands on pivot tables for this purpose.

Attached excel file (inside the .zip) has two sheets: "data" contains the data you provided, and "pivots" has two pivot tables linked to the same data. You can paste in all the data and change the source for the pivot tables if you want. The dates have been grouped into months by configuring the pivot table.