?
Solved

MIN function or similar in EXCEL 2007

Posted on 2011-10-12
7
Medium Priority
?
204 Views
Last Modified: 2012-05-12
Hi all.

I have a column with 5columns: ItemNumber, Competitor1, Competitor2, Competitor3, Competitor4

The competitor columns have the price for each item number. I want to add 2 more columns: LowestPrice and Competitor.

I want to be able to find the lowest price and who the competitor is.

I used the MIN function but that just gives me the lowest price but how do I find out who the competitor with the lowest price is.

Any ideas? Thanks.
0
Comment
Question by:printmedia
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36957235
Are you doing this for each item? If you only have two items, couldn't you use an IF formula, e.g. IF A1<B1 "Competitor 1" etc?
0
 

Author Comment

by:printmedia
ID: 36957250
The file has about 90 items. So there will be items where we have pricing for all 4 competitors, other items will only have 1 or 2 competitor pricing. I want to be able to see the lowest price and the competitor column header. Each competitor is it's column all I would need is the column header.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36957251
Out of just 2?

In D2 try

=IF(A2>B2,B$1,IF(B2>A2,A$1,"Tie"))

regards, barry
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Accepted Solution

by:
kgerb earned 1000 total points
ID: 36957256
This formula will work I think

=INDEX($G$3:$J$3,MATCH(MIN(G4:J4),G4:J4,0))

Open in new window


See attached workbook

Kyle
Q-27393330-RevA.xlsx
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36957270
@Barry
Writing formulas on your phone...Nice :-)
0
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 1000 total points
ID: 36957271
Maybe if your items are in col A and competitors in cols B-E, this?

=INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0))
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36957843
>Writing formulas on your phone.

took me ten minutes to type that, too! I'm not really one of the "text generation"......

Why aren't the > < and = signs more accessible, isn't that what everybody wants to use?

barry
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question