O T: Spreadsheet help

[the_constructor]

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 (C23

[Steve Lupton]

Hi

You don't say what to do if C23 is not less than 670. Assuming '0' for now:-

=IIF(c23

[Pete Zahut]

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

[Grant]

[Jules]

I suspect possible solutions depend on whose spreadsheet app you're using, and you don't say...

[the_constructor]

Using OpenOffice Spreadsheet prog comes up with err509 Steve

Kindest regards,

Jim

[the_constructor]

Using OpenOffice spreadsheet program show ERR508 for the above

[Martin]

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 =3D Price 1 (pence/KwH) cell C2 =3D Price 2 (pence/KwH) cell C3 =3D Threshold cell C5 =3D KWh used cell C7 =3D Total price

Enter inital values in adjacent cells, e.g.

-------------------------------------------------------- cell D1 =3D 0.653=20 cell D2 =3D 0.050 cell D3 =3D 670 cell D5 =3D 1200 cell D7 =3D =3DMIN(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.

--=20 Martin

[the_constructor]

C32 is the cell which deals with KWh above 670, thus:

=(c23-670)*c29

Where c29=0.03557

[Martin]

Providing C23 >=3D 670 .... ;-)

--=20 Martin

[Grant]

Fine here using OO 3.0.1 on Ubuntu 9.04.

[Stuart Noble]

This might help

are different in Excel, but in cell c31 try

=IF(c23

[Martin]

=20>=20> Things are different in Excel, but in cell c31 try

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.

--=20 Martin

[Stuart Noble]

In Excel you would :-)

[Nick]

OpenOffice uses semi colons not commas =MIN(C23*C27;670*C27)

or even

=MIN(C23;670)*C27

[Grant]

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

OO 3.0.1

[Steve Lupton]

OpenOffice flavoured version:-

=IF(C23

[Stuart Noble]

>>> Things are different in Excel, but in cell c31 try

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

There's a free energy tracker spreadsheet here

but looks quite well designed at first glance

[Corporal Jones]

Try this ready made

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