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

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

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 :o) 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

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)

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

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 :o)
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

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

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

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

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

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 :o) 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 :o)

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

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.

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

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 :o) 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 :o)



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

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

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 :o) 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 :o)


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.

O T: Spreadsheet help
 

Martin wrote:

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

In Excel you would :-)

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

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

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)

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

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"

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 :o) 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(D3:D7)





--
Cheers,

John.

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

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.

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 :o) 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(D3:D7)





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

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

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 :-)

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

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

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.

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

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

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

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.

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

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)

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.

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

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.