I have price list i.e. discounts based on length in meters, and it looks like this:
It's defined in pricelist and it't the same for each service.
However, discounts are applied in a way that first 4m discount is 15%. For the length after 4m, but before 8m discount is 55% etc.
That means, e.g. if I have length of 11,5m, there will be discount on price per meter of 15% for 4m (from 0 to 4m), discount of 55% for 4m (from 4m to 8m) and discount of 71,53% for 3,5m (from 8m to 11,5m).
For that purpose I need to split length into bands i.e. to determine length in each band in order to apply appropriate discount.
That is not a problem if I have just a single value as I can use a helper table and various standard formulas (IF, MAX etc9
However, this is part of large specification where there are a number of such items, so I need to develop formula which will result in an array of values, each representing length in each band.
E.g. if I have lenght of 57,731 I need a formula that will result in an array:
i.e. resulting array should be: 4;4;8;16;25,731. That's how length of 57,731m is divided into bands according to the first table, and I already created array formulas that can use that array and multiply it with array of discounts and apply them and generate final, single value (price).
Simplified, my specification looks like this:
There are hundreds of rows, so I cannot use helper table for each row, but I need an array formula that I will use for calculating compound discount and final result (Total price) in each row.
Any idea?
Couldn't find anything similar on internet.
| Length more than meters | Discount (%) |
0 | 15% |
4 | 55% |
8 | 71,53% |
16 | 55% |
32 | 0% |
It's defined in pricelist and it't the same for each service.
However, discounts are applied in a way that first 4m discount is 15%. For the length after 4m, but before 8m discount is 55% etc.
That means, e.g. if I have length of 11,5m, there will be discount on price per meter of 15% for 4m (from 0 to 4m), discount of 55% for 4m (from 4m to 8m) and discount of 71,53% for 3,5m (from 8m to 11,5m).
For that purpose I need to split length into bands i.e. to determine length in each band in order to apply appropriate discount.
That is not a problem if I have just a single value as I can use a helper table and various standard formulas (IF, MAX etc9
However, this is part of large specification where there are a number of such items, so I need to develop formula which will result in an array of values, each representing length in each band.
E.g. if I have lenght of 57,731 I need a formula that will result in an array:
4 |
4 |
8 |
16 |
25,731 |
i.e. resulting array should be: 4;4;8;16;25,731. That's how length of 57,731m is divided into bands according to the first table, and I already created array formulas that can use that array and multiply it with array of discounts and apply them and generate final, single value (price).
Simplified, my specification looks like this:
| length (m) | Service | price per meter (€) | discount (%) | Total price |
| 11,5 | Service 1 | 150 | 0,461178261 | |
| 57,731 | Service 2 | 30 | 0,300053697320331 | |
| 123,123 | Service 1 | 66,7 | xxx | |
| 456,456 | Service 3 | 28,5 | xxx | |
| 12,13 | Service 4 | 13,4 | xxx | |
| 15,123 | Service 1 | 121,55 | xxx | |
| 22,333 | Service 3 | 35,4 | xxxx |
There are hundreds of rows, so I cannot use helper table for each row, but I need an array formula that I will use for calculating compound discount and final result (Total price) in each row.
Any idea?
Couldn't find anything similar on internet.
