Page 2 of 3
• posted on July 6, 2009, 4:43 pm

the_constructor wrote:

OpenOffice uses semi colons not commas =MIN(C23*C27;670*C27)
or even
=MIN(C23;670)*C27
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 4:51 pm
Nick wrote:

Works fine with commas here; straight out the box, no changes.
OO 3.0.1
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 9:43 pm
Grant wrote:

Apparently commas are a trial feature in on the Ubuntu 3.0.1 version.
Hopefully they will role it out to the windows versions too.
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 8, 2009, 2:15 pm

You chaps on free.uk.diy and home.uk.d-i-y are so helpful I can not thank you enough, the above worked champion.
Now, thinking that I had already sussed the other calculation out for KWh above 670, I have fallen flat on my face.
C23 = 34
C27 = 0.6530
C29 = 0.03557
C31 = Total for 1st 670 KWh ( formula =MIN(C23;670)*C27 )
C32 = Total for above 670 KWh
so C32 needs to be something like: if KWh is above 670 then C23-670 multipy by C29, but if below 670, then answer should be 0 (zero)
anyone like to sort this for me please.
I am using OpenOffice.
I have all the calculations for sorting KWh on the gas bill from the meter readings
My thanks,
Jim
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 8, 2009, 3:26 pm

Google for "open office if statements".
It's probably
=IF(c31>670;(c23-670)*c29;0)
if I've got the cell numbers right. There's probably a more elegant way ....
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 8, 2009, 4:33 pm

Not sure if it's any more elegant, but having used MIN for the first 670, the_constructor could use =MAX(0;(c23-670)*c29). And I don't see any advantage in not combining the two elements in one cell, per my original post.
-- Martin

<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 9, 2009, 12:20 pm
Martin wrote:

the_constructor could use =MAX(0;(c23-670)*c29). And I don't see any advantage in not combining the two elements in one cell, per my original post.

I think you are right and I have, to a certain extent, programmed spreadsheets professionally.
Max and Min are not so much "more elegant" but I would say "more easily understandable".
Hope that makes you feel better ;o)
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 9, 2009, 5:00 pm

Heaps better - thanks - until I read the follow-up from Huge. I use excel [nearly] all the time and, whilst not perfect, it meets the needs. That said, I get paid for the answers it generates, not (usually) for "programming" per se :-)
-- Martin
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 9, 2009, 5:13 pm
Nick wrote:

Well, the OP's original question was expressed in IF terms and, to a beginner, this seems like a logical extension of the way we think. "What do you want to see in cell A10?" "Er, that depends. If this, then that, but, if not, then something else" I got by quite nicely with IFs until they got so long and full of brackets I was forced to learn a bit more.
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 10, 2009, 8:25 am
Stuart Noble wrote:

Taking two arguments MIN or MAX are simpler than the three arguments of an IF. MIN and MAX are also very basic mathematical functions taught to all kids in school.
It is instructive to see that in this real life example the use of the additional complexity of the IF statement introduces an error.
if should be     =IF(c23>670;(c23-670)*c29;0) instead of     =IF(c31>670;(c23-670)*c29;0).
Finally as a matter of preference I would actually write
=MAX(0;c23-670)*c29
As to my mind this more closely models what is actually going on
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 8, 2009, 6:23 pm
On Wed, 08 Jul 2009 15:26:25 GMT, Stuart Noble wrote:

Try
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos
has a load of stuff that I found useful.
--
Peter.
The head of a pin will hold more angels if
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 9, 2009, 5:57 am

<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 7, 2009, 8:56 pm
the_constructor wrote:

for openoffice use semicolon instead of comma
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 3:37 pm
On Mon, 06 Jul 2009 16:07:06 +0100, the_constructor wrote:

I suspect possible solutions depend on whose spreadsheet app you're using, and you don't say...
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 4:16 pm
Jules wrote:

This might help

http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_IF_function
Things are different in Excel, but in cell c31 try
=IF(c23<670; c23*c27; "")
I imagine it needs the equals sign before it, but it doesn't say so in the wiki. Substitute a zero for "" if you want it to display a zero
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 4:28 pm

I suspect (in excel) you need to use comma separators, not semi-colons.
Also, "" will normally display 0, so I suggest use 0 anyway - and hence ensure the cell is numeric, for any subsequent calcs whcih depend on it.
-- Martin
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 4:36 pm
Martin wrote:

In Excel you would :-)
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 6:03 pm
Martin wrote:

There's a free energy tracker spreadsheet here

In Excel but I assume it will run in OpenOffice. Needs modifying for UK but looks quite well designed at first glance
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 3:54 pm

Assuming you're using excel, and that the 670 threshold applies to each charging period (qtrly?), and noting the beauty of s/sheets is you can just change selected variables when appropriate, I would suggest...
Label stuff (for future ref) e.g. ----------------------------------------- cell C1 = Price 1 (pence/KwH) cell C2 = Price 2 (pence/KwH) cell C3 = Threshold cell C5 = KWh used cell C7 = Total price
Enter inital values in adjacent cells, e.g. -------------------------------------------------------- cell D1 = 0.653 cell D2 = 0.050 cell D3 = 670 cell D5 = 1200 cell D7 = =MIN(D3,D5)*D1+MAX(0,D5-D3)*D2
Explanation.... --------------------- D2 - whatever price the "excess" units are charged at
D7 - the "Min" bit caters for actual total consumption being less than 670 (also see Grant's post) - the "Max" bit deals with the "excess" units, but prevents a negative charge if total consumption is less than 670.
Other things... -------------------- You'll also need some formulas to convert meter reading to KWh, via "correction factor", "calorific value", "feet to metres" etc....
And at the end, add any standing charge and 5% VAT.
Tweak number formats so you get req'd number of decimals etc.
Take care not to mix up pounds and pence ...!
HTH - but re-post if anything's not clear.
-- Martin
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
• posted on July 6, 2009, 8:46 pm
the_constructor wrote:

http://www.filefactory.com/file/af3c110/n/Energy_Calculator2_2_xls
--
Corporal Jones
"I don\'t like it up me"