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

Hi :)
Someone else asked a very similar question about a month ago and i think someone created a neat 
calculation or spreadsheet to help figure it out.  Now the trick is trying to find it in the 
archives!
Good luck and regards from
Tom :)  





> ________________________________
> From: zr1hpc <hylton@conacher.co.za>
> To: users@global.libreoffice.org 
> Sent: Thursday, 14 February 2013, 6:07
> Subject: [libreoffice-users] Calc scale formula conundrum/ logic confusion
>
> 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
>
> Values used in formulae below are:
> G2 = 433 available units
> $F$18 = 1.132 cost per unit
> $G$18 = 0 -> 150 units
> $H$18 = 169.80 = value of F*G
> $I$18 = 1.1811 cost per unit
> $J$18 = 150.1 -> 600 units
> $K$18 = 531.38 = value of I*449.90 = 600-150.1
> $L$18 = 1.4018 cost per unit
> $M$18 = 600.01
> $N$18 = 841.09 = M*L however this value is dependent on how many units
>         over 600 column G2:g13 is.
>
> SUM(IF(G2>=$G$18,$H$18,(G2-$G$18)*$F$18),IF($G$18>(G2-$G$18)<=$J$18,$K$18,(G2-$G$18)*$I$18),IF((G2-$G$18-$J$18)>=$M$18,$N$18,(G2-$G$18-$J$18)*$L$18))
>
> Even taking out the SUM(), and replacing 'commaIF' with '+IF's gives the
> incorrect answer i.e. 467.22.
>
> There must be a problem with my logic in the above formula as the answer is
> so close, but for the life of me I cannot see it.
>
> Asking for some 'logic'al help to work out the correct value in H2:H13
>
> File attached via Nabble:  Electricity.ods
> <http://nabble.documentfoundation.org/file/n4037350/Electricity.ods>  
>
> Appreciated
> 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
> ========================================================================
>
>
>
> --
> View this message in context: 
> http://nabble.documentfoundation.org/Calc-scale-formula-conundrum-logic-confusion-tp4037350.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
-- 
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