Results 1 to 6 of 6
  1. #1
    Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Lounger
    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.

  4. #4
    Uranium Lounger
    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

  5. #5
    Lounger
    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.

  6. #6
    Uranium Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •