# Need help creating a lengthy formula (or a macro)

#### amgis

##### New Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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 ?

Replies
11
Views
460
Replies
17
Views
323
Replies
27
Views
1K
Replies
1
Views
427
Replies
12
Views
884

1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

### 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.

### Which adblocker are you using?

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

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