How to get the best from my spreadsheet - complete excel novice!

[nickreed86]

Hi Guys,

I am a complete excel novice and I am hoping to create a spreadsheet for my business. We are about to expand production and I need to compare spice prices from my suppliers - see screenshot so far.

Screenshot (1).png

As you can see there are 6 suppliers and around 43 different spices and I have created a basic spreadsheet from my limited excel knowledge where it will work out the price per gram from each supplier.

My question is, how can I improve this to make it do more. For example, can I get the conditional formatting to show me the cheapest price on each sub group to save me having to manually compare these? Or are there better ways of doing this by creating tables etc?

Thanks in advance for any help you guys can give me!

Nick

[oeldere]

you get better help if you add an small excel file, without confidential information.

Please also add the desired / expected result in your file.

[nickreed86]

Thanks for the reply. I had completed the rest of the information but it doesn't let me upload the file - do I need to save it as a different file? Its currently a .ODS file.

[oeldere]

you need to upload an excel file., since you are on an excel forum.

[nickreed86]

Spice List (posted).xlsx

Sorry i thought since it was excel it would save as an excel file - as i said complete novice but thanks very much for the sarcasm!!

[newdoverman]

I have used two hidden columns. The first one is column B which is column A expanded to fill each row with the appropriate spice. The other is column H that uses column B to calculate the lowest price for each spice.

[nickreed86]

I have used two hidden columns. The first one is column B which is column A expanded to fill each row with the appropriate spice. The other is column H that uses column B to calculate the lowest price for each spice.

Thanks that is brilliant!! What do you call this type of formula so I can research how to replicate this in future spreadsheets?

[newdoverman]

Glad to help. Sometimes it is easier to solve a problem if you use helper columns that you can hide.

The only formula used is in column H and it is:

Please Login or Register  to view this content.

I call this a MIN IF formula. It takes the minimum value found under the conditions specified by the IF. In this case the IF specifies the cell range of B2:B259 must be equal to the value in B2 (the other hidden column that has the values of the merged cells filled in all the rows). The MIN part of the formula then takes the smallest value that the IF has found for B2. The formula is filled down the column and B2 value changes with the spice involved and the same logic is applied to the new value.