Calc sliding scale formulae?

users
#1

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

#2

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.

#3

Jay,

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

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

#4

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.

#5

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

#6

Hi :slight_smile:
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 :slight_smile:

#7

This message never seemed to get thru :frowning:

#8

Hi,

Hi :slight_smile:
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 :slight_smile:

I still await that spreadsheet Tom :slight_smile:

I hope the further explanation I sent the list makes it thru this time.

Please forward it to me via pvt email.

Tnx

Hylton

#9

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

#10

Hi :slight_smile:
I'm just using Nabble to upload the file from the mystery person that kindly
solved the problem.
Electricity.ods
<http://nabble.documentfoundation.org/file/n4032958/Electricity.ods>

I dunno what she (or he) said in their private email to Hylton but at least
we have the spreadsheet. It's much more complicated than mine but it does
the job and mine didn't.
Regards from
Tom :slight_smile: