O T: Spreadsheet help

[Steve Lupton]

OpenOffice flavoured version:-

=IF(C23

[Stuart Noble]

>

the cell is numeric, for any subsequent calcs whcih depend on it.

There's a free energy tracker spreadsheet here

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

[Corporal Jones]

Try this ready made

[John Rumm]

>

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)

[Nick]

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.

[Rod]

I actually find Excel quite useful as a drawing tool.

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

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

[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

has 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