Need help creating a lengthy formula (or a macro)

amgis

New Member
Joined
Oct 20, 2016
Messages
3
I have some data where I need to calculate the "best deal". I have the basic math worked out, I just need to find a way to implement it into an excel sheet or macro. Here is how my data is structured:
minimum buy quantity, rate 1, rate 2, rate 3, company, order quantity ...
item 1 1000, 5, 4, 3, JOHNS COMPANY, 750 ...
item 2 2000, 3, 2, 1, JOHNS COMPANY, 2500 ...
...

Those are the 6 important attributes my data has. It is organized on one sheet in a quasi-tabular form like above, for each company we have pricing on (so it repeats across the sheet lengthwise). The goal is to determine whether to buy item 1 from JOHNS COMPANY or one of the other ones, after some comparisons.

Sometimes buying 1,000 even if you only need 750 is better. I could do a min function for each row, but then I have the lowest price without any seller information. If I could get the cell address of the lowest price, I could find who has the lowest price. At this point though transforming the data into pivot tables or creating a vba macro starts to seem more appealing.

Ideally I won't have to change the format of the data, even if it isn't optimal. The formula I would need to make has to compare 10+ companies so it would probably turn out like a =IF(AND(OR(... lots of stuff ))) nightmare. Let me know if I left anything out or if you know of anything off of the top of your head to get me pointed in the right direction. Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

It's not clear to me, how you decide what is the best deal.
Lowest total cost ?
Lowest unit cost ?
Something else ?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,529
Messages
5,659,343
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top