"the_constructor" wrote in message ...
Got a little problem trying to sort out a formula for a spreadsheet. Yes,
some of you may think that I should use a different group, but all the
computer groups are not as helpful as this group.

Can anyone help ?

C23 = 34

C27 = 0.6530

C31 = Total

Now the formula that I need is like this:

If (C23670) Then Let C31 = (C23*C27)

I am trying to write a spreadsheet to work out the cost of my gas bill. New
meter installed with reading of zero a couple of weeks ago.

upto 670 KWh gas is charged a 0.06530p per KWh
after that it is charged at another price. I got the after bit worked out
but beggered if I can sort the first bit.

Anyone wish to hazzard a guess at the formula ?

Kindest regards,

Jim

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