Getting started with Excel

[annsolomon]

I'm teaching myself Excel and hope some kind soul will help me get started.

I picked what I think should be a very simple starter project. I created a sheet that lists grocery prices at several neighborhood stores. I want to compare the prices and list the lowest price in a separate column. No problem. Here's where I go brain dead: Rather than list the lowest price, I really want to show the store name (which, of course, is a column heading).

Ann

 

[xlbo]

How is your data organised? How does the store name relate to the price you are looking for?

If you have a standard table layout:

[tt]
Store Name Item Price
Store1 Item1 £10
Store1 Item2 £3
Store2 Item1 £8
etc etc...[/tt]

then you can use the MIN price along with INDEX and MATCH to return the store name....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Except if there are more than one store with the same minimum price.

I'd also use the MIN function but then Data > Filter > AutoFilter to display the Store Names that match that criteria.

Or you could use MS Query to return the list to another sheet location. faq68-5829

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[dianemarie]

She states that the store name is a column header so my guess is her data is not set up optimally. Ann, if that is the case, your first step should be rearranging your data as Geoff laid out in his post. Then you will be able to work through the suggested solutions. Refer to Excel Help first to get an understanding of the functions. Just type the name of the function (ex: MIN) in the search field. Excel Help is an excellent resource. If you find manuals easier to work with, find a beginner Excel book and just work through it chapter by chapter. You'll get there!

 

[Gavona]

1. With the existing layout it would be possible to use conditonal formatting to highlight the values that are equal to the minimum for that row (or indeed that are within a certain tolerance of the minimum).

Consider perhaps identifying all where the price is within 1% of the cheapest? =Min(range)*101%
Or within 5 cents of the cheapest =min(range)+.05

2. Once you have your data in the form Geoff suggested you can create a pivot table report from it that is similar to the layout that I suspect you currently have (rows for items, columns for stores).


3.Comments on your project specification:
Consider if you have an item that is 1 cent cheaper in shopA than shopB is the cost difference material to you?

Maybe you could develop the model to include the volume of each item, cost of buying the basket of items from each individual store, cost of buying at the cheapest price available......

Now I am getting carried away with ideas to develop your model...
Identify the cheapest store for the entire basket
Identify the items from that store that are more than 1% more expensive than the cheapest available.
Identify the cheapest store for that subset of items.
Looking just at those two stores which items should you buy from each? How much will my basket cost?
Is this sufficiently close to the cheapest possible? (maybe a criteria "to be worthwhile visiting another store I should save at least $5 by doing so.
................................

I think that you could achieve the above with formulae and lots of columns. However the real point is that IF the above were your ultimate goal then it might be that the ideal data layout and approach would change.

Regards


Gavin