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!
Bookmarks