UK diy (uk.d-i-y) For the discussion of all topics related to diy (do-it-yourself) in the UK. All levels of experience and proficency are welcome to join in to ask questions or offer solutions.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default O T: Spreadsheet help

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



  #2   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 25
Default O T: Spreadsheet help

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
  #3   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 52
Default Spreadsheet help

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


  #4   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 22
Default Spreadsheet help

the_constructor wrote:

Now the formula that I need is like this:

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


MIN(C23*C27,670*C27)


  #5   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 1,668
Default O T: Spreadsheet help

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...




  #6   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default Spreadsheet help


"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


  #7   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default O T: Spreadsheet help


"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


  #8   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default Spreadsheet help


"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



  #9   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 17
Default Spreadsheet help


"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

  #10   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default O T: Spreadsheet help


"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









  #11   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 52
Default Spreadsheet help

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 )


  #12   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 17
Default O T: Spreadsheet help


"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

  #13   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 22
Default Spreadsheet help

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.


  #14   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default O T: Spreadsheet help

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
  #15   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 48
Default Spreadsheet help

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


  #16   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 17
Default O T: Spreadsheet help


"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

  #17   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 48
Default Spreadsheet help

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.
  #18   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default O T: Spreadsheet help

Martin wrote:

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

In Excel you would :-)
  #19   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 11
Default Spreadsheet help

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
  #20   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 22
Default Spreadsheet help

Nick wrote:
OpenOffice uses semi colons not commas


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

OO 3.0.1




  #21   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 25
Default O T: Spreadsheet help

the_constructor wrote:


Using OpenOffice Spreadsheet prog comes up with err509 Steve

Kindest regards,

Jim



OpenOffice flavoured version:-

=IF(C23670;C23*C27;0)
  #22   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default O T: Spreadsheet help

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
  #23   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 31
Default O T: Spreadsheet help


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"
  #24   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 25,191
Default Spreadsheet help

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 |
\================================================= ================/
  #25   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 11
Default Spreadsheet help

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.


  #26   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
Rod Rod is offline
external usenet poster
 
Posts: 2,892
Default Spreadsheet help

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
  #27   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 11
Default Spreadsheet help

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


  #28   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default Spreadsheet help

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 :-)
  #29   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 58
Default Spreadsheet help

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
  #30   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 215
Default Spreadsheet help

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


  #31   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default Spreadsheet help

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.
  #32   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default Spreadsheet help


"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



  #33   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default Spreadsheet help


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 ....
  #34   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 17
Default Spreadsheet help


"Stuart Noble" wrote in message om...

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 ....


Not sure if it's any more elegant, but having used MIN for the first 670, the_constructor could use =MAX(0;(c23-670)*c29). And I don't see any advantage in not combining the two elements in one cell, per my original post.


--
Martin



  #35   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 4,341
Default Spreadsheet help

On Wed, 08 Jul 2009 15:26:25 GMT, Stuart Noble wrote:

f 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".


Try

http://wiki.services.openoffice.org/...tation/How_Tos

has a load of stuff that I found useful.
--
Peter.
The head of a pin will hold more angels if
it's been flattened with an angel-grinder.


  #36   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 274
Default Spreadsheet help


"PeterC" wrote in message
...
On Wed, 08 Jul 2009 15:26:25 GMT, Stuart Noble wrote:

f 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".


Try

http://wiki.services.openoffice.org/...tation/How_Tos

has a load of stuff that I found useful.
--
Peter.
The head of a pin will hold more angels if
it's been flattened with an angel-grinder.


My thanks to everyone for your most helpful comments. I now have the
spreadsheet working superbly.
Kindest regards,
JIm


  #37   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 11
Default Spreadsheet help

Martin wrote:
"Stuart Noble" wrote in message om...
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 ....


Not sure if it's any more elegant, but having used MIN for the first 670, the_constructor could use =MAX(0;(c23-670)*c29). And I don't see any advantage in not combining the two elements in one cell, per my original post.



I think you are right and I have, to a certain extent, programmed
spreadsheets professionally.

Max and Min are not so much "more elegant" but I would say "more easily
understandable".

Hope that makes you feel better ;o)
  #38   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 11
Default Spreadsheet help

Huge wrote:
On 2009-07-09, Nick wrote:

I think you are right and I have, to a certain extent, programmed
spreadsheets professionally.


No offence, but I would suggest that it is not possible to program
spreadsheets "professionally" due to the total inadequacy of the
tool. For money, certainly....


Offence taken. I like to pride myself that I have always taken clients
money for an overpriced, poor and shoddy service. If that isn't
professional I don't know what is.
  #39   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 17
Default Spreadsheet help


"Nick" wrote in message ...
Martin wrote:
"Stuart Noble" wrote in message om...
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 ....


Not sure if it's any more elegant, but having used MIN for the first 670, the_constructor could use =MAX(0;(c23-670)*c29). And I don't see any advantage in not combining the two elements in one cell, per my original post.



I think you are right and I have, to a certain extent, programmed
spreadsheets professionally.

Max and Min are not so much "more elegant" but I would say "more easily
understandable".

Hope that makes you feel better ;o)


Heaps better - thanks - until I read the follow-up from Huge. I use excel [nearly] all the time and, whilst not perfect, it meets the needs. That said, I get paid for the answers it generates, not (usually) for "programming" per se :-)


--
Martin

  #40   Report Post  
Posted to free.uk.diy.home,uk.d-i-y
external usenet poster
 
Posts: 5,937
Default Spreadsheet help

Nick wrote:
Martin wrote:
"Stuart Noble" wrote in message
om...
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 ....


Not sure if it's any more elegant, but having used MIN for the first
670, the_constructor could use =MAX(0;(c23-670)*c29). And I don't see
any advantage in not combining the two elements in one cell, per my
original post.



I think you are right and I have, to a certain extent, programmed
spreadsheets professionally.

Max and Min are not so much "more elegant" but I would say "more easily
understandable".

Hope that makes you feel better ;o)


Well, the OP's original question was expressed in IF terms and, to a
beginner, this seems like a logical extension of the way we think.
"What do you want to see in cell A10?"
"Er, that depends. If this, then that, but, if not, then something else"
I got by quite nicely with IFs until they got so long and full of
brackets I was forced to learn a bit more.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Car fuel consumption spreadsheet Dave Baker UK diy 51 January 30th 09 01:31 AM
New construction spreadsheet. cln Home Repair 3 November 6th 07 03:17 AM
HandyDandy Excel/Works spreadsheet. Robatoy Woodworking 1 January 29th 07 12:05 AM
anyone know a good mortgage comparison spreadsheet? [email protected] Home Ownership 3 July 21st 06 08:26 AM
Electrical estimating spreadSheet? ashnook UK diy 0 March 3rd 06 09:42 AM


All times are GMT +1. The time now is 07:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 DIYbanter.
The comments are property of their posters.
 

About Us

"It's about DIY & home improvement"