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

As I noticed you're practically around the corner from me, I got
interested, and had a look at your spreadsheet :)

[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
amending your spreadsheet to include both solutions.

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
gladly upload it for you to look at, but I'm not sure how to upload
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 adding a whole bunch of
columns to your spreadsheet to calculate 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, then implement it in
your spreadsheet. The user function code can 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
problems with the clarity of your headings, and the complex formulas.

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 badly named.

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 that your thinking about
the problem has become over-complicated, and 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 
> 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


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