+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Need formula to reference multiple cells, and average.

  1. #1
    Registered User
    Join Date
    10-08-2009
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2010
    Posts
    20

    Need formula to reference multiple cells, and average.

    Hi there, i'm looking for a little help on writing a formula for a work spreadsheet. I work in transportation deal with a a lot of financial information thats categorized by week number. I'll attach a mock spreadsheet as an example of what I'm working with.

    I'm not even sure what formula to begin with, whether it's =if, some form of =vlookup, i have no idea. So any help would be very appreciated!

    What i need to do is have it so cell B4 in the Reference Tab (Week 1 for 2011), looks at the Weight tab, and finds both 2011 and Week 1 in the same row for everything that applies (in this case, i need it to look at cells A172:F175), add everything that applies from the F column, and divides it by everything that applies in the E column.

    So I need a formula in B4 in the Reference tab that basically says:
    "Find everything that applies to the week on my left, for the year above in the Weight Tab, and average weight for that month and week"
    I'm not sure how to word that better, but if i can explain things better or answer any questions, i'll happily do what i can.

    The second formula i need (also in the Reference tab) would go into i4, i'd imagine this is damn near the same formula, if any differences at all. Similar to the one above, i need it to look at what week and year it applies to, then looks at the "Costs" tab (in this case, A172:F175). Sums "Expense", and divides it by the sum of "Total Profit" for that specific Week and Year.

    I'm looking to find a way to do this as automated as possible as this is part of a very thorough daily running report, but i'm looking to avoid VBA and Macros as much as possible since it's a large corporation where using either of these have been disabled on 90% of computers other than about 4 people in my office. Any help is more than appreciated, thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need formula to reference multiple cells, and average.

    Rizzu,

    Weights formula (in cell B4):
    Please Login or Register  to view this content.
    Cost formula (in cell I4):
    Please Login or Register  to view this content.


    Then I just copied over and down. Attached is a modified version of your workbook so you can see how it works

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-08-2009
    Location
    Calgary Canada
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: Need formula to reference multiple cells, and average.

    Fantastic! Thank you so much, this is exactly what i needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1