> 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 > -- The spreadsheet structure you are using is not so adequate for this calculation.
I have a suggestion for your formula in H4. But, before I post it, you should know that there are better possibilities, and that my suggestion doesn't solve all your problems in that spreadsheet. Additionally, the formula I am suggesting might not be optimized, and it is certainly not the only solution. Anyway, in your posted spreadsheet, Electricity.ods -> Data2 sheet -> Cell H4, I suggest the following formula as one possibility: =(0<G4)*(G4<=$G$20)*(G4*$F$20)+($G$20<G4)*(G4<=$G$20+$J$20)*($G$20*$F$ 20+(G4-$G$20)*$I$20)+($G$20+$J$20<G4)*(G4<=$G$20+$J$20+$M$20)*($G$20*$ F$20+$J$20*$I$20+(G4-($G$20+$J$20))*$L$20)+($G$20+$J$20+$M$20<G4)*($G$ 20*$F$20+$J$20*$I$20+$M$20*$L$20+(G4-($G$20+$J$20+$M$20))*$O$20 Hopefully, the email width format won't mess up my intention (a reply to this email probably will). Best Regards, Ady. -- 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
