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

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.

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.

--
Jay Lozier
jslozier@gmail.com


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