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

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. 

Also i really don't think we have a problem with trying to help people with
their homework.  Of course there are always 1 or 2 awkward people in any
social group or people that got out the wrong side of bed or just "having a
bad hair day" (or month/year/decade).  I might have ethical issues if you
were getting grades for our work but we really don't answer that quickly. 
As far as most (i suspect) of us are concerned it's just another interesting
puzzle to figure out.  

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.  

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.
Regards from
Tom :)




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
> -- 
> ========================================================================
> 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-tp4037350p4037519.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