O T: Spreadsheet help

Page 2 of 3  

the_constructor wrote:

=MIN(C23*C27;670*C27)
or even
=MIN(C23;670)*C27
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Nick wrote:

Works fine with commas here; straight out the box, no changes.
OO 3.0.1
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Grant wrote:

Hopefully they will role it out to the windows versions too.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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 ....
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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)
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

My thanks to everyone for your most helpful comments. I now have the spreadsheet working superbly. Kindest regards, JIm
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
the_constructor wrote:

for openoffice use semicolon instead of comma
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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...
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Jules wrote:

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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Martin wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

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
D5 - your actual consumption
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
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
the_constructor wrote:

Try this ready made
http://www.filefactory.com/file/af3c110/n/Energy_Calculator2_2_xls
--
Corporal Jones
"I don\'t like it up me"
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

Related Threads

    HomeOwnersHub.com is a website for homeowners and building and maintenance pros. It is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.