Hi,
zr1hpc wrote
Using Calc 3.4.5 on openSuse 11.2
I am trying to work out the currency value of the amount of electricity
units I have left at the end of the month (G2).
This same formula will be used for the other 11 months of the year with
only the G2 reference changing to G3, G4...etc.
For those who wish to stretch their grey matter as mine just ain't
stretching any more.
The pricing scale the energy supplier has given me is:
Unit Range Value per unit
0-150 1.1320
150.01 -> 600 1.1811
600.01 -> 1.4018
Therefore the first 150 units are priced at 1.132 ea. any units still
available need to be calculated at the next rate of 1.1811, but only up to
449.90 units at the second rate. Anything more than that must be at the
highest rate of 1.4018.
Assuming my unit balance of G2 = 433 I can work out the currency value of
G2 by following the following logic, and using the scale above:
433 > 150 therefore the initial 150 units need to be priced at 1.1320 i.e.
150* 1.132= ZAR169.80. To this the balance of the units over 150 need to
be multiplied by the next rate of 1.1811 i.e. 433-150= 283. Since 283 is
below the max of 449.9 units allowed for this scale, therefore 283*1.1811=
ZAR334.25. The third scale is not needed as all the units have been
accounted for, however it must be available should G2 exceed 600 units
e.g. taking 601 units; the first 150 would be costed at 1.132, the next
449.9 costed at 1.1811 and the balance of 1.1 units costed at 1.4018 per
unit.
Therefore total value of 433 units is:
150*1.1320 = 169.80
+ 283*1.1811 = 334.25
_____________________
Value ZAR504.05
This formula will give you the result :
=SUMPRODUCT(G2>{0;150;600};G2-{0;150;600};{1,132;0,0491;0,2207})
0.0491 is the difference of the cost between the cost for 150>units<600 and
units<150
0.2207 diff between cots for units>600 and 150>units<600
Look at this post in the forum :
http://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=40938
Download the file of my post (the 3rd one) to see how it works.
Gérard
--
View this message in context:
http://nabble.documentfoundation.org/Calc-scale-formula-conundrum-logic-confusion-tp4037350p4037526.html
Sent from the Users mailing list archive at Nabble.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
Context
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.