Published by Ewan Spence at 9:24 GMT, January 27th 2005
In part one of this series of articles, we looked at the basics of a spreadsheet, and how to construct a spreadsheet on your smartphone. Part two looks at using a PC or Mac to write your spreadsheet, and then transfering it to your smartphone.
Developing 'off-device' is an important part of any programming langauge. Almost every C++ or Java prorgammer of smartphones will do the majority of their work on a PC. It's a much larger screen, it's easier to see what's going on and type in a lot of information. And when things go wrong (which they will) all of that will let you focus on the real problem in the spreadsheet.
Quickoffice, on both UIQ and Series 60 smartphones, can read in spreadsheets saved in the latest Microsoft Excel formats (mHTML). And while the 9500 and 9300 Communicators can read in the Excel format, dragging and dropping Excel files onto these devices using their PC Suites will automatically convert them into the much more efficient Symbian Sheet format. If you can, you should always try to convert them as you move them over.
If you have a copy of Microsoft Office (available from all good computer retailers), then you should have Excel available. Quickoffice's spreadsheet saves and reads files in the standard Microsoft format of mHTML, which features in Office XP and Office 2003. Earlier versions of Office will require an Excel Plug-in (freely downloadable with Quickoffice) that adds a "Quickoffice" menu to your version of Excel.
Excel has a huge number of functions, graphical options, charting and other options. You may not find it surprising, but many of these are not part of the spreadsheet applications in your smartphone. So always follow the KISS rule - Keep It Simple, Stupid.
Stupid in this sense doesn't mean do a simple spreadsheet, but to keep what you do in the spreadsheets within the realms of what your smartphone can do. So feel free to format the colour of cells, but don't go overlaying jpeg impages along the top line showing a fancy title. If you start using some wierd mathematical functions, then check that these commands are available on your smartphone.
Let's build our first spreadsheet using your desktop computer. Open up your spreadsheet program on your desktop, and use "File... New..." to create an empty spreadsheet. It might be an idea for you to check out how to add in information, edit the content of the cells, and do the basic formatting that you practiced on the Sales Tax calculator in part one.
Let's look at another spreadsheet close to everyone's hearts. How much you get paid for your work. The aim here is to have a spreadsheet where we put in the number of hours that you work, and it then works out various deductions, to arrive at your net pay for that week. As with any program, it's important to consider the information you are going to put into the spreadsheet, information that will change each time you want to do a calculation (and which numbers will stay the same), and what information we want to get out.
Obviously these are geared to my wages in the UK, but you will be able to adapt these to your own circumstances - which is one of the great things about programming something specifically for yourself.
With our blank spreadsheet now in front of us on the PC, we can add in all these variables and constants into column A, so we can see which number is which.
As well as our yellow bar to explain what each column is for, we have the first 'section' where I can input my hours, the second section where the deductions are carried out, and a final section of one line, showing the money I actually recieve.
We work out the total hours by multiplying the number of shifts by the length of shifts (the variable is the hours in a single shift), and then adding in the number of single hours. The formula doing this is in cell B6, and is =(B4*C4)+B5. The brackets here are used to make sure the calculation inside the cell is done before any calculations. Just like you learned in your Maths classes. Then we take the hours worked and multiply this by the hourly rate (the variable in C7) to get the pay without any deductions. Note that we're using the result of a formula in B6, and it is treated as if it was a number.
Percentage deductions act on the gross wage, so we can take the value in B7 and apply some percentage changes to it - just as we did in the VAT example in part one. We have two percentage deductions here, one for 22% and one for 8%: B9=B7*C9, and B10=B7*C10. The third deduction here is a fixed deduction, so we simply copy over the value from the variable into the B column.
Now let's add all these deductions together. How would you do it? If you said that cell B12 would be =B9+B10+B11, you'd be right. But imagine if we had to add up one hundred cells in a column. That's a lot of typing. But we can use a function. This is a special type of formula that has a command and some arguments (normally cell references). To add up a column or row of cells, we can type =SUM(B9:B11). This adds up the value of all the cells from the first cell referenced (B9 here) to the last cell (B11).
So now we have my total wage earned (in cell B7) and the amount of deductions (B12) we can go =B7-B12 to get the wage I'll end up with after a week of work.
That's it for this week - you'll probably want to enter the spreadsheet, then tailor it for your own use. You might want to play around with the insert rows and insert column options on your PC spreadsheet when adding in cells. In part three we'll look at a much larger spreadsheet, and start to build up a simple Bank Account Tracker in a spreadsheet.
Share This (Digg, del.icio.us, Facebook, etc.)
Categories: How To, Software
Platforms: Series 60, Series 80, UIQ
You can use this widget to send bookmarks or notes for this page to your favourite sharing / bookmarking service. You will need an account at mnay of these services and may need to log in to use them.