| Computers Forum Index » Computer Applications - Spreadsheets » Can anyone help with a formula please ?... |
|
Page 1 of 1 |
|
| Author |
Message |
| the_constructor... |
 Posted: Mon Jul 06, 2009 7:20 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| Don Schmidt... |
| Posted: Mon Jul 06, 2009 11:51 pm |
|
|
|
Guest
|
What is the rate if it exceeds 670 KWh?
--
Don
Vancouver, USA
"the_constructor" <the_constructor at (no spam) freeukisp.co.uk> wrote in message
news:6fudnYv_Sqnbi8_XnZ2dnUVZ8gydnZ2d at (no spam) brightview.co.uk...
Quote: 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
|
|
|
| Back to top |
|
|
|
| the_constructor... |
| Posted: Wed Jul 08, 2009 9:29 am |
|
|
|
Guest
|
"Don Schmidt" <Don Engineer at (no spam) PNB.Retired_1987> wrote in message
news:g8ydna-kl_ozyM_XnZ2dnUVZ_u6dnZ2d at (no spam) posted.palinacquisition...
Quote: What is the rate if it exceeds 670 KWh?
--
Don
Vancouver, USA
"the_constructor" <the_constructor at (no spam) freeukisp.co.uk> wrote in message
news:6fudnYv_Sqnbi8_XnZ2dnUVZ8gydnZ2d at (no spam) brightview.co.uk...
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
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. |
|
|
| Back to top |
|
|
|
| Don Schmidt... |
| Posted: Wed Jul 08, 2009 9:38 pm |
|
|
|
Guest
|
This is how I would do it in Lotus 1-2-3. Probably the same for other
"sheets" except substitute the = sign for the at (no spam) sign.
at (no spam) 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
"the_constructor" <the_constructor at (no spam) freeukisp.co.uk> wrote in message
news:PImdnazl_scls8nXnZ2dnUVZ8rWdnZ2d at (no spam) brightview.co.uk...
Quote:
"Don Schmidt" <Don Engineer at (no spam) PNB.Retired_1987> wrote in message
news:g8ydna-kl_ozyM_XnZ2dnUVZ_u6dnZ2d at (no spam) posted.palinacquisition...
What is the rate if it exceeds 670 KWh?
--
Don
Vancouver, USA
"the_constructor" <the_constructor at (no spam) freeukisp.co.uk> wrote in message
news:6fudnYv_Sqnbi8_XnZ2dnUVZ8gydnZ2d at (no spam) brightview.co.uk...
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
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.
|
|
|
| Back to top |
|
|
|
| RUSS BARTOLI... |
| Posted: Thu Jul 09, 2009 5:09 am |
|
|
|
Guest
|
Would something like this work?:
at (no spam) MIN(A1,670)*0.0653+ at (no spam) MAX(A1-670,0)*0.03557 |
|
|
| Back to top |
|
|
|
| Don Schmidt... |
| Posted: Thu Jul 09, 2009 5:15 am |
|
|
|
Guest
|
Very nice; I like it.
--
Don
Vancouver, USA
"RUSS BARTOLI" <russ.bartoli at (no spam) worldnet.att.net> wrote in message
news:Arb5m.105445$d36.97547 at (no spam) bgtnsc04-news.ops.worldnet.att.net...
Quote: Would something like this work?:
at (no spam) MIN(A1,670)*0.0653+ at (no spam) MAX(A1-670,0)*0.03557
|
|
|
| Back to top |
|
|
|
| the_constructor... |
| Posted: Thu Jul 09, 2009 12:36 pm |
|
|
|
Guest
|
"Don Schmidt" <Don Engineer at (no spam) PNB.Retired_1987> wrote in message
news:JZCdnRjvsvYk0MjXnZ2dnUVZ_uydnZ2d at (no spam) posted.palinacquisition...
Quote: Very nice; I like it.
--
Don
Vancouver, USA
"RUSS BARTOLI" <russ.bartoli at (no spam) worldnet.att.net> wrote in message
news:Arb5m.105445$d36.97547 at (no spam) bgtnsc04-news.ops.worldnet.att.net...
Would something like this work?:
at (no spam) MIN(A1,670)*0.0653+ at (no spam) 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 |
|
|
| Back to top |
|
|
|
|
