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 <[email protected]> wrote: > 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)" <[email protected]> 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: [email protected] 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
