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+help@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