Discussion:
Can anyone help with a formula please ?
(too old to reply)
the_constructor
2009-07-06 15:20:35 UTC
Permalink
Hi Everyone,

Name is James from UK

A few weeks ago I had a new gas meter installed with a reading on the meter
of zero.

What I am trying to do is write a spreadsheet to work out my gas bill.

Upto 670 KWh gas is charged at 0.06530p per KWh

Here's what I have:
C23 = 34 (KWh)
C27 = 0.06530
C31 = Total

Formula needs to work out the following:
If (C23<670) Then Let C31=(C23*C27)

Can anyone help please.?

Kindest regards,

James
Don Schmidt
2009-07-06 19:51:10 UTC
Permalink
What is the rate if it exceeds 670 KWh?
--
Don
Vancouver, USA
Post by the_constructor
Hi Everyone,
Name is James from UK
A few weeks ago I had a new gas meter installed with a reading on the
meter of zero.
What I am trying to do is write a spreadsheet to work out my gas bill.
Upto 670 KWh gas is charged at 0.06530p per KWh
C23 = 34 (KWh)
C27 = 0.06530
C31 = Total
If (C23<670) Then Let C31=(C23*C27)
Can anyone help please.?
Kindest regards,
James
the_constructor
2009-07-08 05:29:27 UTC
Permalink
Post by Don Schmidt
What is the rate if it exceeds 670 KWh?
--
Don
Vancouver, USA
Post by the_constructor
Hi Everyone,
Name is James from UK
A few weeks ago I had a new gas meter installed with a reading on the
meter of zero.
What I am trying to do is write a spreadsheet to work out my gas bill.
Upto 670 KWh gas is charged at 0.06530p per KWh
C23 = 34 (KWh)
C27 = 0.06530
C31 = Total
If (C23<670) Then Let C31=(C23*C27)
Can anyone help please.?
Kindest regards,
James
Above 670, the rate changes to 0.03557 (C29)but I have this in another cell
(C32) with this formula:
=(C23-670)*C29

If it makes it any clearer to anyone, I am prepared to send you a copy of
the spreadsheet.
Don Schmidt
2009-07-08 17:38:01 UTC
Permalink
This is how I would do it in Lotus 1-2-3. Probably the same for other
"sheets" except substitute the = sign for the @ sign.

@IF(A1<=670,A1*0.0653,(A1-670)*0.03557+(670*0.0653))

This uses cell A1 for the quantity.

Also, you may want to substitute cell locations for the 670, and the two
unit costs for they will change in time.


Post back with your results.

Good luck,
--
Don
Vancouver, USA
Post by the_constructor
Post by Don Schmidt
What is the rate if it exceeds 670 KWh?
--
Don
Vancouver, USA
Post by the_constructor
Hi Everyone,
Name is James from UK
A few weeks ago I had a new gas meter installed with a reading on the
meter of zero.
What I am trying to do is write a spreadsheet to work out my gas bill.
Upto 670 KWh gas is charged at 0.06530p per KWh
C23 = 34 (KWh)
C27 = 0.06530
C31 = Total
If (C23<670) Then Let C31=(C23*C27)
Can anyone help please.?
Kindest regards,
James
Above 670, the rate changes to 0.03557 (C29)but I have this in another
=(C23-670)*C29
If it makes it any clearer to anyone, I am prepared to send you a copy of
the spreadsheet.
RUSS BARTOLI
2009-07-09 01:09:52 UTC
Permalink
Would something like this work?:


@MIN(A1,670)*0.0653+@MAX(A1-670,0)*0.03557
Don Schmidt
2009-07-09 01:53:59 UTC
Permalink
Very nice; I like it.
--
Don
Vancouver, USA
Post by RUSS BARTOLI
@MIN(A1,670)*0.0653+@MAX(A1-670,0)*0.03557
the_constructor
2009-07-09 08:36:34 UTC
Permalink
Post by Don Schmidt
Very nice; I like it.
--
Don
Vancouver, USA
Post by RUSS BARTOLI
@MIN(A1,670)*0.0653+@MAX(A1-670,0)*0.03557
My thanks to everyone for your most helpful comments. I now have the
spreadsheet working superbly.
Kindest regards,
JIm

Loading...