I have data which I desperately need to analyze and I'm at a loss as to how to proceed. The data is in three columns; the first is date. The dates are not in order (for various reasons) and new data is just added to the bottom of the list. I also have the selling price of a product in the second column and the type of product in the third. For example:
Calculations must be based on the seven most recent prices of any one type. The 3rd lowest price and 2nd highest price go into the table starting with Cells “A” (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">F12</code>) and “B” (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">G12</code>), respectively.
The third formula should eliminate (ignore) the highest and lowest of the seven most recent prices, and calculate the average of the remaining five prices. This goes in the last column of the table, starting with Cell “C” (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">H12</code>).
Then I need a combined bar/line graph with "type" on the horizontal axis and "price" on the vertical. I need a stacked bar graph showing the lower range (Cell “A”) and higher range (Cell “B”), and a line graph is to be overlaid with the averages (Cell “C”).
I'm not an Excel wizard, and I've tried every self-help tutorial I can to work this out. I’ve gotten as far as thinking that <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">VLOOKUP</code> may be useful, but I'm now at a loss.
Calculations must be based on the seven most recent prices of any one type. The 3rd lowest price and 2nd highest price go into the table starting with Cells “A” (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">F12</code>) and “B” (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">G12</code>), respectively.
The third formula should eliminate (ignore) the highest and lowest of the seven most recent prices, and calculate the average of the remaining five prices. This goes in the last column of the table, starting with Cell “C” (<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">H12</code>).
Then I need a combined bar/line graph with "type" on the horizontal axis and "price" on the vertical. I need a stacked bar graph showing the lower range (Cell “A”) and higher range (Cell “B”), and a line graph is to be overlaid with the averages (Cell “C”).
I'm not an Excel wizard, and I've tried every self-help tutorial I can to work this out. I’ve gotten as far as thinking that <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant-caps: inherit; line-height: inherit; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">VLOOKUP</code> may be useful, but I'm now at a loss.
