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 [email protected] -- To unsubscribe e-mail to: [email protected] 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
