2013 Archives by date, by thread · List index

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

```On 19/10/13 20:25, Brian Barker wrote:
```
```At 14:36 19/10/2013 +0200, Hylton Conacher wrote:
```
```Entering a formula into the Function Wizard in Calc shows up two
```
```
Certainly: there would otherwise be no point in having two boxes.
Suppose you use the Function Wizard to construct =3+SQRT(4).  The
function result is 2 but the formula result is 5.
```
```
```
Tnx, Now I see the difference i.e. the Function result is of the function/formula the cursor is on whilst the result is the end answer.
```
```
```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)

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.
```
```
But your formula refers to the values in row 20, which are thus the
values you need to look up.  Your IF conditions are your current way of
selecting the appropriate values from that row.

```
```Have a peak at the uploaded spreadsheet on Nabble:
```
```
Will do - but I'll need more than a peek!

```
```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.
```
```
I think this may be more by luck that judgment, as the prices for your
first three ranges are identical - which doesn't test your formula
effectively.
```
```
```
This minor statement about the price ranges being identical caused me to test by starting the first at 1 and incrementing each range by one until 4. Changing the value in G4 made NO change to my calculated field <H4>, so guess its time to revisit which formulae to use :(
```
```
```There must be an answer and or my formula syntax is wrong unbeknown to
me.
```
```
Oh, the formula is certainly wrong.  There will be a right one ...
```
```
Still searching for the needle in the haystack...
--
========================================================================
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
========================================================================

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