Re: [libreoffice-users] Calc IF problem

At 16:55 21/10/2013 +0200, Hylton Conacher wrote:
> On 19/10/13 16:46, Brian Barker wrote:
> > To get the best help from others, you really do need to explain 
> > your problem in detail - and that means words, not formulae or 
> > values.  Once you have defined your problem, it is very likely 
> > someone can help you. Indeed, explaining your problem concisely and 
> > completely may well help you to see how to solve it yourself!
>
> The spreadsheet is what I use to calculate the exact units purchased 
> and used each month for a prepaid electricity meter. [...]

Sorry, but you've missed my point.  It's not that anyone particularly 
needs to know the purpose of your spreadsheet, but that the exact 
purpose of the *formula* needed to be specified.  Originally, you 
just gave the formula, saying it didn't work, but not specifying 
exactly what it was supposed to calculate.  You needed to specify the 
purpose of your formula mathematically - separately from the formula 
that you knew wasn't correct.  (Except that I think you may still 
think the formula is correct.)

> > You have laid your values out as a row (in row 20) - as if it's a 
> > vector, a twelve by one array.  But surely it is a four by three array?
>
> Then you need another column to show what number of units were 
> purchased in each given month for each of the four price brackets....

No, you don't need "another" column: the four by three array is a 
separate table specifying the price structure.  You will, of course, 
retain a column for units - but this will be in your main table 
showing the months.

> > You have apparent logical expressions such as 0<G4<=$G$20.  Whilst 
> > A<B<C is meaningful in mathematics, where in the documentation did 
> > you find any suggestion that it makes sense in spreadsheet lingo?!
>
> Actually in the Formula Wizard as it mentions the first field needs 
> to be the test value, the next field needs to be the action if the 
> test field is true and the third field in the IF formula needs to be 
> applied if the test field evaluation is false.

Exactly so - but 0<G4<=$G$20 is *not* a spreadsheet test value - at 
least, not one meaning what you want it to mean.

> > Since it's undefined, I'm having to guess what this will mean to 
> > Calc.  First, 0<G4 will be evaluated, giving the result TRUE or 
> > FALSE.  Now the rest of the expression reduces to TRUE<=$G$20 or 
> > FALSE<=$G$20 - which make no sense, of course.  In practice, it 
> > seems that the logical values are interpreted as numbers - with 
> > FALSE being zero and TRUE being one - and this numerical value is 
> > then compared with $G$20.  But these values are 150, 200, and so 
> > on, which will always be greater than either zero or
> > one, so all the conditions will evaluate to TRUE!
>
> No offence, ...

None taken!

> ... but moving back to school mathematics the phrase 0<G4=<G20 would 
> ask a true/false question i.e. is G4 BOTH greater than 0 AND less 
> than or equal to G20. If the g4 value is only 10 then the expression 
> is true, thus allowing the corresponding action to be exercised. If 
> the g4 value was 25 the result of the initial test would have been 
> false and the other corresponding action taken.

As I acknowledged, A<B<C makes complete sense in mathematics - school 
or otherwise!

> The above is my interpretation of the IF formula in Calc.

Oh, dear: then you are doomed to continue to fail.  As I explained 
before, A<B<C is not a logical spreadsheet expression that is TRUE 
when both A<B and B<C.  Instead, A<B appears to be evaluated first, 
then the resulting value of this part is (mis)interpreted as a number 
(zero for FALSE and one for TRUE), and finally this *number* is 
compared with C.  As I also mentioned, if you want the idea of B 
being between A and C, you have two comparisons and you need *in a 
spreadsheet* an expression something like AND(A<B;B<C).  As it is, 
all your conditions will be TRUE.

> > Your last IF function has a argument for truth but not one for 
> > falsehood, so the logical value FALSE will be substituted.  You 
> > have given no alternative because your final condition is bound to 
> > be true if the previous ones have all been false, so you don't need 
> > it.  Remove that IF!
>
> My last IF I did battle with as given the previous pricing range no 
> value requiring the 600 price range would ever be below 600 units.

Exactly: so, as I mentioned, you don't need to test that condition: 
that IF is superfluous.

> Even putting in the same formula for both True and False 
> possibilities yielded the wrong answer i.e. 751.25 and not 751.52.

That's because of more fundamental errors in the formula.

> Further testing has just revealed that because the first three price 
> brackets are identical, changing the first price changes my end 
> formula, which to me indicates that the formula for units over 600 
> isn't even being used. :(

Exactly: all your conditions are TRUE.  Since you nested the IFs, the 
fact that the first one evaluates to TRUE means that all the later 
ones are never used.

> > There is a lot here that anyone helping is still having to guess, 
> > but the problem is almost certainly best solved using VLOOKUP().  I 
> > haven't had time to look at it yet ...
>
> I am afraid I disagree as the end number I need is determinant by a 
> variable input, which itself attracts different pricing based on its value.

As you will have seen, I have suggested an alternative route.  But 
using VLOOKUP() would have made sense referred to a price table: to 
calculate each part of the contribution to your cost, you need to 
look up and use in your formula both the price of each band and the 
limits of each price band.

> There is much to think about and I have to admit laying out the 
> table in a different format is my least favourite or preferred but 
> if someone can prove to me I would get the answers I seek, then the 
> least favourite would have to be implemented. How to re-arrange the 
> table to cater for four pricing ranges, each with a different max 
> number of units, over twelve months, as well as proving annual 
> totals and monthly averages is currently beyond me.

I don't think it is.  The values you have in each monthly row are 
mostly just repetitions of values in the basic price table.  You 
could harvest the relevant values directly form such a (small) table 
instead of repeating them for each month in your main table.

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