Excel sanity check

[Bobchinski]

Hi everyone,

Let me start by saying thanks for having a look. I need to know:

a) If what I am trying to do is possible using excel functions?
b) If my train of thought for how to go about trying to achieve what I want to achieve is right?
c) If it would be easier to do in VB?

I am currently running Office 2010 on Win7.

I have two arrays of data, firstly, items, the priority of those items and cost and secondly, a running balance of savings and spending.

What I am trying to achieve is have the list of money spent populate based on the current money saved value and the item's listed priority so that if I reorganise the priority/add new items, the budget listing will automatically update based on the changes. i.e. it will never populate with item of priority=2 before the priority=1 item is already budgeted for and if the priority=2 item gets bumped down the list by a new item then the budget plan will update accordingly.

The hardest part of what I am trying to achieve is having so that if the total cost of two or more items is less than the current saved amount, then the budget appropriately account for this.

I am currently trying to use a series of functions that I recently used in a drop down list that limited the use of each item to one, as such the columns needed to make that work are hidden (identified by {}).


[Priority] {Used Priority} {Lowest Priority in List} [Item Name] [Item Type] [Item Cost]
<item name=""><item type=""><item cost="">
[Month] [Money Currently Saved] [Items to buy in month] [Total spend] [End of month total (i.e. Money saved - money spent)]
<month><money currently="" saved=""><items to="" purchase="" that="" month=""><total cost="" of="" items=""><net money="" saved="" (i.e.="" current="" -="" spend)="">
Then I think I need to use an array function to identify the highest priority items and IF they are less than the current saved amount.

What I am really not sure how to do is to take that one step further and make this recursive so that it has a rolling subtotal until the total spend on items within a month is as close to the current saved amount as possible.

Thanks again for looking and any suggestions anyone has.</net></total></items></money></month></item></item></item></priority>

 

[bruderbell]

I have two arrays of data, firstly, items, the priority of those items and cost and secondly, a running balance of savings and spending.

What I am trying to achieve is have the list of money spent populate based on the current money saved value and the item's listed priority so that if I reorganise the priority/add new items, the budget listing will automatically update based on the changes. i.e. it will never populate with item of priority=2 before the priority=1 item is already budgeted for and if the priority=2 item gets bumped down the list by a new item then the budget plan will update accordingly.

The hardest part of what I am trying to achieve is having so that if the total cost of two or more items is less than the current saved amount, then the budget appropriately account for this.

Can you post a snapshot of what your data looks like, and what you want the result to look like, and what the result shoudl look like after you add a new priority?

 

[Bobchinski]

Does this help?



Eventually I want to hide row K and have it display the item names rather than their priorities (as this is still a work in progress I am not too worried by presentation at this point.

 

[bruderbell]

It looks like column K is the contribution amount (the monthly delta), and M is the running total. So you would purchase multiple items based on column M, right?

 

[Bobchinski]

J[row] = M[row-1]+40

M[row] = J[row]-L[row]

L[row] = SUM(K[row]) where K[row] is all the items that can be afforded in that month, chosen in terms of priority (Column A)

So the delta£ = M[row]+40-L[row]

So the total available money in a month is actually column J, and I want to limit column K to be less than J in a given month.

 

[bruderbell]

Here's a formula that will reorder a list based on priority values:
=IFERROR(INDEX(Table1[Item],MATCH(SMALL(Priority,ROW(1:1)),Priority,0)),"")

Where Table 1 is as you show above.

Then, you can use MATCH and INDEX to return the prices next to the prioritized list:
=IFERROR(INDEX(Table1[Cost],MATCH(J3,Table1[Item],0)),"")

where column J is where my prioritized list is (J3 is the first member of the prioritized list).

Finally, you can put in a running total in column L with something like this:
=IFERROR(IF(0>L2+IF(ISNUMBER(I3),I3,0)-INDEX(Table1[Cost],MATCH(J3,Table1[Item],0)),"Can't Afford",L2+IF(ISNUMBER(I3),I3,0)-INDEX(Table1[Cost],MATCH(J3,Table1[Item],0))),"")

Where column I contains deposits. The idea is that you manually enter the deposits in I, and L will tell you what items you can afford based on the current deposits. If you wanted to, you could Have a date column next the deposits column to show when those deposits need to be made.

I realize this is a bit backwards from what you asked. You wanted the sheet to tell you which items during a given date, and this tells you what dates you must deposit to buy the items. It's as close as I could get it