Re: [libreoffice-users] Calc IF problem

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.

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!

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

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.

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

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!

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.

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 trust this helps (so far).

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