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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Context
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.