Button Description Listing
There is a keypad for each input box. Both keypads are identical with ten numeric keys, a decimal [point] key and a key marked 'CL' which clears the relevant input box.
The string of digits in the input box is added to by each keypress on the keypad. If numbers are already in the input box, the keypad will add digits to the right.
The cursor does not need to be in the relevant input box for the keypad to work, and the cursor is not moved after the keypad is used, except for the CL key which clears the input box
and puts the cursor in the box.
The decimal button adds a decimal point to the right hand end of the string of existing digits. If more than one decimal point is entered the value will default to zero.
Digits can be entered directly from the PC keyboard if the cursor is in the input box. If non–numeric characters are entered, the value will default to zero, when the cursor leaves the input box.
Back to Calculator Image
The input boxes show the values to be used for a calculation, and remain in–place and reusable after each calculation.
The values can be input directly from the spreadsheet, either by pre–selecting cells before starting In–Cell, or by selecting a cell or cells
and using the Resample button. For details of pre–selection, re–sample and other methods, go to the Data Input page.
In general if the first cell and the last cell in a selection contain a valid number, then the values in the first and last cells are placed into Inputs #1 and #2 respectively.
Each input box has an adjacent Copy button which will copy the contents of the Active Cell into the input box, replacing any existing contents. Note that this copy function uses In–Cell's Active Cell, not the spreadsheet's highlighted cell, (although these can be the same). The Active Cell has a colored border which is user selectable.
The adjacent Sum button will 'sum' all valid numbers in the highlighted range, replacing any existing contents.
Data can be entered from the keypads or by direct cursor/keyboard entry into the input boxes. Existing values can be modified by adding digits to the end of the number from the keypad, or anywhere in the number using the cursor and keyboard entry.
The backspace button removes the last digit while the CL key on the keypad erases the whole number.
The CL key also places the cursor in the relevant input box. Using the other keypad keys does not change the location of the cursor.
Data can also be transferred into either of the input boxes from Memory (M1Re or M2Re buttons) or from the Result box (Inp1 or Inp2 buttons in the From Result box To: group).
Note that the value in either of the input boxes will default to zero if the value is not a valid number. This occurs when the cursor leaves the input box or when a calculation is attempted.
Back to Calculator Image
Memory functions
Memory functions work like classic calculator memories. There is one memory associated with each input. To clear a memory use the M1Ca or M2Ca button. M1Sa and M2Sa save the current value
of input #1 or #2 to the relevant memory, replacing the current value of the memory. M1+/M2+ and M1–/M2– add or subtract the input value from the existing memory value.
The memory value can be sign–changed with the adjacent +/– button.
The value in the two memories can be exchanged with the 'Mem 1 <> Mem 2' button.
The memory values can be exported directly to the result box using the To Result box from: memory move buttons M1 and M2, (just below the result box).
In reverse, the result of calculations can be added to/subtracted from either memory, using the four M1+, M1–, M2+, M2– buttons in the From Result box to: group of buttons.
In–Cell saves the values in the two memories when you exit the calculator (don't forget to close In–Cell before quitting Excel), and the results will be there next time you start In–Cell. Values are stored by User, if separate user log–on profiles have been setup in Windows.
Back to Calculator Image
Memory value
The value in each memory is displayed in these boxes. The memory values are not editable, however there are several buttons that manipulate the memory values, as discussed in the section on memory functions.
When you start In–Cell, the memory values will contain the values from your last session. Values are stored by User, if separate user log–on profiles have been setup in Windows.
Back to Calculator Image
Sign change
The sign of both data inputs, both memories and the result can be reversed using the sign change button next to the appropriate value box.
Back to Calculator Image
Decimal / Integer / Whole number rotation
If the number in the adjacent input box has both an integer part and a decimal (fractional) part, then this button will rotate
through the two components (integer and decimal) and then back to the whole number.
This button can also be used to reverse decimal rounding,
as it will return the value in the input box to the value before rounding was applied.
Back to Calculator Image
Decimal Rounding
The decimal part of the value in the adjacent input box can be rounded. Each click of this button reduces the number of decimal places used, rounding the result at each step.
It appears that the VBA rounding function uses 'Bankers' rounding rather than normal mathematical rounding. In this blog about rounding
the bankers rounding is described as follows: The convention is to round to the nearest even number, so that both 1.5 and 2.5 round to 2, and 3.5 and 4.5 both round to 4.
In–Cell makes a small adjustment to the value before rounding to approximate to mathematical rounding, rather than bankers rounding
The adjustment added is 0.000000001
The Round function does not currently round the integer part of the number, i.e., rounding stops when there are no decimal places remaining.
To reverse rounding, use the D/I button – See Decimal / Integer / Whole number function.
Back to Calculator Image
Sum of Range
If a range of cells has been selected, the sum of all the cells containing numbers is placed in the adjacent input box.
Note that this function does not use, or change, In–Cell's Active Cell.
Back to Calculator Image
Copy from Active Cell
The Copy button copies the value in In–Cell's Active Cell, into the adjacent input box, replacing the current contents.
In–Cell's Active Cell is the cell outlined in red or other user–selected colour, not the cell selected in Excel, although they are often the same cell.
Back to Calculator Image
Backspace input characters
The backspace button erases the last digit showing in the adjacent Input box. Characters can be erased by clicking in the Input box and using the keyboard backspace or delete keys.
Back to Calculator Image
Standard operators
This group of buttons provide the standard multiply, divide, add and subtract functions.
The function selected is shown in the adjacent operator box. No action occurs until the equals button is hit.
If equals is hit, but no operator has been selected, a warning box opens, and no operation is performed (i.e., there is no default operator),
however, once an operator has been selected, it will be used until changed. The operator selected can always be seen in the adjacent operator box.
Back to Calculator Image
Equals Operator
The equals button carries out the calculation selected by the standard operators. Note that percentage functions are not dependant on the equals sign.
The adjacent current function box displays the equals signs or one of the percentage functions.
Back to Calculator Image
Current Function Display
The selected operator, Equals, Percentage, As percentage or Plus/minus percentage is displayed in this box. This displays the last calculation undertaken,
although if Equals is shown, then the current operator box shows the specific function, (x, /, + or – ).
Back to Calculator Image
Percentage Function
The Percentage function performs a simple percentage calculation. The result is Input 2's percentage of Input 1.
Example: Input #1 = 200, Input #2 = 8, Result = 16. The current operator box shows '%'.
Back to Calculator Image
As Percentage Function
The As percentage function calculates what percentage Input 2 is, of Input 1.
Example 1: Input # 1 = 200, Input #2 = 8, Result = 2500.
Example 2: Input # 1 = 8, Input #2 = 200, Result = 4. (i.e., eight is 4% of 200). The current operator box shows 'A%'.
Back to Calculator Image
Plus or Minus Percentage
The Plus or minus percentage function adds or subtracts Input 2's percentage of Input 1 to the value of Input 1.
Example 1: Plus percentage: Input # 1 = 200, Input #2 = 8, Result = 216. The current operator box shows '+%
Example 2: Minus Percentage: Input # 1 = 200, Input #2 = 8, Result = 184. The current operator box shows '–%'.
Back to Calculator Image
Active Cell Position and Move
The active cell address box shows the address of the cell designated as In–Cell's Active Cell. The associated spin buttons allow the Active Cell to be moved.
If more than a small move of the active cell is required, a new cell or group of cells can be selected on the spreadsheet followed by the Resample button.
Back to Calculator Image
When the 'No Scroll' check box is checked, as the Active Cell is moved, using the spin buttons, the spreadsheet display stays in a fixed position on the screen,
and the highlighted Active Cell moves across the display.
When not checked, the Active Cell appears fixed in position on the screen and the spreadsheet moves.
The actions vary slightly when the Active Cell reaches the limits of the spreadsheet, or the limits of the visible spreadsheet.
Checking or unchecking this box does not affect any of the calculator's functions. Try both options, to find which works best. It is likely that you will change between
the two options depending on the area of a spreadsheet being worked on.
Back to Calculator Image
Result display
The result box shows the result of the latest calculation, the value from a transfer into the result box, (see the To Result Box, From functions)
or the value from the Active Cell, in an exchange operation, (see the Exchange Active Cell with Result function).
The value displayed in the result box may appear different to the pasted value as the result box display can have the number of decimal places displayed truncated.
Hover the mouse over the Result box to see the current maximum number of decimal places displayed. The number of decimals displayed is user definable, and does not affect the accuracy of the result.
See Options for information on accessing the user definable features.
Back to Calculator Image
Move Results to Other Locations
These buttons move the value in the Result box to other locations: Inp 1 to Input #1, Inp 2 to Input #2, and CB to the Windows clipboard.
The value can be added to or subtracted from either memory using one of the four memory buttons, (M1+, M1–, M2+ and M2–).
Back to Calculator Image
Move Data from Other Locations to Result Box
These buttons move values into the Result box. This is usually done as a means of transferring a value to a spreadsheet cell, using the Paste Value button.
Values can be transferred from either Input box (Inp1 or Inp2), from either memory (M1 or M2), and from the Windows clipboard (CB), or the last result value can be retrieved.
The Last result button only holds one value at present, and it is not possible to roll–back through a series of previous results.
Back to Calculator Image
Paste Result to Active Cell
The Paste Value button pastes the value in the Result box to the Active Cell, (In–Cell's highlighted Active Cell).
Back to Calculator Image
Paste Result to Active Cell
The Paste Formula button pastes a formula for the result to the Active Cell, (In–Cell's highlighted Active Cell).
If the calculation was not directly linked to a cell or cells, then the values are substituted.
If the value in Input #1 has come from a cell by a pre–sampling, re–sampling, or Copy function, the cell reference is used, rather than the value.
The same applies to Input #2. If the cell value has been modified by use of the Decimal / Integer function, the formula pasted will include the appropriate functions.
Similarly, if Rounding has been applied, the pasted formula will include the appropriate rounding function.
Example: Input # 1 came from cell C10 which contained 8.198065, and Input #2 came from Cell D10 which contained 2, then the integer part of Input #1 was selected with the D/I button,
and the +% function was used, the result would be 8.16, i.e., 8 plus 2% of 8. Pasting the formula would display 8.16, but the cell would contain the formula '=INT(C10)+(INT(C10)/100*D10)'.
The Paste formula function will not use a cell value if a non–function change has been made, such as direct editing of a value in an input box, the use of the backspace character, or adding digits from one of the keypads.
If the cell address is not available, the value is used. In the example, if the value 2 from cell D10 had been changed to 2.1, the formula would be: '=INT(C10)+(INT(C10)/100*2.1)'.
The Paste formula function will only work for the first paste formula operation after switching spreadsheets or workbooks, i.e., the cell address WILL include the source spreadsheet / workbook for a single paste formula
operation after switching to the new spreadsheet / workbook. After that In–Cell bases all cell addresses on the new worksheet / workbook. (This limitation will be addressed in a future release).
Back to Calculator Image
Undo Paste
The Undo Paste button reverses the last Paste Value or Paste Formula action. The previous cell contents are replaced.
Note that this is a single undo function – ONLY the last paste function is reversed. Only one value is held in the Undo function. This may be changed in a future release.
Back to Calculator Image
Paste Date or Time to Active Cell
The current date or current time can be pasted directly into In–Cell's Active Cell using the appropriate button.
Note that the time button uses an integer value of zero, with the time as a decimal value. (12 midday is 0.5). The date paste function leaves the decimal part blank, i.e., zero, equivalent to a time of midnight.
The cells are formatted with a date or time format as appropriate. This function is to be changed in a future release, so that the date or time format applied is the user's Windows short date or time formats.
Back to Calculator Image
Resample data on spreadsheet
Using the Resample button results in the selected cell or range of cells being assessed and the first and last numeric values entered into Inputs 1 and 2 respectively. If a single cell is selected,
and it contains a numeric value, the value is entered into input 1. The method used is the same as that for the pre–select when In–cell is first called. If there is an empty cell after the last number–containing cell,
then this empty cell is selected as the Active Cell, ready to receive a calculation result. If there is no empty cell at the end of the range, then the last cell is selected as the active cell.
Resample can be used to select a cell containing multiple numbers, numbers written as text, or a date containing cell, ready for one of the special conversion or extraction routines.
Back to Calculator Image
Close and Exit the In–Cell Calculator
The Exit button first saves all the user selectable features such as text box colours and fonts, default scroll / no scroll setting, In–Cell's start–up position, certain special conversion and extract options, and the values in memory #1 and memory #2.
These values are saved in a file in the user's documents and settings directory, which allows each user to retain their own customization. After saving this file, In–Cell closes down. The highlighting around the Active Cell is removed.
If In–Cell is not closed down through the Exit button, the user settings are not retained, and the Active Cell highlight is not removed.
Back to Calculator Image
Exchange Functions for Inputs and Memories
The Input1 <> Input 2 button swaps the values in inputs 1 and 2.
The Memory1 <> Memory 2 button swaps the values in memories 1 and 2.
Back to Calculator Image
Active Cell and Result Value Exchange
The Active cell <> Res button swaps the value in the Active Cell with the value in the Result box. If the Active Cell does not contain
a numeric value, no exchange is undertaken.
Back to Calculator Image
Extract Numbers – Special Function
The Numbers button will extract multiple numbers from the contents of the Active Cell. The cell could contain multiple numbers as part of a comma delimited list,
or numbers with intervening text, such as 14 boxes @ $28 each, or a longer piece of text with numbers in it. The first number found is shown in the adjacent display box.
Repeatedly pressing the Display button will step through all the numbers found. The way that numbers are recognized depends on which option has been selected in the
Customize and options dialog. The main option selects whether commas are delimiters between numbers, or are being used as the thousands separator, (e.g., 1,000).
The default 'Auto' setting results in In–Cell attempting to identify numbers using both approaches. This typically results in more numbers being identified, as 101,202
will be recognized as 101202, i.e., the comma was a thousands separator, and as both 101 and 202 on the assumption that the comma was a delimiter, separating numbers.
Depending on the data being used, one or other of the specific settings may yield better results.
When a number is displayed in the box, use the Inp 1 or Inp 2 buttons to move the value to Input #1 or Input #2. The paste button pastes the value to the Active Cell.
After the initial recognition / extraction, the Active Cell is moved one cell in the default direction, ready for a paste operation. The default direction is set in the Customize and options dialog box.
If 'None' has been set as the default move, the Active Cell will not automatically advance.
Double clicking Paste will result in all the numbers found being pasted into a series of cells, in the default direction. If any of the cells contain data, a warning is displayed asking if you wish to proceed.
There is no Undo for multiple paste operations.
See the Special functions page for details of the multiple number extract function.
Back to Calculator Image
Convert numbers in Text to Numbers – Special Function
The Text to Numbers function can convert numbers written as text, to numbers. Multiple numbers embedded in text can be converted and extracted.
There is a wide range of ways in which numbers are written, particularly for larger numbers, and there are differences within English–speaking countries. This
makes this conversion less consistent. If numbers are written in a relatively standard way and are separated by other text or clearly separating words (e.g., plus, minus, times), conversion is
usually effective. The word 'and' is not taken as a separator between numbers, as it is regularly used within numbers in some English–speaking countries. The form
e.g., two hundred and ten.
When a number is displayed in the box, use the Inp 1 or Inp 2 buttons to move the value to Input #1 or Input #2. The paste button pastes the value to the Active Cell.
After the initial recognition / extraction, the Active Cell is moved one cell in the default direction, ready for a paste operation. The default direction is set in the Customize and options dialog box.
If 'None' has been set as the default move, the Active Cell will not automatically advance.
Double clicking Paste will result in all the numbers found being pasted into a series of cells, in the default direction. If any of the cells contain data, a warning is displayed asking if you wish to proceed.
There is no Undo for multiple paste operations.
See the Special functions page for details of the multiple number extract function.
Back to Calculator Image
Convert Text to Dates or a Date Number to a Date – Special Function
The Dates button will examine the contents of the Active cell for date information. If the cell only contains a single number, In–Cell treats it as a date number
and provides a conversion to text in the users default date format. The numeric values for day, month and year are provided, and if the number has a decimal component, the time is displayed in hours, minutes and seconds.
In–Cell handles dates prior to 01 January 1900, most of which are negative numbers. Dates back to the 1700's are handled. The special functions page has lots more detail on this.
If the cell contains text, groups of text representing consisting of three values are grouped and converted to dates, including dates that Excel does not recognize, such as 5th January 2007, or dates before 01 January 1900.
All the dates found can be examined by repeatedly hitting the Display button. For each date, In–Cell displays the date as text in the user's Windows date format, followed by the numbers for year, month, day,
then hour, minute and second, and finally the date number. For dates from 01 March 1900, the date number is the same as that used natively by Excel. Between 01 January 1900 and 01 March 1900, there is a difference
due to Excel's error in allocating a number to the non–existent 29 February 1900. If In–Cell finds 29 Feb 1900, it returns an error message. If multiple dates have been found, the Display button will run through
the same sequence of values for each subsequent date.
When a number is displayed in the box, use the Inp 1 or Inp2 buttons to move the value to Input #1 or Input #2. The paste button pastes the value to the Active Cell.
See the Special functions page for details of the multiple number extract function.
Back to Calculator Image
User selectable options and configuration
Clicking the Copyright bar at the very bottom of In–Cell opens up a new dialog box which allows a series of user–defined parameters and settings to be changed.
See the Customize and options page for details of the customizable features and user–definable options.
Back to Calculator Image
Section Hide or Display Bar
The bars above the three exchange function buttons and above the three special conversion / extraction groups can be clicked to hide the relevant button group.
Clicking the bars when the section is hidden redisplays the group of buttons. These options allow the space taken up by In–Cell on the screen to be reduced when these functions are not needed.
Note also that In–Cell can be minimized using the minimize button at top–right of the calculator. The minimized display stays within Excel's boundaries, in the bottom–left corner.
Click the full–size button to redisplay In–Cell.
Back to Calculator Image
Copyright © 2007 Humar Consulting Inc.
