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
--
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.