2013 Archives by date, by thread · List index

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

```Hi Steve,

Your input provided the solution to the formula syntax and use.

Had you looked, I am assuming you did not, at the Nabble document I had
uploaded with the original question you would have seen the correct values
for some of the  'Givens' you listed i.e.

If you have G2 as your input,   CORRECT
F18 = 1.132   CORRECT
G18 = 150     Correct
H18 = Rand Value of 150 units at the unit price ie F18*G18 = R169.80
I18 = 1.1811
J18 = 449.90
K18 = I18*J18 = R531.38
L18 =  1.4018
M18 =  600.01
N18 =  841.09

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

HOWEVER,
Using Your formula as a template, I rectified a mistake or two and arrived
at this formula:

=IF(G2<=\$G\$18,G2*\$F\$18,IF(G2<=\$J\$18,(G2-\$G\$18)*\$I\$18+\$G\$18*\$F\$18,(G2-\$G\$18-\$J\$18)*\$L\$18+(\$K\$18+\$H\$18)))

to achieve the the correct answer for cell H2 ie R504.05 as a unit value for
433 units in C2.

I removed the \$ around \$G\$2 as it is the reference that has to change ie
G3,G4..G13
Because only the scale cell figures are absolute ie with \$, the formula can
be copied down the column of H from cell H2 all the way to H13 with only the
initial cell reference G2 changing along the way ie G3,G4,G5 etc.

Thank you VERY much for the assistance of everyone. I have included a Nabble
link to the file, for those who may be interested.

Electricity.ods
<http://nabble.documentfoundation.org/file/n4038664/Electricity.ods>

Regards
Hylton

--
View this message in context:
http://nabble.documentfoundation.org/Calc-scale-formula-conundrum-logic-confusion-tp4037350p4038664.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
```