+ Reply to Thread
Results 1 to 4 of 4

Rating Based on Daily Qty Purchased

  1. #1
    Registered User
    Join Date
    03-03-2014
    Location
    Atlanta
    MS-Off Ver
    Excel 2003
    Posts
    15

    Rating Based on Daily Qty Purchased

    Would like to copy a formula down the 'Rating' column in the bottom table that
    identifies the row by date by cutomer that conatins the highest 'Rating' (qty purchased)
    for each day. Any help/guidance with this is appreciated.



    Qty Purchased Rating
    100-1000 A
    50-99 B
    1-49 C


    Date Customer Qty Purchased Rating
    3/12 Xx 20 C
    3/12 Xx 10
    3/15 Xx 250 A
    3/15 Xx 5
    3/12 Yy 20 B
    3/12 Yy 10
    3/15 Yy 5
    3/15 Yy 15
    3/15 Yy 80 B
    3/12 Zz 10 C
    3/15 Zz 150
    3/15 Zz 320 A
    Attached Files Attached Files
    Last edited by clockspring; 03-05-2014 at 02:59 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Rating Based on Daily Qty Purchased

    Hi,

    Try this array formula in cell D14 and copy the formula down to D25:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Here are 2 things to note, however:
    1. Your D18 value was B, but my formula returns C. If I understand what you are trying to do, C is actually the correct answer. If I am mistaken, please explain to me why B is the correct answer.
    2. This is an array formula. To enter an array formula, you need to use Ctrl + Shift + Enter to enter the formula, not just Enter. If entered correctly, the formula will be surrounded by braces {} in the formula bar. Also, an array formula that is not entered as such will often result in an error or an incorrect value somewhere, so this may also make it obvious that the formula has not been entered correctly.

    I have attached the workbook with the formulae in the yellow cells, so that you may see that it works at least in this small sample.

    I hope this helps

  3. #3
    Registered User
    Join Date
    03-03-2014
    Location
    Atlanta
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Rating Based on Daily Qty Purchased

    ajryan88 - appreciate the quick response. The formula works great on the example I provided. When I tried to apply it to my file it didn't work. I've attached the file (Book2). Not sure if it's a formatting issue or not. If you have a moment to review it to identify my error I'd appreciate it. Thanks for your time.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Rating Based on Daily Qty Purchased

    Hi,

    Your formulas weren't entered as array formulas. That's all it was. Please read my previous post to understand the difference

    If this resolves your problem, please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks

+ 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: 4
    Last Post: 03-18-2013, 04:19 PM
  2. How to Determine Commission Payout Based on Rating Scale
    By cheeseburglar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-31-2013, 12:26 AM
  3. [SOLVED] Performance Rating based on Percentages
    By tomlancaster in forum Excel General
    Replies: 3
    Last Post: 09-27-2012, 01:44 AM
  4. Overall rating based on an alphabetical score
    By OlSchool in forum Excel General
    Replies: 5
    Last Post: 06-23-2011, 10:10 AM
  5. Replies: 2
    Last Post: 01-09-2010, 04:06 AM

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