Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

At 17:31 21/10/2013 +0200, Hylton Conacher wrote:
On 19/10/13 19:32, Jean-Francois Nifenecker wrote:
Le 19/10/2013 14:36, Hylton Conacher a écrit :
The initial formula is:

this is waaay too convoluted to be easily checked.

I would agree, but it should still work.

Any formula that cannot be effectively checked cannot be *relied on* to work, of course.

-> I share Brian's idea about using VLOOKUP() through a 3 lines set of data.

I have no problem using VLOOKUP but this data does not lend itself towards using VLOOKUP, to my minor knowledge.

If someone could give me more of an explained example of VLOOKUP using the pricing structure and unit limits on each, that can be expanded across 12 months, I will look into it further.

As you now, I've now abandoned VLOOKUP() as being the easiest or best way of solving your problem, but to answer your question, my idea was that you would have a separate small table of the price bands and values (instead of repeating them for each month), let's say in A1 to B4:
   0    1.25
150     1.25
350     1.25
600     1.52

Now an expression such as VLOOKUP(X;A1:B4;2) - where X represents your input value - will find the price for the band including your value. The function searches the A column for the value, finds the highest band boundary not exceeding that value, and then returns the corresponding value from the B column (the second column because of the "2"). That's a start, but the problem is that you'd need to use the idea repeatedly even for each input value, as you need to include values from lower price bands in your calculation too. The MATCH() function is similar, returning the row number instead of a value, and you could use that - but with the same drawback. With these schemes, you would not repeat the price band values across each monthly row but refer back to the small table in the formula for each month's calculation - which I think is a better structure in any case.

In the end, it's easier, I think, to use MAX(0;...) to switch on or off the contribution from each band to the calculation.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.