+ Reply to Thread
Results 1 to 8 of 8

Average Unit Rate Using Tiering Schedule

  1. #1
    Registered User
    Join Date
    03-03-2015
    Location
    New York
    MS-Off Ver
    2010
    Posts
    17

    Average Unit Rate Using Tiering Schedule

    Hello All,

    First off, I want to thank you in advance for your assistance. I am trying to create a formulaic (non-macro) way to tabulate an Average Unit Rate (AUR). I have attempted to build several layers of formulas to calculate this, but have been unsuccessful so far. In the attached document, my inputs are highlighted in green and what I'm trying to solve for is highlighted in blue. For this example, I am trying to calculate the AUR for Zone #2 in 2Q15. If you look off to the right, I have performed a manual calculation of what the AUR should be ($607). Now when you look at my formulaic method (cell R20, highlighted in yellow), you'll see that it's returning $871. However, when you look at the corresponding possible unit rates in the table (F12-F16) you'll see there is nothing higher than $725, making $871 mathematically impossible.

    Again, I thank you in advance for your assistance in helping me to formulaic-ally automate this process.
    Attached Files Attached Files
    Last edited by lipper79; 03-03-2015 at 04:33 PM.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Average Unit Rate Using Tiering Schedule

    First thing I noticed is that the cell with $871 in it is actually correct according to the formula in the cell, R19/R13 = $87,050/100 = $870.5 - Maybe I am misunderstanding something.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Average Unit Rate Using Tiering Schedule

    Your manual calculation doesn't help me because it doesn't look like the cells in red. I don't understand it and don't need it. I think it would be more helpful to explain simply in your cell R15 is 51 what you would expect the formula to return? If 51 is not right, what value did you expect and what cell is that value in and why did you expect that value? Same thing for R16 and R18, these are all formulas that are pulling values from somewhere else, what did you expect them to be and why?
    Last edited by skywriter; 03-03-2015 at 03:14 PM.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Registered User
    Join Date
    03-03-2015
    Location
    New York
    MS-Off Ver
    2010
    Posts
    17

    Re: Average Unit Rate Using Tiering Schedule

    Cell R20 is supposed to equal cell Z11. The calculation resulting in Z11 is what the calculation in R20 is supposed to return. If you look at the math behind cells Y8:A11, I am basically doing a weighted average.
    Last edited by lipper79; 03-03-2015 at 04:34 PM.

  5. #5
    Registered User
    Join Date
    03-03-2015
    Location
    New York
    MS-Off Ver
    2010
    Posts
    17

    Re: Average Unit Rate Using Tiering Schedule

    I'm not disputing the math in cell R20, I'm disputing the theory behind the calculation. You can't have an average unit rate that is greater than any of your unit rates.
    Last edited by lipper79; 03-03-2015 at 04:34 PM.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Average Unit Rate Using Tiering Schedule

    Perhaps you could read post#3 and address the questions I posed to you in that post. Regarding Cell R21, it's empty and it's grey.

    Quote Originally Posted by lipper79 View Post
    Cell R21 is supposed to equal cell Z11. The calculation resulting in Z11 is what the calculation in R20 is supposed to return. If you look at the math behind cells Y8:A11, I am basically doing a weighted average.

  7. #7
    Registered User
    Join Date
    03-03-2015
    Location
    New York
    MS-Off Ver
    2010
    Posts
    17

    Re: Average Unit Rate Using Tiering Schedule

    It was cell R20 (highlighted in yellow). I have updated all of my postings to reflect that. My apologies.

  8. #8
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Average Unit Rate Using Tiering Schedule

    Is there some reason you keep avoiding addressing my request in the last post to answer the questions in post #3?

    Quote Originally Posted by lipper79 View Post
    It was cell R20 (highlighted in yellow). I have updated all of my postings to reflect that. My apologies.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 05-07-2013, 05:19 PM
  2. Different royalty rate depending in unit sales
    By DavidK in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-20-2007, 03:05 PM
  3. Tiering Macro, Should be easy?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2005, 07:05 PM
  4. creating a rate schedule
    By Melissa in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1