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

Hylton,

If you can breakup the formula into smaller units you might find the
problem. 

Often when I have had a similar problem I found the my problem
was I reversed test in an IF clause.

On Sat, 2013-10-19 at 14:36 +0200, Hylton Conacher (ZR1HPC) wrote: 
> Hi,
>
> Entering a formula into the Function Wizard in Calc shows  up two 
> different answers in the 'Result' and 'Function Result' answer box.
>
> If the editing cursor is placed in a certain section of the formula, 
> only the 'Function Result' answer box holds the correct answer i.e. 
> 751.52004. Pressing 'OK' puts the 'Result' answer box value in the 
> calculated cell, which unfortunately is the wrong answer i.e. 751.25.
>
> I have looked at the help file to see if there was a difference between 
> the Result answer boxes but found none.
>
> The initial formula is:
> =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))))))))))
>
> 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)
>
> The SUMIF function was also tried but my brain just gave up.
>
> 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.
>
> Have a peak at the uploaded spreadsheet on Nabble:
>
> http://nabble.documentfoundation.org/file/n4077843/Electricity.ods
>
> 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. The moment G4 exceeds 
> 600, even by 1, there is a problem.
>
> There must be an answer and or  my formula syntax is wrong unbeknown to me.
>
> Help would be very much appreciated
>
> Sincerely
> 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
> ========================================================================

-- 
Jay Lozier
jslozier@gmail.com


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