Items, of 3 different groups, and their values are entered in a table progressively, over a year period . I am trying to work out a way of getting the month total value of each of the 3 groups seperately, for each month of the year. A sample worksheet is attached
Thanks in advance for any help.
No attachment.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks DonkeyOte. The attachment is now there
I don't think you really need me to tell you that your layout is not overly conducive to simple analysis...
First... as things stand you're looking at using Array formulae, Arrays can not be used in Merged Cells, ie G8:G12 can't be merged and the same holds true for H & I and each block.
Second... to keep things simple I would be inclined to sort the master table (K:P) by Item Letter in Ascending order (A,B,C,D etc..)
Then, assuming further that you have a block for each month (no breaks in time) you could perhaps use something like:
the above can in turn be copied across to H8:I8 and G8:I8 can be copied to G13:I13,G17:I17 etc...
Please Login or Register to view this content.
As already inferred a more coherent - standard table like layout will lend itself it to simpler and more efficient analysis.
DonkeyOte,
-- I certainly agree that it is rather complicated as it is, but on this layout other formulae have been based,and changing it would cause trouble.
-- Sorting the master table K;P by item letter is not possible, as these letters will actually be changed to the real names or descriptions of the items to be entered, eg. phone bill, health care, entertainment, ...etc.
and the list is not final or fixed, as more new items will be entered later on as the time goes by till the end of the year.
**Any way, I unmerged the merged cells, copied & pasted the formula as you explained, but it did not work.
However, If you believe that the present set up is not workable, I will have to find a way of doing some changes or substituting the present requirement by some thing else. I appreciate your remarks and take them very seriously.
If for no other purpose than to illustrate otherwise... see attached**Any way, I unmerged the merged cells, copied & pasted the formula as you explained, but it did not work.
(note I am not implying this either a] resolves your underlying problem b] is impervious to error (given present use of LOOKUP it is not))
Remember also than delimiters vary between locales, given you specify neither your locale nor your XL version I'm afraid at times you will not be getting perfectly tailored solutions to your environment.
As to your question regards optimal set-up.
If you're storing data think database, either as physical warehouse or if you prefer to use XL as warehouse you should think in terms of database structure
For ex.
-- were you to use a DB consider how you would store that same data in a single table.
XL is really no different in that it will perform better if the data is stored in a logical and coherent manner.
-- think of columns as "fields" where each field should serve a unique purpose and no field should be replicated within any given table
-- to avoid need for "cross tab" queries (ie linking one data table in XL to another to ascertain additional info for table 1 records), store all nec. info in table 1 by means of additional fields
(ie in this instance I would suggest storing the Frequency value of each item against each transaction by means of INDEX/MATCH etc... ie generate individual records by formulae if nec. but having the records present in the final table greatly simplifies analysis en masse, aggregation etc...)
Last edited by DonkeyOte; 11-24-2009 at 10:20 AM. Reason: altered narrative
In terms of how I'd say you "should" do it in an ideal world... specifically see sheets Sheet2 & Sheet3.
Column D on Sheet2 is tied to your table on Sheet3 (the ordering of which would not matter)
The Pivot Table on Sheet3 is set to use the Dynamic Named Range _PTData (itself based upon data on Sheet2)
Now this formula seems to be working OK.
But I have to point out that I want to apply it for 2 different tables, the sample worksheet is one of them,and the other one is slightly different in that the value heading covers 2 columns. I hope that wont be a problem, but not quite sure, until I try tomorrow, as it is now 2 AM (after midnight) in Sydney-Australia, and I want to get some sleep. So, I might need more help from you.
Thank you very much.
Have a good day.
DonkeyOte, thanks for your effort to help, but the solution offered is not what is actually required. It is tailored to the items listed in the sample worksheet as A, B, C, ... etc., which, in practice, will have to be replaced by actual names (or descriptions). And it does not allow for new items to be added whenever required in the long run.
What I am actually doing is trying to create a Budget Planner to be used by any body. the prospective user is not supposed (or allowed) to do any thing other than entering item names & values, and it is assumed that his knowledge is limited to just that. He can't do any adjustments to suit his particular situation needs.
-------------------------------------------------------------------
Now, I am thinking of changing the object to a different one. Instead of creating a column for the month total of each of the 3 groups, can we discard the "Frequent" & "Isolated" groups and keep only the column of the "regular" items.
Each Regular item has a known (fixed) value and falls due on a known date. So, as shown in the sample worksheet, all regular items are entered in advance from the start to the end of the year (in the block on the left) without the values (which are shown in the master block on the right).
Is there a way in which the number of occurances of each regular item (within each month) is counted, multiplied by its known value (from the block on the right), then getting the products added together (for all regular items within the same month) ?
I hope I am not confusing you, as I feel I am confused myself !
The importance of this is showing in advance the amount of money due , say in the form of regular bills, each month.
I realy wonder if this objective is easier to achieve than the earlier one !!
Waiting to see your opinion.
Regards
Michael
Michael, I've offered my opinion(s) / suggested routes previously - moreover I fail to see why the "suggested" approach is not viable. Therefore at this juncture I'm afraid I have nothing further to add to this thread.Originally Posted by Michael6
I hope another member will take this on for you.
Thank you again DonkeyOte. I really appreciate your advice, and don,t think at all that any other member would do any better. So, I have to give up and consider this thread closed. Modifications to the structure or framewok of what I am working on has to be made.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks