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

At 14:36 19/10/2013 +0200, Hylton Conacher wrote:
> Entering a formula into the Function Wizard in Calc shows up two 
> different answers in the 'Result' and 'Function Result' answer box.

Certainly: there would otherwise be no point in having two 
boxes.  Suppose you use the Function Wizard to construct 
=3+SQRT(4).  The function result is 2 but the formula result is 5.

> In addition I have tried changing the formula without success to try 
> and reduce the multiple nested IF functions:
> i.e.:
> =sum((IF(0<G4<=$G$20,G4*$F$20,0),(IF($G$20<G4<=$J$20,(G4-$G$20)*$I$20+($F$20*$G$20),0),(IF($J$20<G4<=$M$20,(G4-$G$20-$J$20)*$L$20+(($G$20*$F$20)+($J$20*$I$20),0),(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),0),(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)),0)
>
> I cannot see how the VLOOKUP function will help either as I am not 
> looking up values from cells, I am calculating a single value in H4 
> based in a variable input in G4.

But your formula refers to the values in row 20, which are thus the 
values you need to look up.  Your IF conditions are your current way 
of selecting the appropriate values from that row.

> Have a peak at the uploaded spreadsheet on Nabble:

Will do - but I'll need more than a peek!

> You will see that provided you enter a value for G4 below 600 the 
> correct answer is shown i.e. for 600 in G4 H4=750.

I think this may be more by luck that judgment, as the prices for 
your first three ranges are identical - which doesn't test your 
formula effectively.

> There must be an answer and or my formula syntax is wrong unbeknown to me.

Oh, the formula is certainly wrong.  There will be a right one ...

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