Formulaic Weighted Average Unit Rate
Hello All,
First off, I want to thank you in advance for your assistance. In the attached file, I am trying to create a formulaic (non-macro) way to tabulate a Weighted Average Unit Rate (AUR). The numbers I will be inputting are shaded in green (and will correspond to the inventory ranges in column C) and the Weighted Average Unit Rate (shaded in blue) [determinant from columns D:G], is what I am trying to solve for. In the calculation box, is how I expect the end result to work (this is for demonstration purposes only). I need help creating a formula that does it automatically, using the data from the table. To validate your formula(s) is calculating correctly, the result in cell L10 should be approx $633.
Please don't hesitate to contact me with any questions or if I have been ambiguous in my request and need to explain further. I look forward to working with you.
| Attachment | Size |
|---|---|
| Formulaic Weighted Average Unit Rate.xlsx | 463.15 KB |
Hi Lipper70
Hey there, Jon here. I just saw your post here and I really would like to resolve this issue for you. After all you have been very professional and prompt in settlements on that site, which I greatly appreciate. I tried to reply there but the question got closed.
I will try to explain in some detail.
The main problem with what you are trying to do is that you are giving a single value for the number of units (e.g. 100) and you expect the formula to return the average unit rate, not for that whole number of units, but for a series of smaller units that add up to 100. While this is entirely doable with formulas, but the formula needs to know which specific units that add up to 100 should be considered, as there is an infinite possible of combinations that add up to 100 (e.g. 50+50, 25+40+35, 10+20+30+40, etc.). EACH OF THESE COMBINATIONS WILL RETURN A DIFFERENT AVERAGE UNIT RATE.
If we can agree on the above concept we should be able to move on with setting up the formula accordingly
Jon