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
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.
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
Max and Min are not so much "more elegant" but I would say "more easily
Hope that makes you feel better ;o)
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 :-)
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.
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
Finally as a matter of preference I would actually write
As to my mind this more closely models what is actually going on
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
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.
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
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.
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.
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.