[libreoffice-users] Re: Calc scale formula conundrum/ logic confusion

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