2013 Archives by date, by thread · List index

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

```Ok, so I just used a file hosting service found via Google search.
Here's the file I modified with my two solutions:

http://www.filedropper.com/electricity_1

It didn't work the first time or two that I tried to download the file
to check, not sure why, but it worked the third time, so maybe
persevere if it doesn't work first time for you.

When I tried to open the file again, I got told the security settings
prevented macros from running, and the cells that used the user
function to calculate your answer showed "#VALUE", I think it was. You
can reduce the security settings to allow macros to run, of course, but
perhaps this is not the best way forward after all, although it is
still an elegant solution for some values of elegant.

So if using just formulas, I liked Brian's use of MAX(0, value) instead
of my IF(value > 0, value, 0), so I would probably substitute that
first off, to make the formulas I've used more legible. I would still
strongly recommend intermediate steps for each line of Brian's formula,
and then one simple SUM to get the final answer.
```
```=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)
```
```
Also, while Brian has, as I understand it (without actually trying it
out), combined the information into a simple table, much as I first felt
should be done, this does mean the price brackets can't change per
month, while my solution does allow for this. Your choice.

Hope this helps, and feel free to ask me for any clarifications of what
I've done.

Paul

P.S. On a separate note, how does one sign macros for use with LO? I've
come across the same issue with Excel, and never got round to finding
out. If one can sign the macros, can the end user choose which signed
macros to trust, or does one essentially have to buy a
trusted certificate, and end users only get the option of trusting all
such certificates? This is how I understand Excel to work, though I may
be wrong.

I really feel there should be an easy way to generate "self-signed
certificates" for one's own macros, and end users should be given a
white-list of certificates which they can trust, such that you can
create macros and sign them, and simply tell your end users to add your
certificate to their trust list, and all macros from you will be run
without questions, while all other macros will not be run, without the
end user having to choose to allow your macros each time, like medium
security caters for. Perhaps this should be a feature request? Or am I
horribly missing the point of macro security?

On Sun, 20 Oct 2013 01:15:05 +0200
Paul <paulsteyn1@afrihost.co.za> wrote:

```
```As I noticed you're practically around the corner from me, I got

[tl;dr: I got solutions, but need some help uploading the file. Also,
it may be that you could work it out in a *much* simpler way, using
what you already have, but a) something is broken in your other
formulas, and b) that's if they are supposed to do what I think they
are.]

I can see what you are trying to do, but it took some figuring out.
I'm not convinced you have everything laid out correctly in that
file. For example, your "Max Units" column has a complicated
calculation that doesn't actually just figure out the difference
between the two "brackets" (as I called the groups). For example,
between the 0->150 bracket and the 150->300 bracket, the difference
should be 300 - 150, but instead you calculate something using an IF.
I don't know why, but I wasn't really focusing on that, it just seems
odd (and possibly incorrect) to me.

As for your problem at hand. The formula you are using is waaaaay too
complex. I would not use something like that, I would either split it
up into more columns, or write a user function for it. I actually
played a little with it to figure out how it worked, and ended up

As I understand the problem, you have a value that needs splitting up
into the different price brackets, and then a per bracket price is
calculated, and you need the total of the per bracket prices. The
split is by amount, that is to say the first bracket is the first 150
units of the value, the second bracket is the next 200 units, and so
on. Each bracket has a unit price, and once you figure out how many
units of the value falls into each bracket, you can work out the
price for those units, and then total it to get a final price for the
value. Sorry if that is a convoluted explanation, but it's the best
way I could think of to describe it.

It is not clear to me how VLOOKUP could be used for this, as the
problem as I understand it isn't one of matching your cell to a given
list of criteria and inserting a corresponding value.

I also can't explain my solutions here, as I haven't simply "fixed"
your formula. I have saved your file with both solutions. I would
files. I know I can't do it via email, as attachments apparently get
stripped, and I can't see how to do it from nabble, unless you only
get that option when logged in. If so, what are my login details? As
I'm subscribed to the list, I would assume that means I have a login
for nabble, or is it a separate thing? All I know about nabble is
that it has to do with the mailing lists...

The user function solution is elegant, and simply replaces your
formula with a user function call, and the user function is written
in LO Basic. Very straight forward. I have only used your original
columns as input to that function. I have assumed only four price
brackets, so if any more are added, the function calls and the
function itself will need some amending. The other solution involves
intermediate steps. This does mean no additional coding skills
required, and the benefit is each step is then simple and clear, your
solution can be easily checked, and you have the break-downs by price
bracket. Again four brackets are assumed, but should more be made,
you can simply add more columns, which you would have to do anyway.
The downside is more columns, but as the ones I added all contain
formulas, they can be hidden once the formulas are copied to all
relevant rows.

Note that I messed around a bit with the spreadsheet, possibly
removing some formulas in the process, so don't simply use the
spreadsheet as I give it to you. Read and understand my solution,
probably be copied straight out, though.

So yes, I have a solution for you, I just need some assistance
uploading it. The moral of the story, though, is that you shouldn't
try to make such convoluted formulas. Use columns for intermediate
steps, and keep each step simple. Or use user defined functions if
you must have it in one step and have some coding skills. If there is
a simpler, built-in function that can do this, or that can simplify
your formula to manageable proportions, I don't know what it is.

Paul

P.S. Looking at your "Max Units" column now, it seems like this may be
one of the values I am calculating, so my solutions could have been a
little simpler. However, this is not clear to me, as those cells
calculate their value based on values in their row, and not in the row
of the "Last Day Closing Balance" column. I assume the two sets of
rows will contain the same data, but I don't know that for sure. If
so, why don't you simply total up the "Rand Value" columns in the
second set of rows?

And looking at this shows even more problems with the spreadsheet.

For one, there is a row above the month rows with values, what are
they for? Now that I look closer, I see why they are there, but
between them and the column headers, things are unclear. I guess that
doesn't matter so long as you understand it, but it makes it not
obvious to anybody trying to help you. It does mean my solutions
could have been simpler. But taking out the duplications I have made
is an exercise I leave to the reader :)

Secondly, there is a problem with your formula in the "Over 600 units"
column. That formula gives an incorrect (as far as I understand its
purpose) result, meaning that you can't simply total up those columns
to achieve your goal. Or maybe I don't understand its purpose. More

Thirdly, don't use "SUM" for simple calculations. It seems to work,
but I think it's a bad idea. Instead of "=SUM(C4:C4)" simply use
"=C4" and for "=SUM(Y21*W21)" simply use "=Y21*W21".

And if you can fix the formula so that you can simply total up that
row, then it just shows that the whole thing is so complex that even
you didn't see that there was a *very* simple solution to your
problem. Having two sets of rows for the same data does mean possible
overlaps in functionality. Perhaps the solution isn't to fix your
formula, but to go over the whole spreadsheet, and simplify, or at
least re-evaluate the purpose of, each column. You might find you
have more of the intermediate steps than you realise, they're just

Whew, this email has become waaay longer than I planned. And sorry, it
wasn't meant to sound too critical. I think it's great that you're
putting this all together, and I realise that what seems confused to
me makes perfect sense to you. But when I see the formula you had
problems with, and how much of the intermediate steps you may
actually have had but I couldn't initially find, it just suggests
you need to take a step back and re-think it all, in a step by step
manner. Happens to me all the time.

On Sat, 19 Oct 2013 14:36:08 +0200
"Hylton Conacher (ZR1HPC)" <hylton@conacher.co.za> wrote:

```
```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, only the 'Function Result' answer box holds the correct
value in the calculated cell, which unfortunately is the wrong

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

```
```

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