2013 Archives by date, by thread · List index

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

```At 14:36 19/10/2013 +0200, Hylton Conacher wrote:
```
```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 peek at the uploaded spreadsheet on Nabble:
```
```
You can do what you need using VLOOKUP(), seeking values from a small table (much as you already have in rows 58 to 61). And you can probably more easily use MATCH(), which works fairly similarly, but returns a pointer instead of a value. But both of these methods suffer from the problem that you need to seek multiple values from your table in each calculation, so the formulae become rather cumbersome, with a necessity to retrieve essentially the same information repeatedly.
```
```
It's worth saying that it's generally not a good idea to repeat similar information - as you have at lines 21 to 32. Instead, either use values from a minimal table (as already mentioned) or embed the values in the formulae.
```
Let's redraw your table more simply - say in rows 71 to 74 of columns A and B:
1.25
150     1.25
350     1.25
600     1.52
(Nothing is needed in A71.)

Then you can calculate what you need using something like:
=MIN(G4;\$A\$72)*\$B\$71
+MAX(0;(MIN(G4;\$A\$73)-\$A\$72)*\$B\$72)
+MAX(0;(MIN(G4;\$A\$74)-\$A\$73)*\$B\$73)
+MAX(0;(G4-\$A\$74)*\$B\$74)
where G4 is your sample data cell.

```
Each line of this formula (I've split it only for ease of reading, of course) calculates the contribution to the cost from one of the four price bands. Each MIN() function ensures that the upper limit of the quantity is either the value itself or the upper limit of the band - whichever is smaller. So where are the IFs to test which bands are needed?, you ask. Well, each unnecessary contribution would be calculated here (wrongly) as negative, so the MAX() functions, with their first arguments of zero, cancel these incorrect contributions. Try it: it gives the answers you want.
```
I trust this helps.

Brian Barker

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