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

On 10/01/13 17:38, 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.

The calculation of the number of unit of electricity across a sliding 
scale has been solved.

A member of this mailing list suggested using the IF formula. With some 
formula tweaking with &, it proved to be the right answer. Tnx to that 
member. I only wish they had corresponded via the mailing list as 
opposed to directly, as perhaps there would have been greater learning 
for all subscribers.

I have attached the completed to date file, via BCc email, for those who 
showed an interest in solving the problem, as I know the ML doesn't 
accept attachments. . I anyone else wants it, feel free to contact me 
directly at hylton@conacher.co.za and request it.

I have tested the Purchases tab in the spreadsheet using LibreOffice 
3.4.5 and using common currency values, it works.

Next are the other tabs etc.

Thanks again to those that helped
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