Results 1 to 6 of 6
Thread: Array formula help (2002)

20031118, 20:12 #1
 Join Date
 Aug 2002
 Location
 MN, USA
 Posts
 49
 Thanks
 0
 Thanked 0 Times in 0 Posts
Array formula help (2002)
Hi,
I'm working on a spreadsheet to help figure out expenses between myself and my roommates (see attached). I used an array formula for the "Paid" report and it worked beautifully. However, I'm having some trouble with the "Share" report formulas. I need to reference my Table worksheet via a VLOOKUP and I believe that this function and array formulas don't go together very well.
I've added some verfication data in the log to help see what each person really owes for each month. I don't want this data in the final project.
Anybody have any ideas? Let me know if something needs clarification.

20031118, 20:38 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Array formula help (2002)
Not exactly sure what you are after, I will have to look closer.
But if you are trying to do summaries of the data, instead of array formulas, a pivot table would be much better. Arrays are best for just a calc not a table of calculations, since they are resource intensive.
Steve

20031118, 20:51 #3
 Join Date
 Aug 2002
 Location
 MN, USA
 Posts
 49
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Array formula help (2002)
Well each person owes a share of the total money paid and each person paid a portion of the total money paid. I'd like that information sorted by month for each party involved.
If you look at the validation data that is on the "Log" spreadsheet, you can see that for November Brad and Meghan owe 131.67 and they paid 230.00. So their 'share' is 131.67. I would like an array formula that figures out this share. It is confusing because for each purchase different people are paying and different people owe money for part of the item(s).
I can easily just use my validation data to create the answer I would like, but I would like to skip this step if possible.
Can a pivottable take into account that each person is paying a different ratio for each purchase? An example might be helpful here.

20031118, 21:09 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Array formula help (2002)
A little hard to follow, but are you saying that right now your formulas in cells H4:J4 don't yield correct results, that they should return:
<table border=1><td></td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>4</td><td align=right>$131.67</td><td align=right>$65.83</td><td align=right>$57.50</td></table>
If so, in the Share table there is no way to know what the allocation of each item according to "Purchased For" ("Bought For" in the Table Sheet) ; you will need an intermediate table like the one you have created for an example.John ... I float in liquid gardens
UTC 7ąDS

20031118, 21:33 #5
 Join Date
 Aug 2002
 Location
 MN, USA
 Posts
 49
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Array formula help (2002)
yup, Sorry about that confusion. That is what the formula should yield for November.
I was looking at the results it gave me and wasn't able to figure out what my current (incorrect) forumula was doing. That might provide some insight to the problem.

20031118, 21:34 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Array formula help (2002)
Steve may have a better solution, but I would leave in the table that you used for Verification purposes on Sheet "LOG", cells H3:J& (and down), then I would format it and call it "Per Item Share". Then in Sheet "Report" Cell H4, I'd use the formula:
=SUM((YEAR(LOG!$B$4:$B$1000)=YEAR($G4))*(MONTH(LOG !$B$4:$B$1000)=MONTH($G4))*LOG!H$4:H$1000)
and copy it right and down.John ... I float in liquid gardens
UTC 7ąDS