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 <[email protected]> >To: [email protected] >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: [email protected] >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: [email protected] 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
