As the title states, I'm working on a workbook to find the average price paid for goods and services over a multi year period.
I've currently got it set up as follows: Page 1 is the compilation page. I have 3 columns (Item, Unit size, and Avg. Price) where I've alphabetized the items. This list will be changed as needed which means resorting to alphabetize each time an item is added or deleted.
Page 2 is the template I'll use for the years being looked at, most likely the past 3-5. I have the first two columns on this page set to read from the first two columns on the first page (Item and Unit). Columns 3 and beyond are the sellers columns where I list their names at the top and prices below, matching the prices with the item list on the left.
Here is where I'm running into 2 major problems. First, when I add an item into the list on the first page and re-alphabetize, the first two columns on the second page work as designed but the rows of the columns that follow (sellers columns) don't shuffle to stay matched to the updated list.
Second, I want to have the average price on the first page be created by having the software look in each row on the following pages and find the highest price, lowest price, and the median price for each item. My goal is to have a formula that uses (highest+lowest+(median x4)) divided by 6.
Help with either of these issues would be much appreciated. If you think there's an easier way to do this, let me know!
Bookmarks