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