Stuart Noble wrote:
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.

Taking two arguments MIN or MAX are simpler than the three arguments of
an IF. MIN and MAX are also very basic mathematical functions taught to
all kids in school.

It is instructive to see that in this real life example the use of the
additional complexity of the IF statement introduces an error.

if should be
=IF(c23670;(c23-670)*c29;0)
instead of
=IF(c31670;(c23-670)*c29;0).

Finally as a matter of preference I would actually write

=MAX(0;c23-670)*c29

As to my mind this more closely models what is actually going on

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

"mark" wrote in message
...

"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


admitted ly they do, on a quarterly bill or if you phone them, but I wanted
to work mine out for my own piece of mind so that I know how much it is
costing me per day, week, month etc....
James