O T: Spreadsheet help

[robgraham]

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

[Stuart Noble]

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

[geoff]

In message , robgraham writes

And I thought I led an interesting life ...

[Andy Burns]

for openoffice use semicolon instead of comma

[Stuart Noble]

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.

[the_constructor]

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

[Stuart Noble]

Google for "open office if statements".

It's probably

=IF(c31>670;(c23-670)*c29;0)

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

[Martin]

Not sure if it's any more elegant, but having used MIN for the first =

670, the_constructor could use =3DMAX(0;(c23-670)*c29). And I don't see = any advantage in not combining the two elements in one cell, per my = original post.

--=20 Martin

[PeterC]

Try

a load of stuff that I found useful.

[the_constructor]

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

[Nick]

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)

[Martin]

670, the_constructor could use =3DMAX(0;(c23-670)*c29). And I don't see = any advantage in not combining the two elements in one cell, per my = original post.

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

--=20 Martin

[Stuart Noble]

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.

[Nick]

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(c23>670;(c23-670)*c29;0) instead of =IF(c31>670;(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

[AJH]

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.

[Stuart Noble]

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]

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

Thus paste: =IF(C23 >>> Got a little problem trying to sort out a formula for a spreadsheet. >>> Yes,

[Martin]

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.

--=20

Martin

[mark]

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_constructor]

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