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

On 19/10/13 20:25, Brian Barker wrote:
> 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.

Tnx, Now I see the difference i.e. the Function result is of the 
function/formula the cursor is on whilst the result is the end answer.

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

This minor statement about the price ranges being identical caused me to 
test by starting the first at 1 and incrementing each range by one until 
4. Changing the value in G4 made NO change to my calculated field <H4>, 
so guess its time to revisit which formulae to use :(

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

Still searching for the needle in the haystack...
--
========================================================================
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