2013 Archives by date, by thread · List index

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

```
```
```Hi,

Entering a formula into the Function Wizard in Calc shows  up two

If the editing cursor is placed in a certain section of the formula,
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.

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
Additionally, the formula I am suggesting might not be optimized, and
it is certainly not the only solution.

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,

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