Re: [libreoffice-users] Calc sliding scale formulae?

Jay,

Thank you. Your input is appreciated. My comments below.

On 10/01/13 19:36, Jay Lozier wrote:
> 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 350 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 350 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.

Correct

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

What if there was no previous calculation i.e. first month of year?

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

My problem is letting the formula know on which scale to work on i.e. 
ax, by,cz.

To explain:

The rates are indicated like so:

Units                   Cost per unit
0 -> 150             1.29
150.1 -> 600         1.35
600.1 -> infinity    1.60

Single units of any tier can be bought provided the previous tier 
pricing has been used i.e. 601 units would cost R811.60.

So given a currency value purchase of R2000 would equate to:
150 units @ 1.29 =      R 193.50
450 units @ 1.35 =      R 607.50 i.e. =SUM(R2k, -193.50) is >1.35 buy
749.38 units @ 1.60 =   R 1199.00 i.e. =SUM(R2k, -193.50, -607.5)/1.60

The above all worked out via calculator, BUT how can I get Calc to do it 
i.e. how is Calc going to know to only calculate on a +ve value meaning 
there is still money available for the purchase of electricity?

Further to this is working out the cost of the electricity used using 
the same sliding scale having only the number of units units used i.e. 
500 units usage?

It seems to be a case of an IF, Then argument i.e. if the units listed 
are greater than what the scale allows then the remaining units must use 
the next unit of pricing up the scale.

Anyone who wants to play can request my spreadsheet. Pick a new sheet 
and play to your hearts content. Of sending answers off list to to me is 
also OK. I'll sum them all up and give a solution on the list, if one is 
worked out.

Thanks again
Hylton
--
========================================================================
Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://e-clubhouse.org/sites/fishhoek/index.php being part of the worlds
largest non profit NGO
========================================================================



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