+ Reply to Thread
Results 1 to 4 of 4

Formula for number of perfect ordered needed to achieve certain target.

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    1

    Formula for number of perfect ordered needed to achieve certain target.

    Hi all,

    I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.

    Order with no problem gets 100 points per order
    Minor problem gets 0 point
    Moderate problem minus 100 points
    Severe problem minus 500 points

    Grade are evaluate by:
    <84.5 Fair
    <96.5 Good
    <98.5 Very good
    Above is Excellent

    Total points / total orders = final grade (or percentage)
    As of now i got
    53800 (points) / 624 (orders) = 86.22(%)
    Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.

    Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.

    Please help.

    Thank you!!!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Formula for number of perfect ordered needed to achieve certain target.

    Put this in E14 and copy down. I'm not sure what you want to go in the Order Needed section.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Formula for number of perfect ordered needed to achieve certain target.

    Post deleted - got it wrong and realised on testing.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula for number of perfect ordered needed to achieve certain target.

    B
    C
    D
    E
    F
    G
    4
    Good
    86.22%
    5
    6
    Orders
    Pts/Ord
    Total
    7
    No prob
    586
    100
    58600
    8
    Minor prob
    18
    0
    0
    9
    Moderate prob
    13
    -100
    -1300
    10
    Severe prob
    7
    -500
    -3500
    11
    624
    53800
    12
    13
    Target
    %
    # to go
    14
    Good
    84.50%
    0.0
    E14: =MAX(0, $C$11 * (C14-$D$4) / (1-C14))
    15
    Very Good
    96.50%
    1833.1
    16
    Excellent
    98.50%
    5109.3


    EDIT: Same result as k64, other than suppressing negative results.
    Last edited by shg; 06-11-2014 at 08:08 PM.
    Entia non sunt multiplicanda sine necessitate

+ 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. Rate Volume Analysis to achieve Revenue Target
    By harris92 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-08-2014, 12:18 PM
  2. [SOLVED] Percentage required to achieve target at a date
    By preparing in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2014, 04:35 AM
  3. [SOLVED] Formula to calculate number increase to achieve a percentage target
    By mick45 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-14-2014, 09:26 AM
  4. Replies: 2
    Last Post: 10-15-2012, 06:25 AM
  5. Replies: 5
    Last Post: 10-26-2008, 06:56 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