Re: [libreoffice-users] Calc Function Wizard - IF Result differences

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