# Thread: Find High, Low, and Average Price

1. ## 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.

2. Is this what you (partially) mean? Not totally clear.

3. 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

4. Steve, this is sweet music and perfect. GREAT JOB and THANKS!

5. 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
•