And the winner is …

29 04 2017

Building applications for small screen sizes is always a big challenge. Apple and Microsoft both offer their productivity suites iWork resp. Office for smartphones. If you never tried to create or use a spreadsheet on a smartphone I’m quite sure you would say that it’s against all right ideas about how things ought to be.

Well, I’m sure you would revise your opinion if you try it out for some days.

The first versions of Office for iOS were far behind Apple’s iWork suite but today the tables have turned. Microsoft overtook lots of innovative ideas Apple implemented in their mobile app versions. I don’t care about copycats. I’m a user and so I’m just interested in perfect solutions whoever created them.

Microsoft reconquered the pole position if it comes to Excel vs. Numbers.

And the reason is that Apple did a very poor job with its latest update for its spreadsheet app NUMBERS. Entering data and moving the focus from cell to cell is a disaster.

Well done, Microsoft.
Do your homework, Apple.

I would like to know who at Apple is responsible for the final inspection of the UI. Transfer him for disciplinary reasons, maybe he is better in testing the Apple car.

I would also like to know why Microsoft’s iOS versions of Office are a lot better than their Windows 10 Mobile counterparts. Sad but true.


Visualizing your Success

5 06 2015

Some people think that personal success can be measured in bucks, clicks, +1s, or Likes.

So I won’t be surprised if somebody wants to visualize his personal value with a chart created with Apple’s spreadsheet app Numbers for iOS.

Here is the tutorial.

  • Step 1
    Create a new spreadsheet within Numbers.
  • Step 2
    Create a table (table 1, top left) and enter the months and the corresponding number of bucks or clicks in a month.
    The last row should contain the sum. It’s needed to display the relative amount of success (the ratio bucks / total x 100).


  • Step 3
    Create another table (below the first table). This table isn’t really needed but usually it’s worth to separate the data from its visualization.
    Refer to the the first two columns of table 1 by using terms like ‘=Table 1::A1’. This will show the months and the absolute values of bucks.
  • Step 4
    Now let’s create the crux of the matter.
    Use the built-in function REPT (Repeat) with the money bag (from Apple’s Emoji keyboard; activation via Settings – General – Keyboard – Keyboards – Add New Keyboard and select Emoji).

Refer to Apple’s excellent help for this function and all the others to understand the parameters.

  • Param 1
    the symbol you want to use for the bar graph
  • Param 2
    the length of the bar (the monthly value in % of the total)

Use it and you will see that Numbers for iOS is perfectly adapted to touch screens.

If you want to refer to a cell of an other table in a function, just tap on it instead of entering the table number and the coordinates. That’s perfect user experience, isn’t it?

Now your visualized success is always with you, on an iPhone, an iPad, or an  Watch.

I suggest to publish your stats quarterly on the internet like Apple does in its U.S. Securities and Exchange Commission (SEC) filings.

Summary …

The usage of functions in Numbers is explained in meaningful helps.
An alternative is to enter the values in a table and use the feature Create Chart from Table. But that would be too easy. Always following the KISS principle doesn’t help you to improve your knowledge.

Related links …

Avoid a Liquidity Bottleneck

An Invoice created with Numbers

If Then Else

Thanks for visiting iNotes4You.

Avoid a Liquidity Bottleneck

18 06 2014

To avoid a liquidity bottleneck at the end of a month you can use Apple’s iWork app Numbers. Once you created an appropriate spreadsheet with the necessary information you can predict your account balance more or less accurately (if the rare event of a crashed iOS device doesn’t happen and you are out of warranty claim).

Here is an example to create the spreadsheet using functions implemented in Numbers and some other tricky algorithms. The good news is, it’s synced with all your other devices via iCloud. So the information is available wherever you are.

The components …

What you need is

  • Apple’s iWork app Numbers
    It’s part of Apple’s iWork suite, the pendant to Microsoft’s Office suite with one important difference: it’s optimized for touch screens and seamless to use even on the small screen of an iPhone and all your documents are automatically synced with all your devices. You can even work on your documents if you don’t have access to one of your Apple devices because there is iWork for iCloud with which you can work on documents via browsers like Safari, Google’s Chrome, or Microsoft’s Internet Explorer.
    And it’s free for newly purchased devices.
  • A spreadsheet within Numbers
    which allows to predict the account balance
  • Access to your bank account
    to look up the transactions and the actual balance.

If you are not familiar with programming a spreadsheet don’t hesitate to download my template from my Box account. It’s free and there is no copyright on it. If you downloaded the template you can (easily) adapt it to your needs. The following description might help.

The spreadsheet …

The template contains two essential tables.
The first table is designed to enter the actual account balance, the performed transactions as well as periodical transactions. I call it the Dynamic table

The second table contains the dates for periodical transactions like rents or taxes to be paid in advance. I call it the Static Table.

Here is how it looks like:


And here is the download link

Application File format
Apple Numbers numbers


If you tap on the link and downloaded the file you will see a control ‘Open in Numbers’ for a short time. If the message is gone just tap on the display again.

If you want to work with the template it’s highly recommended to do it with a copy of the original.

The dynamic table …

To predict the approximate liquidity at the end of a month you have to enter the actual account balance and monitor what’s expensed during the month by accessing the transactions with an online banking tool or a browser.


The spreadsheet contains the necessary formulas to add or subtract incoming and outgoing money transfer. For cash payments (food, has, etc.) an approximate amount can be set on a weekly basis.


Every time you get fresh cash from your bank you can reduce the remaining value with a so-called ‘Stepper’ control.
A Checkbox is used for payments on a monthly basis e.g. your health insurance. Just set it to true if the payment is expensed.

The static tables …

The first table is just for calculating a date 30 days prior to the actual date to point to an upcoming payment. The second table is for documentation purposes only and not used in any calculation.

The EOMONTH function returns a date 30 days prior to the actual date.


This the conditional statement which sets the factor to -1 for all months (3, 6, 9, 12) with a due date. It can be easily adapted to other periods resp. months.


This simple conditional statement sets a marker to emphasize upcoming payments.



The kernel statement (image 2) sets the factor to -1 according to due dates.
Factor x Base Value defines the content in the column ‘Value’ which is part of the summarization to get the total liquidity.

The adaption …

You have to adapt the template by entering all the necessary values. A look on your last statements of account helps to find out the values. Some of them will be fixed values others vary and you have to define an average value.

You can use the COPY+PASTE feature to add more lines of similar type. In the context menu select ‘PASTE FORMULAS’ to let numbers adapt the clipboard content.

Summary …

If you run into the red it’s not my fault.
I’m sure the reason is your optimism bias or a shaking finger followed by wrong entries.

Related links …

An invoice created with numbers

If … Then … Else

Thanks for calculating with iNotes4You.

An invoice created with Numbers

20 02 2014

Apple made the iWork suite for free when buying a new iOS device to improve the user experience by providing all the basic features users expect. The iWork Suite for iOS consists of

  • Keynote
    the presentation tool for creating slides and present them to an audience
  • Pages
    the text editor to write letters and more
  • Numbers
    the spreadsheet application for calculations, stats, and other stuff for which a table is the appropriate approach

This post is about an application using Numbers for creating an invoice.

Two versions …

There is a simple approach with a template showing all products in a table. The invoice then is created by deleting unwanted rows and adjusting the quantities.

Another version is more complex and allows to refer to an additional sheet where products are listed with their prices. It uses the embedded function ‘Lookup’ to retrieve the unit price from a sheet where all products are listed.

Both versions work with a control called Stepper to adjust the desired quantity for each item (see image below). I used Apple’s template ‘Invoice’ which is available after the installation of Numbers.

The simple version …

The kernel of this version is a table where all products are listed. A calculation for the total net value, the tax, and the gross amount has already been added by Apple. If you use the template just tap on the cells to see how the calculation is done.


Steps to create the invoice:

  • 1 Tap on the tab ‘Invoice’ and select ‘Duplicate’ from the context menu.
    This step is necessary to not loose the template for further invoices.
  • 2 Delete unwanted rows by tapping on the table (to select the table object).
    Bars appear above the columns and to the left of the rows.

  • 3 Tap on the row in the left bar to delete it.
    The whole invoice is immediately recalculated.
  • 4 Repeat this step for all unwanted items.


If product properties have to be updated select the template ‘Invoice’ and make the necessary changes for product description or unit price.

The more complex version …

An alternate version uses a separate sheet where all products are listed, the price list. Another sheet contains the template for the invoice with some rows where the desired products must be inserted by referring to the sheet ‘Products’.
This version uses two embedded features:

  • 1 the function Lookup
    to search for a product in the sheet ‘Products’ and to insert its unit price in the column ‘Unit Price’. Calculations are done by the same formulas as in version 1.
  • 2 a control called Pop-Up Menu which contains all product descriptions.
    The values for choosing a product must be added manually when creating the control. All values have to match the original product description. Otherwise the ‘Lookup’ function doesn’t return a valid unit price.
    The ‘Pop-Up Menu’ is bound to each line of the column ‘Description’.

Here are the steps to create the Stepper Control to access the unit price from the sheet Products.

  • 1 Tap on the first cell in the column ‘Description’.
  • 2 Tap on the ‘Edit’ symbol in the task bar of Numbers (top left).
  • 3 Select the tab ‘Format’ and then ‘Pop-Up Menu’.
  • 4 Create the proper values by entering the product description.
    You have to enter the exact description used in the sheet ‘Products’. Otherwise the item cannot be found.


  • 5 Use the function Lookup within the cell Unit Price to refer to the unit price in the table Products.
  • 5.1 This is done by double-tapping on the cell Unit Price. The input bar of Numbers comes up.
  • 5.2 Tap on ‘Functions’ and then on ‘Reference’.
  • 5.3 Select Lookup.
    The LOOKUP function finds a match for a given search value in one collection, and then returns the value in the cell with the same relative position in a second collection.

    LOOKUP(search for, search where, resultvalues)

    •search tor:
    The value to find. Search value can contain any value.

    Select the first parameter by tapping on it.
    Select the cell Description.
    The coordinate is overtaken as the first parameter.

    •search where:
    The collection containing the values to be searched, search where can contain any values.

    Select the second parameter by tapping on it.
    Select the sheet ‘Products’.
    Select the whole column Description by tapping on the upper bar (which comes comes up when selecting the table). The coordinates of this column are overtaken as the second parameter.

    An optional collection containing the value to be returned based on the search, result values can contain any values.

    Select the third parameter by tapping on it.
    Select the whole column Unit Price.
    The coordinates of this column are overtaken as the third parameter.

  • 5.4 Tap on ‘Finish’ (white check mark with green background).
  • 20131122-131010.jpg

    The usage …

    If you now want to create a new invoice, copy the template, enter the customer data, and compose the product list to be calculated. The latter is done by using the Stepper Controls for Description (Step 1) and Quantity (Step 2).


    The pros and cons …

    Version 1 is simple whereas version 2 is more suitable when it comes to a large number of products. The sheet ‘Products’ can also be used as a price list. Just style it with some added images of products and add the terms and conditions.

    Another benefit of the 2nd version is that the maintenance of products is centralized. With added templates for offers, delivery notes, and sales confirmation there is just one data basis, the sheet ‘Products’. If you change details of a product it will immediately be changed in all the other templates.

    Send and archive …

    Invoices can be mailed to customers as PDFs. For archiving documents it’s recommended to save a PDF on a cloud storage for which encryption is offered, in transit as well as at rest. iDriveSync for example offers client-side encryption and can be accessed via WebDAV protocol from within iWork for iOS.

    For further information about cloud storages and WebDAV access please refer to ‘Related links’ below.

    Summary …

    For small businesses Apple’s Numbers might be useful. Legislation in nearly all countries require to save invoices for a longer period e.g. in Germany it’s 10 years. Additionally it’s required to use a strictly ascending invoice number. To fulfill all the advices mentioned in the legislation can be a hard job when using Numbers for iOS.

    But as always it’s a question of accurate work and an appropriate workflow.

    Related links …

    Numbers for iOS Devices

    Numbers for the Mac

    Risky free clouds

    WebDAV basics

    Thanks for stopping by.

    If … Then … Else

    8 03 2013

    IF your device crashed THEN

    restore it from a backup


    use it for improving your life

    END IF

    Software developers call this an IF-Statement.

    The part marked in red is the CONDITION and the ones in blue are ACTIONS.
    The condition is always a question to the system with an unexceptional True or False for the answer. If the condition would sound like ‘IF the car is expensive THEN’ the following action will be up to a subjective assessment of the meaning of ‘expensive’ and no rigorous True or False.

    It’s definitely the most used statement in programming and all of us are faced with this procedure over and over. In real life it’s sometimes hard to pursue the right path and we need time to decide and may go another way if our first decision was wrong. The main difference between IFs in software development and in real life is that the CONDITION in real life often cannot be reduced to a Yes or No.

    For a better understanding look at this video.
    It explains some lines of programming and the result on the iPhone’s screen.

    I’m sure this article is boring for developers but can be interesting for normal users as IF-Statements can be effectively used e.g. in Apple’s spreadsheet app Numbers.

    So let’s leave the magic activities of app developers behind us and return to daily life.

    Consider the following situations …

    Mark a line in my spreadsheet to emphasize

    • it’s important
    • it’s greater than 500
    • there are less than 30 days to an event

    Unlike in grown up spreadsheet applications installed on big brothers of the iPad conditional formatting is not available in Numbers for iOS. However here is an alternative to feature information.

    • Double tap the cell where a marker should be displayed
    • Select = from the input bar
    • Tap on Functions
    • Select Logical and Information and then If
      Numbers puts a generalized If-Statement into the input bar.
      Your task is now to fill it out with referrals to cells.
    • For defining the If-expression tap on cell C3 and add =High.
    • The action for if-true is to display the marker.
      To do this tap on “abc” on the keyboard. An input bar opens. Enter the marker e.g. from the iOS keyboard Emoji.
    • Repeat the procedure for the if-false part of the expression and in the input bar enter nothing so that nothing is displayed if the priority is not High.


    Here is the formula for an If-Statement comparing a value with a given value.
    The marker is set if the contents of cell C5 is greater than 500.


    Here is the formula for an If-Statement comparing a given date with the current date using the NOW-Function which provides the current date and time of your devices clock. Additionally the a function called DATEDIF is used which provides the difference between two dates in a selectable unit (day, month, etc.).


    If you have any questions don’t hesitate to contact me via comment or the contact form on my page ‘About’.

    Thanks for visiting I hope you come back soon.

    Numbering in Apple’s Numbers

    6 03 2013

    Numbers is Apple’s app for creating spreadsheets.
    It’s part of the iWork Suite for iOS.

    From my point of view it’s the far away best app for working with spreadsheets on iOS devices.
    It’s simple but yet powerful. The UI is perfect and with the last update 1.7 (2012-12-04) Apple provided a functionality called ‘LOCK’ with which you may secure objects, e.g. tables so that their position and their content cannot be changed any longer until they are unlocked.

    I want to continue my series about the usage of Apple’s Numbers.
    The task is to automatically number lines in a table starting with a given value, to extend the numbering if lines are added and renumber them if lines are moved to new positions.

    Step 01 …

    Set the start value e.g. 100 in the upper left cell. The cell is identified by A1.

    Step 02 …

    Define a formula which tells Numbers how to generate the line number for the line following the first line with the start value.


    To define a formula for a cell double tap the cell and select ‘=’ from the input bar.
    The input field is prepared to enter a formula. Tap on the cell A1 which contains the start value 100. A symbol for this cell appears in the input field. Add 1 to this parameter and tap Done on yhe right side of the input field. 101 will appear in cell B1.

    Step 03 …

    I think you agree with me that you don’t want to repeat this procedure for the next 200 lines.
    So select cell B1 and tap on Copy in the upcoming context menu.
    Select cell C1 and expand the cell marker downwards for all lines you want to be numbered.
    Tap on Paste and then on Paste formulas.


    Step 04 …

    If you add lines to the table the new lines are firstly not numbered.
    To renumber all lines just copy the first cell B1 and repeat the Paste procedure described in Step (3). This can also be done after inserting new lines or changing places of lines.

    Thanks for visiting my blog.

    Update 1.7 of iWork for iOS

    7 12 2012

    On 2012-12-04 Apple’s iWork-Suite was updated to version 1.7.

    This update is a major improvement of iWork for iOS without destroying the simplicity of usage. The updated features are perfectly designed and retain the simplicity of the product. From my point of view that’s an intellectual stimulus for all people developing software.

    We will see how the competitor Microsoft will response.

    For now, advantage Apple!

    The feature changes …


    Pages for iOS …

    • Use Change Tracking to track changes to body text in a document
    • Accept and reject individual changes as you review a document
    • Import Pages and Microsoft Word documents with change tracking and continue to track changes to body text
    • Preserve tracked changes in documents exported in Microsoft Word or Pages format
    • Preserve calculations in tables when importing from and exporting to Pages for Mac

    Numbers for iOS …

    • Hide and unhide rows and columns
    • Import and export Numbers for Mac spreadsheets with filters, and turn filters on and off
    • Preserve rich text in tables when importing and exporting

    Keynote for iOS …

    • Import and export all Microsoft PowerPoint and Keynote for Mac slide sizes
    • Import and export presentation themes, complete with master slides and preset styles
    • Play back all Keynote action builds including Move, Rotate, Scale, and Opacity
    • Add new slide transitions including Shimmer and Sparkle
    • Preserve calculations in tables when importing from and exporting to Keynote for Mac
    • New print layouts include options to print with presenter notes, with builds, and without backgrounds

    Common improvements …

    • Lock and unlock objects
      For locked objects changing properties (size, position, content) cannot be changed until they are unlocked.
    • Add reflections to shapes
    • Improved compatibility with the pendants Microsoft Word, Excel, Powerpoint

    Some details …

    Locking of objects …

    On my point of view this is a great new feature, because faulty operations on touch screens cannot be avoided particulary on the small screen of an iPhone.


    Objects which can be locked in …

    Pages Numbers Keynote
    Shape ‘Curve’ only All Objects All Objects

    Improved page print layout in Keynote


    Lacks in Numbers …

    Locking …


    SIRI and the keys …


    %d bloggers like this: