I’m currently working on a generic pricing template, where the End user is able to specify the unit cost of an item, the number of units that qualify for a bulk purchase discount and the discount rate at each of these bulk purchase intervals.

__Example__

This is an example of the table the End User would fill in. Items B1 and B2 have different costs, different intervals for the bulk purchase discount and different discounts themselves. Obviously in reality I would have more than 2 or 3 items

Item | Cost | Interval 1 | Interval 2 | Interval 3 | Interval 4 | Interval 5 | % of original cost 1 | % of original cost 1 | % of original cost 1 | % of original cost 1 | % of original cost 1 |

B1 | £1000 | 0-100 | 101-200 | 201-300 | 301-400 | >401 | 100% | 98% | 96% | 94% | 92% |

B2 | £900 | 0-150 | 151-250 | 251-350 | 351-450 | >451 | 100 | 99% | 98% | 97% | 96% |

B3 | Etc. | Etc. | Etc. | Etc. | Etc. | Etc. | Etc. | Etc. | Etc. | Etc. | Etc. |

<tbody>

</tbody>

The End User would then specify how many purchases are made in a given month.

Item | Month 1 no. of units | Month 2 no. of units | Month 3 no. of units | Month 4 no. of units | Month 5 no. of units |

B1 | 95 | 134 | 207 | 389 | 621 |

B2 | 37 | 156 | 273 | 373 | 521 |

B3 | Etc. | Etc. | Etc. | Etc. | Etc. |

<tbody>

</tbody>

I would then have the worksheet calculate the appropriate cost for the total units purchased each month, based on the discount that they qualified for.

In the linked spreadsheet, I have 3 worksheets:

· BoM (Bill of Materials)

· Volumetric Arrays

· Profiling

https://drive.google.com/file/d/0B__bxAaMPpZZMng0V0RpUUpOc2s/view?usp=sharing

__BoM__

In the BoM, the End User specifies the cost of the item (this is currently made up of 4 components), with the total item cost given in Column F. Then the upper threshold, or upper limit of the pricing interval is specified in Columns H:Q. I decided to have 10 different intervals, but this is arbitrary. Similarly, the discount rates are set in Columns S:AB. Here the discount is input as a percentage of the original price.

__Volumetric Arrays__

In the Volumetric Arrays sheet, Cells A4:P4 represent the first entry in the BoM. They give sheet and cell references for the BoM ID, Upper Threshold 1, Upper Threshold 10, % Price 1 and % Price 10, located on the BoM sheet.

Column R uses a indirect and concatenate to calculate the BoM ID from Columns B:D.

Column S calculates the Lower Threshold values.

Column T uses a transpose array, indirect and concatenate to import the correct Upper Threshold values from the BoM.

Column U does the same as above to import the discount rate.

Columns W:AK use a series of direct text entries and formulas to create part of a Index Match formula that will be used on the Profiling sheet.

Column AM and AO concatenate the two separate text strings that will go to make up the index match formula in the following way: “=INDEX(array,MATCH(“ and “,lookup array,1),3)”. Note that the lookup value has been not been assigned in either of these text strings.

By selecting Rows 4-15 and dragging down, this Volumetric Arrays sheet can be populated further to accommodate any additional items added to the BoM. This is why there are 11 rows of blank space (shaded grey), between each entry on the Volumetric Array sheet.

Named ranges:

Column R = VA_BoMID

Column AM = VA_IndexID

ColumnAO = VA_MatchID

__Profiling__

Column A, the End User can select an Item from the BoM in the drop down lists in Rows 3-12.

Columns B:Y, the End User can enter the number of units purchased per month.

Column Z, formula to give the total number of units purchased.

Columns AB:AY, formula yielding the total cost of units per month based on the number purchased and discount rate. Uses Index Match for find the BoM ID unit cost, multiplied by the % Price from columns CA:CX, multiplied by the number of items purchased from B:Y

Columns BB:BY, formula concatenating several index matches. Looks at the row’s BoM ID in Column A and matches to the correct formula strings on the Volumetric Arrays using VA_BoMID, VA_IndexID and VA_MatchID, to put together a text string of the formula to determine the correct discount rate. The lookup value is determined using the CELL function to the appropriate column in the number of units purchased table.

Colums CA:CX, uses a VBA module Eval(), which evaluates a text string as a formula. This points to the corresponding cells in the columns BB;BY, to yield the % Price value for a given month.

Code for VBA Eval() module:

Code:

```
Option Explicit
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
```

As you can probably tell, this is rather clunky workaround to do what I need. Am I missing an in-built function in Excel that could do what I want, or can anyone think of a simpler method to achieve the same result?

Many thanks for your help!

Chris