I am trying to complete a matrix that compares Budget to Actual by Month.
I want the comparison matrix to be populated with the line item budgeted and actual spending amounts to come from 2 separate matrices, one for budgeted amounts and one for actual amounts.
My current approach is a formula I have entered for each month for Budget and Actual amounts – so I have 24 formulas to pick up the all the month’s spending amounts. I thought I could use the lookup formula but it requires all line items in the lookup array to be in ascending order so that won’t work. My line items in all 3 matrices are not in ascending order and are grouped. I have 13 different groups; each group has a number of different line items for different expenses. All 3 matrices are setup with identical line items for the rows for all spending descriptions.
The approach I would use for this in Excel would be to use vlookup. Any thoughts would be appreciated. Thanks.
Hi – not quite following you on the need to link or not to link categories, but regardless you can accomplish a non-linked scenario via the following Select formula.
Budget = select(Budget::Month,@Budget::Year:Month,@Year:Month)
You are selecting the values, and the keys are the year/month combinations.
Either way will work (linked vs. non linked) just a matter of preference on your part.
Without seeing your model this is a bit of a guess but I think this is a good candidate for a SELECT or USING AS function. Take a look at this area of the on-line help to see if this is useful.
Feel free to post your model if you need further assistance.
Thanks for this tip. If I do that I thought I would end up with three columns in each of the two source matrices (Budget and Actual) and I only want the one column for each month there. The reason for his is that I want to import data from an Excel file by month to populate the monthly columns for the Actual matrix months. The goal was once that was complete, the Comparison matrix would pick up the monthly amounts and do the calculations. I suppose I could link the Budget matrix Year and Month but with Comparison matrix, but Budget and Actual matrices are Year and Month are now linked together.
If I link the Year and Months of Comparison to the others, will it duplicate the columns in these two source matrices?
Thanks for this tip. I looked at both functions but don’t think they work here. As I understand it, SELECT is similar to vlookup in Excel. Is this the case?
USING / AS I also think is more than needed. I simply have a matrix with data in one column for each month that I want a second matrix to pick up for the corresponding location in the same place. It would simply be a formula in Excel that would say A1 Sheet2 = A1 Sheet 1. This way the cells in Sheet 2 are just picking up the corresponding values in Sheet 1.
Right now I can accomplish this result with the following formula 4. Comparison::Budget:Jan = Budget::Jan and repeat this for each month so I have 12 formulas for the Budget matrix. This is repeated for the matrix called Actual which is Matrix 2 to get actual values in another column.
Matrix 1 in this case is called Budget (and it consists of 12 columns for the months of the year; Matrix 3 is called Comparison and it consists of 36 columns for the year as it has a Budget column, an Actual column and Difference column for each month. The purpose of Comparison is to show budgeted amounts beside actual amounts with the difference for each month.
The rows are all the same and are liked. The months are linked in the two 12 column only matrices. I have attached it for you so you can see what I mean. When I used the two formulas:
16. Budget = Budget::Month
17. Actual = Actual::Month
the model entered the values from each month for Jan in all of the columns for each month so everything consisted of Jan’s values for the year. I kept them in the model but blocked them out.
Let me know please if this helps. Thanks very much. Tom