Creating function for "Choosing and Analyse Best Supplier"

[peergynt]

Hello All.

I have a question. This is what I want to do;

I have 3 or more suppliers and every supplier gives me price list for items. I compile them into one excel file. I could choose only one supplier and I need to buy all items from that.

For example, I have item list on the first column, Supplier A's prices in the second column, Supplier B's prices on third one etc.

I would like to add extra columns to find out which supplier is best for that item and help me to analyze between suppliers.

I would like to add one column for; check three prices and find minimum one and write it that cell.

For another extra column; indicate this min. price belong to Supp.A or Supp.B or Supp.C

And the hard part that I cannot figure it out; another column to make analyze.

For example, I will write selected supplier on one cell like A, B or C

An extra column for analyzing need to be; look selected supplier, compare its price with other two suppliers item by item.

if min. price belongs to the selected supplier it will write to cell "Cheapest"

if min. price of this item not belong to selected supplier; it will compare with other two suppliers price, if difference less than 10% between selected one and other two supplier price it will write "Not Cheapest but Safe", If the price difference more than 10% between selected one and either one of the other two suppliers it will write "Not Cheapest and Need Check"

What do you think? Is this doable?

ab.JPG

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[peergynt]

Thank you FDibbins for quick reply and your guidance. Sorry for to post thread in wrong way. I am creating new one as you said.

[FDibbins]

I actually meant for you to post the sample WB here, in this thread, not start a new thread

[peergynt]

Oh yes, I am replying and tried to attached the dummy file. I hope it is working

[JohnTopley]

Try

E5

=MIN($B5:$D5)

F5

=INDEX($B$4:$D$4,MATCH($E5,$B5:$D5,0))

G5

=IF(F5=$C$1,"Cheapest",IF(ABS(INDEX(B5:D5,,MATCH(C1,B4:D4,0))-E5)/E5*100>10,"Not cheapest-Need to check","Not cheapest-safe"))

[sktneer]

Please refer to the attached.

[JohnTopley]

.. Update (ABS not needed)

G5

=IF(F6=$C$1,"Cheapest",IF(ABS(INDEX(B6:D6,,MATCH(C2,B5:D5,0))-E6)/E6*100>10,"Not cheapest-Need to check","Not cheapest-safe"))

[peergynt]

Thank you so much guys. I am really appreciated.

I couldn't find to remark this post as solved, but this problem has solved.

[sktneer]

You're welcome! Glad we could help.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.