2013 Archives by date, by thread · List index

# 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

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