Spreadsheets Discussion: Can Anyone Help With A Formula Please ?

Can Anyone Help With A Formula Please ?
Posts: 7

Report Abuse

Use this form to report abuse or request takedown.
The requests are usually processed within 48 hours.

Page: 1   (First | Last)

The_constructor
2009-07-06 11:20:35 EST
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 15:51:10 EST
What is the rate if it exceeds 670 KWh?


--
Don
Vancouver, USA


"the_constructor" <the_constructor@freeukisp.co.uk> wrote in message
news:6fudnYv_Sqnbi8_XnZ2dnUVZ8gydnZ2d@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
>
>
>
>
>
>
>



The_constructor
2009-07-08 01:29:27 EST

"Don Schmidt" <Don Engineer@PNB.Retired_1987> wrote in message
news:g8ydna-kl_ozyM_XnZ2dnUVZ_u6dnZ2d@posted.palinacquisition...
> What is the rate if it exceeds 670 KWh?
>
>
> --
> Don
> Vancouver, USA
>
>
> "the_constructor" <the_constructor@freeukisp.co.uk> wrote in message
> news:6fudnYv_Sqnbi8_XnZ2dnUVZ8gydnZ2d@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.



Don Schmidt
2009-07-08 13:38:01 EST
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




"the_constructor" <the_constructor@freeukisp.co.uk> wrote in message
news:PImdnazl_scls8nXnZ2dnUVZ8rWdnZ2d@brightview.co.uk...
>
> "Don Schmidt" <Don Engineer@PNB.Retired_1987> wrote in message
> news:g8ydna-kl_ozyM_XnZ2dnUVZ_u6dnZ2d@posted.palinacquisition...
>> What is the rate if it exceeds 670 KWh?
>>
>>
>> --
>> Don
>> Vancouver, USA
>>
>>
>> "the_constructor" <the_constructor@freeukisp.co.uk> wrote in message
>> news:6fudnYv_Sqnbi8_XnZ2dnUVZ8gydnZ2d@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.
>



RUSS BARTOLI
2009-07-08 21:09:52 EST
Would something like this work?:


@MIN(A1,670)*0.0653+@MAX(A1-670,0)*0.03557



Don Schmidt
2009-07-08 21:53:59 EST
Very nice; I like it.


--
Don
Vancouver, USA


"RUSS BARTOLI" <russ.bartoli@worldnet.att.net> wrote in message
news:Arb5m.105445$d36.97547@bgtnsc04-news.ops.worldnet.att.net...
> Would something like this work?:
>
>
> @MIN(A1,670)*0.0653+@MAX(A1-670,0)*0.03557
>
>



The_constructor
2009-07-09 04:36:34 EST

"Don Schmidt" <Don Engineer@PNB.Retired_1987> wrote in message
news:JZCdnRjvsvYk0MjXnZ2dnUVZ_uydnZ2d@posted.palinacquisition...
> Very nice; I like it.
>
>
> --
> Don
> Vancouver, USA
>
>
> "RUSS BARTOLI" <russ.bartoli@worldnet.att.net> wrote in message
> news:Arb5m.105445$d36.97547@bgtnsc04-news.ops.worldnet.att.net...
>> Would something like this work?:
>>
>>
>> @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


Page: 1   (First | Last)


2020 - UsenetArchives.com | Contact Us | Privacy | Stats | Site Search
Become our Patron