Hi :) That was my approach too but then i forgot to send my spreadsheet to Hylton and by the time i remembered Jay had produced an answer that took it to the next level. I think it's good to see these sorts of problems occasionally because it pushes us outside of our normal thought processes. Regards from Tom :)
>________________________________ > From: Dan Lewis <elderdanle...@gmail.com> >To: users@global.libreoffice.org >Sent: Friday, 18 January 2013, 15:34 >Subject: Re: [libreoffice-users] Calc sliding scale formulae? > > Perhaps the private suggestion was correct. Perhaps your understanding >of Algebra is not as good as it needs to be. After all this is an Algebra >problem. > Now for some questions: >1. How much will 1 to 150 units cost? What is the formula to determine this? >2. How much will 151 to 599 units cost you? What is the formula for this? >3. How much will 600 units cost? What is the formula to determine this? >4. How much will 601 or more units cost you? What is the formula for this? >5. What is the cost per unit for any given number of units? What is the >formula for this? (Hint: this is a very simple division problem.) >Hint: I have asked you for 5 formula, and that is also the number of cells you >will need: one cell per formula. You will be using a result in some cells in >the formula in another cell. > >--Dan > >On 01/18/2013 07:00 AM, Hylton Conacher (ZR1HPC) wrote: >> It has been privately suggested to me that I want my work done for me, >> however, whilst I have had theory from the list on how the formulae should >> work, I have not had anyone indicating how I can get Calc to only use a >> particular formula range until its result exceeds the amount allowed for >> that formula, and to then use a different range for the remaining value. >> >> My email below indicates the scales used, and please see below for the >> calculation of the number of units per pricing bracket obtained, using a >> calculator. >> >> What is the main problem is getting Calc to only use a formula up until a >> max value before moving onto the next formula. >> >> Assuming I purchased R2000 of the unit(electricity). As per the scale I >> would get 150 units at R1.29 which would equate to R193.50, which leaves a >> further R1806.50 that was used to buy electricity, but at different rate per >> unit. >> >> Taking the remaining R value and dividing it by 1.35(being the next value in >> the sliding scale) provides an answer of 1338.1 units. Only 450 units are >> allowed to be purchased at this price however, so 450*1.35=R607.50. >> >> So all in all we have only spent 193.50 and 607.50 = R801.00 out of R2000. >> >> Therefore the remaining R1199.00 divided by 1.60(being the next value in the >> sliding scale) = 749.38 units. >> >> Therefore our R2000 purchased 150+450+749.38=1349.38 units. >> >> I trust the above better explains what I need Calc to do. Perhaps it is not >> possible, but I challenge you all to solve this one. >> >> > On 01/10/2013 10:38 AM, Hylton Conacher (ZR1HPC) wrote: >>>> Hi, >>>> >>>> I am using Calc 3.4.5 on openSuSe Linux 11.2 >>>> >>>> I am working on a spreadsheet to cater for the monthly measurement of >>>> household electricity consumption, new unit purchase and costing of >>>> used units. >>>> >>>> The electricity supplier gives me the first 150 units of electricity >>>> at R1.29 per unit. The next 450 units are costed at R1.35 per unit. >>>> Thereafter the units cost me R1.60 per unit. The monetary values per >>>> set amount of unit changes when the electricity provider increases >>>> prices i.e. the first 150 units might increase to R1.50 per purchased >>>> unit, the next 450 units might be costed at R1.55 and thereafter units >>>> will cost R2.00. >>>> >>>> Each time I purchase electricity the units I am allocated are based on >>>> the above sliding scale starting from 0. >>>> >>>> How can I work out the number of units I will receive for a given >>>> currency value i.e. R2000.00? i.e. x @ R1.29, y @ R1.35 and z @ R1.60= >>>> R2000 >>>> >>>> In addition how could I work out the value of units consumed using the >>>> above scale? >>>> >>>> I am assuming the R values in the scale will change over time and >>>> these can each be averaged to calculate the cost of units used, but >>>> the problem is still to work out the formula. >>>> >>>> Anybody have an idea if this is possible and where to start? >>>> >>>> I am investigating results found on Google but would appreciate a >>>> pointer or three. >>>> >>>> Thanks >>>> Hylton >>> If I understand your problem, you have a general equation of ax + by + >>> cz = d where a = 150, b = 350, c is currently unknown, d = 2000 (or any >>> value you choose), x = 1.29, y = 1.35, and c = 1.60. Solving for c, you >>> get c = (d - ax - by)/z. >>> >>> Average cost per unit, ave, is ave = (a + b + c)/d., if I understand >>> your question The value c must be calculated prior to this step. In >>> terms of a Calc formula the c value would reference the cell with the >>> previous calculation. >>> >>> In your spreadsheet I would set a table with named cells for each value >>> (a1 is Base_Rate and is 1.29, etc) so you can easily change the values >>> and see the values used. > > -- For unsubscribe instructions e-mail to: users+h...@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
