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

On 2013-02-18 07:31, Hylton Conacher (ZR1HPC) wrote:
> Hi Tom,
>
> On 14/02/13 16:41, Tom wrote:
> > Hi :)
> > Yes, of course you can ask different questions.  I just thought it was
> > exactly the same question again in which case my answer wouldn't have 
> > been
> > so wrong.
>
> Thanks , I was hoping so.
>
> <snip>
>
> > I wonder if there is a really good Maths forum for getting help with 
> > this
> > sort of thing.  It's a bit sideways from our normal ways of thinking 
> > so we
> > might not be much good at it.  A Maths forum like that might even be 
> > good
> > place for students or kids to get unusual but real-life questions to 
> > figure
> > out.  A chance for teachers to escape irrelevant ancient dusty 
> > text-books
> > and get real.
>
> Whilst I agree a good Maths/Calc forum wouldn't be a bad idea, I think 
> considering the software in use on LibreOffice Calc, then the users 
> should mostly be able to find a resolution to their problem regarding 
> formula syntax and entry. This problem is complex but hopefully once 
> solved will help others with scale calculations.
>
> Given the subscribers to the list are the folk who would be most 
> likely to use the formula, its correct usage and syntax is imperative 
> or perhaps it might indicate a problem with a formula in Calc's 
> repertoire.
>
> > Of course an obvious answer is to avoid using a spreadsheet for this
> > initially and just use the back of an envelope or scrap of paper from
> > elsewhere.  When the question states "the amount of unit i have left" 
> > that
> > suggests an upper limit that can't be exceeded such that
> > Total amount of units allowed = Used + amount left
> > So i think i need to know which of these 2 following statements are 
> > true and
> > which isn't (or am i completely off-track?)
> > G2= Total amount allowed, the limit
> > or
> > G2 = amount left
> > In the question it seems to be being used in both ways at different 
> > times.
> > Well, to me anyway.
> Scraps of paper are sometimes a handy quickie solution but if the 
> results need graphing over a few readings, a spreadsheet really is 
> better as I am sure you'll agree :) , mind you that said a database 
> would be the best, but that gets REALLY complicated. :)
>
> G2 in this example is the total electricity unit reading taken at the 
> end of the month that indicated how many units are left or available. 
> What I am aiming to work out is the currency value of those units(H2) 
> using the same scale that is used to purchase the units.
>
> Using my Purchase scale and changing the purchase value in c22 for 
> e.g., I can see that I would need to layout ZAR574.61 to be allocated 
> 150 units on the first scale and 282.99 on the second scale totalling 
> R574.60,5 - vat= R504.05 to give me the value of 433 units.
>
> The question is to work out the value obtained in c22 auto-magically 
> for H2, without using C22 to give the ZAR value of G2 or 432.9 units.
>
> The formula in H2 incorporates the first two scales i.e. 
> `=IF(G2>=$G$18,$H$18,G2*$F$18)+IF(G2-$G$18>=$J$18,$K$18,(G2-$G$18)*$I$18)'
> Provided G2 is 433 the correct answer for H2 of R504.05 is received. 
> HOWEVER using the same formula, if you make G2 =1. The answer in H2 
> should be R1.13 yet shows an answer of R -174.85?
>
> As the above formula only incorporates 2 of the 3 scales, the full 
> formula is 
> `=IF(G2>=$G$18,$H$18,(G2*$F$18))+IF(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-$M$18)*$L$19,(G2-$G$18-$J$18-$M$18)*$L$19)' 
> yet for some reason I am getting a 508 error yet I have checked the 
> number of brackets and all is OK?
>
> > 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
> > >
> > > 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
Hi.
If you have G2 as your input,
F18 = 1.132, G18=150, H18=1.1811, I18=600, J18=1.4018
 and calculate your answer with
=if($G$2<=$G$18,$G$2*$F$18,if($G$2<=$I$18,($G$2-$G$18)*$H$18+$g$18*$f$18,($g$2-$i$18)*$l$18+($i$18-$G$18)*$h$18+$g$18*$f$18))

It seems to work ok.
You and cut and paste the formula into the formula bar.
Your organisation on the page may not lead to easy copying down, I don't 
know if you want to accommodate different tariffs in different months or 
if the tariffs stay the same.
If the tariffs can vary each month then you can get rid of a lot of $ 
signs to make copying easier.
Steve


--
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