Re: [libreoffice-users] Calc IF problem

On 19/10/13 16:46, Brian Barker wrote:
> At 19:11 10/10/2013 +0200, Hylton Conacher wrote:
> > I have a spreadsheet with four pricing ranges for a certain number of
> > units.
> >
> > G4 = 601
> > H4 = calculated by below formula = 751.25 but should be 751.52
> > .
> > F20 = 1.25
> > G20 = 150 i.e. 0->150 units
> > h20 = F*G
> >
> > I20 = 1.25
> > J20 = 200 i.e. 150.0001 -> 350 units
> > K20 = I*J
> >
> > L20 = 1.25
> > M20 = 250 i.e. 350.0001 -> 600 units
> > N20 = L*M
> >
> > O20 = 1.52
> > P20 >= 600 i.e. 600.0001 -> infinity units
> > Q20 = O*P
> >
> > The below formula calculates H4
> > =IF(0<G4<=$G$20,G4*$F$20,IF($G$20<G4<=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),IF($J$20<G4<=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),IF($M$20<G4<$P$20,(G4-$G$20-$J$20-$M$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20),IF(G4>=$P$20,((G4-$G$20-$J$20-$M$20-$P$20)*$O$20+(($G$20*$F$20)+($J$20*$I$20)+($M$20*$L$20)+($O$20*$P$20))))))))))
> >
> >
> > Is there a simpler way to achieve the right answer i.e. 751.52 as I
> > have checked all the cell values and they all read as indicated here.
> >
> > Where am I making a mistake?
>
> Sorry if this appears critical, but there are a number of things to say
> here.

Criticism is not bad, it is a method of proposing different or better 
solution to a problem. Your thoughts are welcome and appreciated.

> o 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. Those same unit values 
could build a trend and so additional units could be bought in the 
colder months, without knowing what additional currency purchase value( 
i.e. ZAR200 or ZAR500) would be needed e.g. Assuming there was say 100 
units of electricity left at the end of the month and based on an 
average the house will probably use 750 units of electricity next month. 
Therefore an amount of 650 units needs purchasing. Based on the pricing 
structure on the number of units required being provided by the local 
electricity authority, what is the currency value that must be taken to 
an electricity vendor to purchase the required 650 electricity units?

> o 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....

> Setting this out as four rows of three columns would help you see what
> the likely solution was.  And you have not used those products (H20,
> K20, etc.) in your formula: instead, you have effectively recalculated
> them all, so they are just distracting.
>
> o I always hope it's obvious (but it's clearly not) that you cannot
> define what you are trying to calculate by quoting a formula that you
> know doesn't work.  This must indicate what you *don't* want!  Please,
> please omit all the detail and say what you do want.  (Forcing yourself
> to do this is a good first step in solving any problem.)

I agree, however the end value I want is determinant on the remaining 
electricity unit balance at the end of the month, which can be highly 
variable.

> o Any formula as complicated and repetitive as this cannot be the right
> way to do things.  Apart from anything else, you might not notice any
> slip you might have made in the middle of the formula, and it would be
> quite easy to miss it during testing.  Anything repetitive should be
> reduced to a simpler formula that nevertheless deals with all situations.

I agree this is a complicated formula and so far during testing I have 
managed to ascertain that despite the 350.1 -> 600 being 250, for some 
reason my formula is not taking heed of the 250 Max unit value i.e. 251 
units instead of being 250 units plus 1 unit at 1.52 being the pricing 
range for the next number of units.

> o 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.

> 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, 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.
The above is my interpretation of the IF formula in Calc.

> o 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. Even 
putting in the same formula for both True and False possibilities 
yielded the wrong answer i.e. 751.25 and not 751.52.

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. :(

> o Remember that multiplication precedes addition and subtraction in
> evaluation, so many of your parenthesis pairs are unnecessary.  You may
> think they clarify things, but in my experience they obfuscate the
> meaning of a formula and mask errors.

I tend to agree but do remember my Maths teacher drumming BODMAS into us 
all repeatedly, so it is really only done to clarify what cells are 
being 'BODMASed'.

> 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.

> I trust this helps (so far).

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 hope this clears the confusion a little.

Hylton
--
========================================================================
Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds
largest non profit NGO
========================================================================



--
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