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

the_constructor wrote:
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.

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


Hi

You don't say what to do if C23 is not less than 670. Assuming '0' for now:-


=IIF(c23670,c23*c27,0)


IIF works like this:-

iif(something is true, put this value, otherwise put this value)


Steve

"Steve Lupton" backwards.moc.liamg@pulevets wrote in message
...
the_constructor wrote:
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.

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


Hi

You don't say what to do if C23 is not less than 670. Assuming '0' for
now:-


=IIF(c23670,c23*c27,0)


IIF works like this:-

iif(something is true, put this value, otherwise put this value)


Steve

Using OpenOffice Spreadsheet prog comes up with err509 Steve

Kindest regards,

Jim

the_constructor wrote:


Using OpenOffice Spreadsheet prog comes up with err509 Steve

Kindest regards,

Jim



OpenOffice flavoured version:-

=IF(C23670;C23*C27;0)

"Steve Lupton" backwards.moc.liamg@pulevets wrote in message
...
the_constructor wrote:
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.

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


Hi

You don't say what to do if C23 is not less than 670. Assuming '0' for
now:-


=IIF(c23670,c23*c27,0)


IIF works like this:-

iif(something is true, put this value, otherwise put this value)


Steve
C32 is the cell which deals with KWh above 670, thus:

=(c23-670)*c29

Where c29=0.03557

"the_constructor" wrote in message o.uk...

"Steve Lupton" backwards.moc.liamg@pulevets wrote in message
...
the_constructor wrote:
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.

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


Hi

You don't say what to do if C23 is not less than 670. Assuming '0' for
now:-


=IIF(c23670,c23*c27,0)


IIF works like this:-

iif(something is true, put this value, otherwise put this value)


Steve
C32 is the cell which deals with KWh above 670, thus:

=(c23-670)*c29

Providing C23 = 670 .... ;-)


--
Martin

the_constructor wrote:
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

I'll go one further and say that I have no idea whatsoever on how to use
spreadsheets. I have Microsoft Excel as part of the Office suite but never
used it - opened it once, scratched me 'ed, and shut it down again ) So,
if anyone would be considerate enough to write out a complete step-by-step
idiots guide on how to do this from scratch, I would be eternally grateful.

Cheers,

Pete

"Pete Zahut" dont@bother wrote in message
...
the_constructor wrote:
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

I'll go one further and say that I have no idea whatsoever on how to use
spreadsheets. I have Microsoft Excel as part of the Office suite but never
used it - opened it once, scratched me 'ed, and shut it down again )
So, if anyone would be considerate enough to write out a complete
step-by-step idiots guide on how to do this from scratch, I would be
eternally grateful.

Cheers,

Pete


Hi Pete,

I just did a google and came up with this for you:

http://www.rgu.ac.uk/files/ACF2EE7.pdf

and

http://www.amazon.co.uk/s/ref=nb_ss_...xcel&x=17&y=15

Kindest regards,

Jim

the_constructor wrote:
"Pete Zahut" dont@bother wrote in message
...
the_constructor wrote:
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

I'll go one further and say that I have no idea whatsoever on how to
use spreadsheets. I have Microsoft Excel as part of the Office suite
but never used it - opened it once, scratched me 'ed, and shut it
down again ) So, if anyone would be considerate enough to write
out a complete step-by-step idiots guide on how to do this from
scratch, I would be eternally grateful.

Cheers,

Pete


Hi Pete,

I just did a google and came up with this for you:

http://www.rgu.ac.uk/files/ACF2EE7.pdf

and

http://www.amazon.co.uk/s/ref=nb_ss_...xcel&x=17&y=15

Kindest regards,

Jim

Thanks Jim. I'll give the pdf a good coat of looking at but I don't know as
I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out gas
and electric costs may be OK but I can't think of anything else I'd need to
use a spreadsheet for.

Cheers mate and good luck in sorting it )

Pete Zahut wrote:
the_constructor wrote:
"Pete Zahut" dont@bother wrote in message
...
the_constructor wrote:
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
I'll go one further and say that I have no idea whatsoever on how to
use spreadsheets. I have Microsoft Excel as part of the Office suite
but never used it - opened it once, scratched me 'ed, and shut it
down again ) So, if anyone would be considerate enough to write
out a complete step-by-step idiots guide on how to do this from
scratch, I would be eternally grateful.

Cheers,

Pete

Hi Pete,

I just did a google and came up with this for you:

http://www.rgu.ac.uk/files/ACF2EE7.pdf

and

http://www.amazon.co.uk/s/ref=nb_ss_...xcel&x=17&y=15

Kindest regards,

Jim

Thanks Jim. I'll give the pdf a good coat of looking at but I don't know as
I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out gas
and electric costs may be OK but I can't think of anything else I'd need to
use a spreadsheet for.

Cheers mate and good luck in sorting it )



Excel - far and away best of MS's efforts IMO. I don't think that pdf
will be too useful except as a reference - try to learn using an example
as the basis - especially if you already use Word (many of the menu
commands are similar).

Rob

Rob wrote:
Pete Zahut wrote:
the_constructor wrote:
"Pete Zahut" dont@bother wrote in message
...
the_constructor wrote:
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
I'll go one further and say that I have no idea whatsoever on how to
use spreadsheets. I have Microsoft Excel as part of the Office suite
but never used it - opened it once, scratched me 'ed, and shut it
down again ) So, if anyone would be considerate enough to write
out a complete step-by-step idiots guide on how to do this from
scratch, I would be eternally grateful.

Cheers,

Pete

Hi Pete,

I just did a google and came up with this for you:

http://www.rgu.ac.uk/files/ACF2EE7.pdf

and

http://www.amazon.co.uk/s/ref=nb_ss_...xcel&x=17&y=15


Kindest regards,

Jim

Thanks Jim. I'll give the pdf a good coat of looking at but I don't
know as I'm interested enough to buy a book - after all, I've managed
51 years without knowing how to use spreadsheets. It just seemed that
working out gas and electric costs may be OK but I can't think of
anything else I'd need to use a spreadsheet for.

Cheers mate and good luck in sorting it )


Excel - far and away best of MS's efforts IMO. I don't think that pdf
will be too useful except as a reference - try to learn using an example
as the basis - especially if you already use Word (many of the menu
commands are similar).


I think =IF(B3650;B3*B4) should get you started, where B3=consumption,
B4=rate.

Pete Zahut wrote:
the_constructor wrote:
"Pete Zahut" dont@bother wrote in message
...
the_constructor wrote:
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
I'll go one further and say that I have no idea whatsoever on how to
use spreadsheets. I have Microsoft Excel as part of the Office suite
but never used it - opened it once, scratched me 'ed, and shut it
down again ) So, if anyone would be considerate enough to write
out a complete step-by-step idiots guide on how to do this from
scratch, I would be eternally grateful.

Cheers,

Pete

Hi Pete,

I just did a google and came up with this for you:

http://www.rgu.ac.uk/files/ACF2EE7.pdf

and

http://www.amazon.co.uk/s/ref=nb_ss_...xcel&x=17&y=15

Kindest regards,

Jim

Thanks Jim. I'll give the pdf a good coat of looking at but I don't know as
I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out gas
and electric costs may be OK but I can't think of anything else I'd need to
use a spreadsheet for.

A DIY uses I find them particularly handy for is costing jobs. Just
stick in a column of descriptions, another of quantities, another of
prices. Then let it work out the total cost. Makes it very easy to tit
about with costs and prices etc.

Example:


A B C D
1 Description Unit Price Qty Total
2
3 25kg Bag sand 2.50 3 =B3*C3
4 Cement 3.90 1 =B4*C4
5
6
7
8
9 Total Cost =sum(D37)





--
Cheers,

John.

/================================================== ===============\
| Internode Ltd - http://www.internode.co.uk |
|-----------------------------------------------------------------|
| John Rumm - john(at)internode(dot)co(dot)uk |
\================================================= ================/

John Rumm wrote:
Pete Zahut wrote:
the_constructor wrote:
"Pete Zahut" dont@bother wrote in message
...
the_constructor wrote:
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
I'll go one further and say that I have no idea whatsoever on how to
use spreadsheets. I have Microsoft Excel as part of the Office suite
but never used it - opened it once, scratched me 'ed, and shut it
down again ) So, if anyone would be considerate enough to write
out a complete step-by-step idiots guide on how to do this from
scratch, I would be eternally grateful.

Cheers,

Pete

Hi Pete,

I just did a google and came up with this for you:

http://www.rgu.ac.uk/files/ACF2EE7.pdf

and

http://www.amazon.co.uk/s/ref=nb_ss_...xcel&x=17&y=15


Kindest regards,

Jim

Thanks Jim. I'll give the pdf a good coat of looking at but I don't
know as I'm interested enough to buy a book - after all, I've managed
51 years without knowing how to use spreadsheets. It just seemed that
working out gas and electric costs may be OK but I can't think of
anything else I'd need to use a spreadsheet for.

A DIY uses I find them particularly handy for is costing jobs. Just
stick in a column of descriptions, another of quantities, another of
prices. Then let it work out the total cost. Makes it very easy to tit
about with costs and prices etc.

Example:


A B C D
1 Description Unit Price Qty Total
2
3 25kg Bag sand 2.50 3 =B3*C3
4 Cement 3.90 1 =B4*C4
5
6
7
8
9 Total Cost =sum(D37)





I actually find Excel quite useful as a drawing tool.

--
Rod

Hypothyroidism is a seriously debilitating condition with an insidious
onset.
Although common it frequently goes undiagnosed.
www.thyromind.info www.thyroiduk.org www.altsupportthyroid.org

Thanks Jim. I'll give the pdf a good coat of looking at but I don't know
as I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out
gas and electric costs may be OK but I can't think of anything else I'd
need to use a spreadsheet for.

Well, I use mine for:-

Keeping rainfall records, daily temperature records, my personal cashflow
(keeps me solvent - more people should do this then they wouldn't get into
deep debt), subscriptions from members of organisations/clubs, investments
bought and sold, dividends paid, lottery handouts for the syndicate, a
Sudoku framework, electricity bills, plus a whole lot of stuff re business.
I'd have a problem to be without it.

Rob Graham

robgraham wrote:
Thanks Jim. I'll give the pdf a good coat of looking at but I don't know
as I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out
gas and electric costs may be OK but I can't think of anything else I'd
need to use a spreadsheet for.

Well, I use mine for:-

Keeping rainfall records, daily temperature records, my personal cashflow
(keeps me solvent - more people should do this then they wouldn't get into
deep debt), subscriptions from members of organisations/clubs, investments
bought and sold, dividends paid, lottery handouts for the syndicate, a
Sudoku framework, electricity bills, plus a whole lot of stuff re business.
I'd have a problem to be without it.

Rob Graham



Good for downloading online bank statements. I have 8 years I can fiddle
about with. Search, filter by name, payment method etc. Certainly
wouldn't want to be without that. Gives me the illusion I'm on top of
things :-)

In message , robgraham
writes
Thanks Jim. I'll give the pdf a good coat of looking at but I don't know
as I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out
gas and electric costs may be OK but I can't think of anything else I'd
need to use a spreadsheet for.

Well, I use mine for:-

Keeping rainfall records, daily temperature records, my personal cashflow
(keeps me solvent - more people should do this then they wouldn't get into
deep debt), subscriptions from members of organisations/clubs, investments
bought and sold, dividends paid, lottery handouts for the syndicate, a
Sudoku framework, electricity bills, plus a whole lot of stuff re business.
I'd have a problem to be without it.



And I thought I led an interesting life ...

--
bumsnase

robgraham wrote:
Thanks Jim. I'll give the pdf a good coat of looking at but I don't know
as I'm interested enough to buy a book - after all, I've managed 51 years
without knowing how to use spreadsheets. It just seemed that working out
gas and electric costs may be OK but I can't think of anything else I'd
need to use a spreadsheet for.

Well, I use mine for:-

Keeping rainfall records, daily temperature records, my personal cashflow
(keeps me solvent - more people should do this then they wouldn't get into
deep debt), subscriptions from members of organisations/clubs, investments
bought and sold, dividends paid, lottery handouts for the syndicate, a
Sudoku framework, electricity bills, plus a whole lot of stuff re business.
I'd have a problem to be without it.

Rob Graham



This is interesting if you're into energy saving, logging fuel bills
etc. Tells you by how much the temperature in your area deviated from
the average so that you can "normalise" consumption and get a better
idea of whether measures you may have taken are actually working.

http://www.degreedays.net/

I'm sure you've seen it but I thought the site deserved a plug anyway.

the_constructor wrote:

Now the formula that I need is like this:

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

MIN(C23*C27,670*C27)

"Grant" wrote in message
...
the_constructor wrote:

Now the formula that I need is like this:

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

MIN(C23*C27,670*C27)


Using OpenOffice spreadsheet program show ERR508 for the above

the_constructor wrote:
"Grant" wrote in message
...
the_constructor wrote:

Now the formula that I need is like this:

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

MIN(C23*C27,670*C27)


Using OpenOffice spreadsheet program show ERR508 for the above

Fine here using OO 3.0.1 on Ubuntu 9.04.

the_constructor wrote:
"Grant" wrote in message
...
the_constructor wrote:
Now the formula that I need is like this:

If (C23670) Then Let C31 = (C23*C27)
MIN(C23*C27,670*C27)


Using OpenOffice spreadsheet program show ERR508 for the above



OpenOffice uses semi colons not commas
=MIN(C23*C27;670*C27)

or even

=MIN(C23;670)*C27

Nick wrote:
OpenOffice uses semi colons not commas

Works fine with commas here; straight out the box, no changes.

OO 3.0.1

Grant wrote:
Nick wrote:
OpenOffice uses semi colons not commas

Works fine with commas here; straight out the box, no changes.

OO 3.0.1


Apparently commas are a trial feature in on the Ubuntu 3.0.1 version.


Hopefully they will role it out to the windows versions too.

"Nick" wrote in message
...
the_constructor wrote:
"Grant" wrote in message
...
the_constructor wrote:
Now the formula that I need is like this:

If (C23670) Then Let C31 = (C23*C27)
MIN(C23*C27,670*C27)


Using OpenOffice spreadsheet program show ERR508 for the above



OpenOffice uses semi colons not commas
=MIN(C23*C27;670*C27)

or even

=MIN(C23;670)*C27

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
readings

My thanks,

Jim

if KWh is above 670 then C23-670 multipy
by C29, but if below 670, then answer should be 0 (zero)


Google for "open office if statements".

It's probably

=IF(c31670;(c23-670)*c29;0)

if I've got the cell numbers right. There's probably a more elegant way ....

the_constructor wrote:

"Grant" wrote in message
...
the_constructor wrote:
Now the formula that I need is like this:
If (C23670) Then Let C31 = (C23*C27)
MIN(C23*C27,670*C27)
Using OpenOffice spreadsheet program show ERR508 for the above

for openoffice use semicolon instead of comma

On Mon, 06 Jul 2009 16:07:06 +0100, the_constructor wrote:

Got a little problem trying to sort out a formula for a spreadsheet.

I suspect possible solutions depend on whose spreadsheet app you're using,
and you don't say...

Jules wrote:
On Mon, 06 Jul 2009 16:07:06 +0100, the_constructor wrote:

Got a little problem trying to sort out a formula for a spreadsheet.

I suspect possible solutions depend on whose spreadsheet app you're using,
and you don't say...


This might help

http://wiki.services.openoffice.org/...c:_IF_function


Things are different in Excel, but in cell c31 try

=IF(c23670; 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

"Stuart Noble" wrote in message om...
Jules wrote:
On Mon, 06 Jul 2009 16:07:06 +0100, the_constructor wrote:

Got a little problem trying to sort out a formula for a spreadsheet.

I suspect possible solutions depend on whose spreadsheet app you're using,
and you don't say...


This might help

http://wiki.services.openoffice.org/...c:_IF_function


Things are different in Excel, but in cell c31 try

=IF(c23670; 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.


--
Martin

Martin wrote:

I suspect (in excel) you need to use comma separators, not semi-colons.

In Excel you would :-)

Martin wrote:
"Stuart Noble" wrote in message om...
Jules wrote:
On Mon, 06 Jul 2009 16:07:06 +0100, the_constructor wrote:

Got a little problem trying to sort out a formula for a spreadsheet.
I suspect possible solutions depend on whose spreadsheet app you're using,
and you don't say...


This might help

http://wiki.services.openoffice.org/...c:_IF_function

Things are different in Excel, but in cell c31 try

=IF(c23670; 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.





There's a free energy tracker spreadsheet here

http://www.energywatcher.com/2009/03...-tracking.html

In Excel but I assume it will run in OpenOffice. Needs modifying for UK
but looks quite well designed at first glance

"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

the_constructor wrote:
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

Try this ready made

http://www.filefactory.com/file/af3c...culator2_2_xls



--
Corporal Jones
"I don't like it up me"

On 6 July, 16:07, "the_constructor"
wrote:
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

to be honest I think you are making things to complex. I had a similar
situation earlier in the year when I needed to monitor BG gas & elec
consumption, I did look at Excel but in the end it was dead simple to
work it out manually and if you take daily or weekly readings it's
dead easy to work out costings and you won't end up spending hours
trying to suss out formulas unless of course you have lots of time
which you need to fill.

AJH wrote:
On 6 July, 16:07, "the_constructor"
wrote:
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

to be honest I think you are making things to complex. I had a similar
situation earlier in the year when I needed to monitor BG gas & elec
consumption, I did look at Excel but in the end it was dead simple to
work it out manually and if you take daily or weekly readings it's
dead easy to work out costings and you won't end up spending hours
trying to suss out formulas unless of course you have lots of time
which you need to fill.

But do you factor in the weather conditions? Can you normalise the
readings so that they're relatively independent of temperature? If not,
they're no indicator of energy efficiency. Everybody needs Excel, they
just don't know it.
So you take your readings daily? Blimey, I thought I had too much time
on my hands :-)

All you need is a simple "If" function, ie: logical test, if true do a,
if false do b

Thus paste: =IF(C23670,C23*C27,C23*C28) into C31, and enter in C28 the
price where consumption is more than
670KwH

Should the price for "up to 670Kwh" actually include 670Kwh, you would
need to change the logical test from C23670, to C23=670

Cheers




Stuart Noble wrote:
AJH wrote:
On 6 July, 16:07, "the_constructor"
wrote:
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

to be honest I think you are making things to complex. I had a similar
situation earlier in the year when I needed to monitor BG gas & elec
consumption, I did look at Excel but in the end it was dead simple to
work it out manually and if you take daily or weekly readings it's
dead easy to work out costings and you won't end up spending hours
trying to suss out formulas unless of course you have lots of time
which you need to fill.

But do you factor in the weather conditions? Can you normalise the
readings so that they're relatively independent of temperature? If not,
they're no indicator of energy efficiency. Everybody needs Excel, they
just don't know it.
So you take your readings daily? Blimey, I thought I had too much time
on my hands :-)

"Jim Garner" wrote in message ...
All you need is a simple "If" function, ie: logical test, if true do a,
if false do b

Thus paste: =IF(C23670,C23*C27,C23*C28) into C31, and enter in C28 the
price where consumption is more than
670KwH

Should the price for "up to 670Kwh" actually include 670Kwh, you would
need to change the logical test from C23670, to C23=670

Cheers


I think the OP already has many solutions - but yours (above) would price all units at the 2ndry rate, whereas (IIUC) the 1st 670 are at one rate, and only any excess at the 2ndry rate.

Also, OP is using Open Office, so commas become semi-colons.


--

Martin

"Martin" wrote in message
...

"Jim Garner" wrote in message
...
All you need is a simple "If" function, ie: logical test, if true do a,
if false do b

Thus paste: =IF(C23670,C23*C27,C23*C28) into C31, and enter in C28 the
price where consumption is more than
670KwH

Should the price for "up to 670Kwh" actually include 670Kwh, you would
need to change the logical test from C23670, to C23=670

Cheers


I think the OP already has many solutions - but yours (above) would price
all units at the 2ndry rate, whereas (IIUC) the 1st 670 are at one rate, and
only any excess at the 2ndry rate.

Also, OP is using Open Office, so commas become semi-colons.


--

The kind folk at British Gas work it out for me.

mark