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

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