Formulaic Weighted Average Unit Rate

[lipper79]

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.

 

[NARAYANK991]

Hi ,

The way your data is laid out , using helper cells will make the formula easier to develop. Is it OK with you ?

Narayan

 

[lipper79]

Hi Narayan,

I'm not entirely familiar with "helper cells," but I trust your expertise and if you think that is what is necessary, then yes, that is OK.

 

[NARAYANK991]

Hi ,

The layout that has been adopted for the data is extremely formula-unfriendly.

You need to take another look at how you can improve this.

The uploaded file contains the output based on a rearranged layout. See if all of the results are correct.

Narayan

 

[lipper79]

Hi ,

The layout that has been adopted for the data is extremely formula-unfriendly.

You need to take another look at how you can improve this.

The uploaded file contains the output based on a rearranged layout. See if all of the results are correct.

Narayan

Yes, you are right. I like your new layout and will adopt it in my model. Thank you very much for all your assistance!!!!