Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find High, Low, and Average Price

    Hi, I have an Excel file from a company that arranges shipping for my company. In it there are rows that contain the following:

    - Origin City
    - Origin State
    - Destination City
    - Destination State
    - Pounds of Shipment
    - Cost of Shipment
    - Date of Shipment

    I'd like to be able to determine:
    - Average Price for Origin/Destination
    - High Price for Origin/Destination
    - Low Price for Origin/Destination
    - If Current Price is >=1.25 x Average Price place the word "review" in a cell within that row

    The goal is to identify "high" outliers that we need to investigate. I can manually do this; however, I am wanting to automate for a go-forward basis. I am attaching a sample file.

    Thanks for looking at.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is this what you (partially) mean? Not totally clear.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In H2 (adjust ranges as needed), the array formula (confirm with ctrl-shift-enter):
    =AVERAGE(IF(($A$2:$A$18=$A2)*($C$2:$C$18=$C2),$F$2 :$F$18))

    In I2 (adjust ranges as needed), the array formula (confirm with ctrl-shift-enter):
    =MAX(IF(($A$2:$A$18=$A2)*($C$2:$C$18=$C2),$F$2:$F$ 18))

    In J2 (adjust ranges as needed), the array formula (confirm with ctrl-shift-enter):
    =MINI(IF(($A$2:$A$18=$A2)*($C$2:$C$18=$C2),$F$2:$F $18))

    In K2:
    =IF(F2>=1.25*H2,"review","")

    Copy H2:K2 from h3:K whatever.

    Steve
    Modified formulas, forgot to include destination
    Last edited by sdckapr; 2013-10-10 at 07:29.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve, this is sweet music and perfect. GREAT JOB and THANKS!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If need be, you can also include the states to compare (for example)
    =AVERAGE(IF(($A$2:$A$18=$A2)*($B$2:$B$18=$B2)*($C$ 2:$C$18=$C2)*($D$2:$D$18=$D2),$F$2:$F$18))

    Change Average to Min/Max as needed
    Steve

Posting Permissions

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