SAP Spreadsheet.

[lightfoot]

Hi All,

For anybody that is interested, I have put together a low cost (it's free ) and simple(ish) spreadsheet for calculating SAP ratings, it is in the same format as the SAP 2005 Worksheet (version 9.80).  It's quite basic and dose still require a element of knowledge and calculation and as they say, garbage in equals garbage out.

You basically just fill in the blank (un-shaded) boxes with the requested data, some of which will need cross referencing with the tables in the to be included 'how to' document and the rest is calculated for you, which allows you to quickly see the effect different spec's have on the overall energy efficiency and cost of running the household. 

It is a bit of a work in progress and I am still a bit of a spreadsheet virgin , so I would be very interested in other peoples advice, comments and improvements.  In the spirit of 'Open Source' the worksheet is not protected in anyway (no secrets here) so feel free to correct, modify, improve and pass on this very basic model.  Just save a master copy to go back to if need be, before you start fiddling or inputting data.   

I created the spreadsheet using Open Office and can save it as a Excel xls document, but then file is five times the size for some reason?  and may well exceed the maximum attachment size allowed.  What would be the best way to upload these files and in what version of Excel would suit most people 

I have attached the Open Office version of the basic SAP (1) worksheet, I am working on some additional info which will be added soon, but this will give you something to chew on in the meantime.  Have a good read through the accompanying 'how to' document, which I will post when someone tells me how, as it is a large file ?

I'm fairly confident that it is working as it should, but only time and testing will tell, so please report any problems found and of course I will have to add that I can't be held responsible for any errors, so use with caution until you hear otherwise.

Good luck 


Lightfoot.


Sorry, but apparently I can't attach Open Office (ods) files..........Help

[Ivan]

Excellent! Sounds very useful. If you'd like to email it to me -  ivan at navitron dot org dot uk, and if you'd like me to, I'll put it on the downloads page of the website.


Ivan

[lightfoot]

Hi Ivan,

I have mailed you a copy of the spreadsheet etc for your review, if you haven't received it let me know.

As mentioned, I have a couple of additional things to add to complete this first draft, and hopefully I will find time this week.  Before you post it on the official downloads section of the website, I would rather you put it all on the forum to give people a chance to review it first and also satisfy myself that I haven't missed anything critical.

Cheers


Lightfoot.

[kristen]

I know a fair bit about Excel, and I'd be happy to comment.

Suggest wither a link to the file somewhere, or Email it to me - send me a PM and I'll tell you the email address by return.

Kristen

[lightfoot]

Thanks, I have sent you a PM Kristen.

Maybe that's the way to go, so if anybody else would like a copy, PM or email me your address and I will send you a copy to review.  Just tell me what type of file you would like ?

Cheers

Lightfoot.

[Bargeman]

Hello Lighfoot,

I have access to an up to date copy of the NHER PLan Assessor software which includes the NHER SAP calculation. I'd be happy to run a sanity check for you against that software. Perhaps if you were able to provide me with a test data set I could input it to the Plan Assessor software and compare results.

You do realise that you may be making a rod for your own back here Lightfoot ! BRE have a habit of issuing rather frequent SAP 2005 updates. 9.81 is the latest which has just been notified and amongst other things now includes good features such as a higher export electricity price (5.7 p/kWh, up from 3.0 p/kWh) and there is now an allowance for generation from micro wind turbines under Appendix Q. I hope your users don't become too demanding.

regards

[Ivan]

Sorry for delay. It is now accessible from the download area of the new navitron website:  http://c-zero.co.uk/download.php. When we do the change-over, the url will be http://navitron.org.uk/download.php....but we're not quite finished...

Ivan

[lightfoot]

Thanks for that Bargeman, I will take you up on your kind offer, I can possibly send you a copy of a completed spreadsheet.

Yes I appreciate the update issue and needs to be kept in mind, so the worksheet can be adapted accordingly, but would it be fair to say that most of the updates would be to the reference tables or in the form of appendixes, rather than the actual basic core calculation ?

Thanks,


lightfoot.


PS, Looks like Ivan's posted it already, so maybe have a look at that first Bargeman and I will try and get some info on version 9.81...........it's worse than Microsoft !

[rhys]

I'd be happy to do the same as Bargeman, I have a copy of another approved program Sapper 7, I could put the same data into that and see how they compare.

[wookey]

Well done Lightfoot. I was a bit miffed that I couldn't find any such thing online, only 17 approved programs, _none_ of which ran on Linux, and so far as I could tell also all cost money, no doubt due to the GBP 1000 or so they got to pay to the BRE for approval.

A free software version of this would be very cool, and a spreadsheet, whilst a bit cheap and cheerful, is by far the easiest way of doing it.

As I mentioned in another thread, the SAP PDFs I downloaded seemed to have the actual worksheet itself missing, otherwise I'd probably have made a sheet myself by now.

Can the admins fix the board software so that we can upload openoffice docs please. Forcing people to upload Windows versions of files is not in the spirit of things round here :-) I see the downloads area only has the .xls version, which seems to have some minor formatting issues (truncated text due to font-size changes).

Lightfoot - can you mail me the original: mailto:wookey@wookware.org to see if it helps.

I'll be very happy to see if I can improve it any.

Cheers.

[martin]

As a fellow Open Office user, I'll have a rootle - any idea what extensions you need? - ".odt" for text files etc.....

[kristen]

Observations on the XLS file.

I can't say anything about the SAP calculation 'coz I know diddly-squat about that  so these are comments about the XLS itself.

I don;t know what things you can influence from the toolset you are using, so I'll make as comprehensive a list as I can and do with it what you wish 

There are 3 sheets, the Excel default, and content only on Sheet1.   Might help users if Sheet2 & 3 could be suppressed

Locking the first row would keep the heading visible during [vertical] scrolling

Can you do any sort of Cell Locking?  I can type anywhere on the sheet, might help if only cells where it is sensible to type are "allowed".   (Perhaps have no Password set, so that folk can remove the restriction if they wish to make modifications)

The formula fields are grey background, could the data-entry fields have a background colour, so its easy to see where to enter data?   (Should be the same cells as the "Permitted" ones above).  Light yellow or somesuch perhaps.

The formula at X12 is showing an error.  The formula is "=P12*R12", where P12 is the second floor area (m2) but R12 is the "(3a)" marker ( instead of the "Average storey height" which is cell T12.  Did the conversion roger this, or is it in the original?

Personally I would have made formulae like the one at T26 (Section 2 Ventilation rate : Number of chimneys : formula for "m3 per hour") use the actual value, rather than repeat it in the R column ("x 40") and the formula column use the "=P26*40".  Risk of transposition error.  So I would change "=P26*40" to become "=P26*R26" (and change the value in R26 from "x 40" to plain "40" so it is numeric).

If the "x 40" is the type of thing that the thread has referred to as changing in the various re-releases of the SAP info then I would make it reference a separate sheet which just stored all the values from the SAP guidelines, so that they can be changed independently.

Do any of the cells need conditional formulae in case their source data is absent?

For example, X34 ("=T34/X18") shows "#DIV/0!" which is fair enough because there is no value in T34, 'coz I haven't put anything in at all.

If Excel I would tend to do something like:

=IF(X18=0,"",T34/X18)

to display a blank cell if there was a zero in X18.

Similarly there are formula like X41 ("=(T39-1)*0.1") which, with no data entered, shows "-0.1".  Would this be better as

=IF(T39="", "", (T39-1)*0.1)

perhaps?

In Excel it is possible to have conditional formatting, so such "no data present" cells could also, for example, show a red background to indicate that their content is suspect.

Let me know if there is any other analysis/review of this file that you would like me to do

Kristen

[lightfoot]

Thanks Kristen,

I have learned a lot from that, I will work through it all and post an update a soon as I can.

Thanks again for your input.

Lightfoot.

[Ivan]

A long time ago, I wrote some webpages (I think it was from within Microsoft Word) which used Java or javascript or something like that, using boxes with calculations pretty much like Excel. Looks quite nice on a webpage, but I've forgotten how it was done, and don't use word for webpages any more (!). If I had the time, and could work out how to do it, I'd try to convert the   various calculators on the website from excel to html

[wookey]

This would be a very useful thing. There are many ways of doing this. Fundamentally you have a web-form in which you enter the data values and a cgi-script that does the sums and displays results. That script can be written in any language you like. perl, PHP and Python are popular. That is at least the most basic way of doing it which will work on every browser ever written. If you want it to do live updating, like a real spreadsheet then you need to write it in javascript or get fancy with AJAX, which is way beyond my level of web-gurudom.

Making a simple webform calculator is pretty straightforward though. I use the perl CGI framework for this sort of thing, but there are no doubt many other ways to do it.

A load of calculators for things would be a very useful facility for Navitron to provide and I'm sure we have the expertise round here. Doing it in a browser makes the facility available to just about all, and if you do it in the right way it will work no matter what computer/browser they use.

The labour-intensive bit is laying out the form. The sums are dead easy