Spreadsheet

src: cdn.lifehack.org

spreadsheet is an interactive computer application for organization, analysis, and storage of data in tabular form. Spreadsheets were developed as computerized simulations of paper accounting worksheets. This program operates on data entered in table cells. Each cell can contain numerical or text data, or formula results that automatically calculate and display values ​​based on other cell contents. Spreadsheets can also refer to any of those electronic documents.

Spreadsheet users can adjust stored values ​​and observe their effect on calculated values. This makes the spreadsheet useful for "what-if" analysis because many cases can be investigated quickly without manual recounting. Modern spreadsheet software can have multiple sheets of interaction, and can display data as both text and numbers, or in graphical form.

In addition to performing basic arithmetic and mathematical functions, modern spreadsheets provide integrated functionality for financial operations and general statistics. Calculations such as current net worth or standard deviation can be applied to tabular data with functions already programmed in a formula. The spreadsheet program also provides conditional expressions, serves to convert between text and numbers, and functions that operate on text strings.

Spreadsheets have replaced paper-based systems around the business world. Although they were first developed for accounting or bookkeeping tasks, they are now used extensively in contexts where tabular lists are created, sorted, and shared.

LANPAR, available in 1969, is the first electronic spreadsheet on mainframe computers and time-sharing. LANPAR is an acronym: LANguage for Programming Arrays at Random. VisiCalc is the first electronic spreadsheet on microcomputers, and it helped turn the Apple II computer into a popular and widely used system. Lotus 1-2-3 is the leading spreadsheet when DOS is the dominant operating system. Excel now has the largest market share on Windows and Macintosh platforms. A spreadsheet program is a standard feature of office productivity suite; since the advent of web applications, office suites are now also in web application forms. Web-based spreadsheets, such as Microsoft Excel Online, Google Spreadsheets, Yahoo Sheets, and Zoho Sheets, are relatively new categories.

Video Spreadsheet

Usage

Spreadsheets consist of table cells arranged in rows and columns and referenced by X and Y locations. The location of X, the column, is usually represented by letters, "A", "B", "C", etc., while rows are usually represented by numbers, 1, 2, 3, etc. One cell can be called by pointing the row and column, "C10" for example. The concept of electronic reference cells was first introduced in LANPAR (The Language for Array Programming at Random) (co-created by Rene Pardo and Remy Landau) and the variant used in VisiCalc, and known as "A1 notation". In addition, spreadsheets have the range concept, a group of cells, usually adjacent. For example, one can refer to the first ten cells in the first column with the range "A1: A10". LANPAR innovates forward reference/natural order calculations that do not reappear until Lotus 123 and Microsoft MultiPlan Version 2.

In modern spreadsheet applications, multiple spreadsheets, often known as worksheets or just sheets , are collected together to form a workbook . The workbook is physically represented by a file, which contains all data for books, sheets and cells with sheets. Worksheets are usually represented by tabs that flip between pages, each containing one sheet, although Numbers alter this model significantly. Cells in multi-sheet books add sheet names to their references, for example, "Sheet 1! C10". Some systems extend this syntax to allow cell references to different workbooks.

Users interact with sheets primarily through cells. A given cell can store data by simply entering it into, or a formula, which is usually created by preceding the text with the same mark. The data may include text string hello world , number 5 or date 16-Des-91 . The formula will start with the same sign as, = 5 * 3 , but this is usually not visible because the screen shows the result calculation, 15 in this case, not the formula itself. This can cause confusion in some cases.

The main feature of the spreadsheet is the ability to formulas to refer to other cell contents, which in turn can be the result of formulas. To create such a formula, one simply replaces the number with the cell reference. For example, the formula = 5 * C10 will produce results multiplying the value in cell C10 with number 5. If C10 holds the value 3 the result will be 15 . But C10 may also hold its own formula which refers to other cells, and so on.

The ability to combine a common formula is what gives the spreadsheet the power. Many problems can be broken down into a series of individual math steps, and these can be assigned to individual formulas within the cell. Some of these formulas can apply to ranges too, such as the SUM function that adds all the numbers in the range.

Spreadsheets share many of the principles and characteristics of the database, but spreadsheets and databases are not the same thing. A spreadsheet is basically just one table, while a database is a collection of many tables with semantically machine-readable relationships between them. While it's true that a workbook that contains three sheets is indeed a file that contains multiple tables that can interact with each other, it does not have a relational structure of the database. Spreadsheets and databases can be operated - sheets can be imported into databases to become tables in them, and query databases can be exported to spreadsheets for further analysis.

A spreadsheet program is one of the main components of the office productivity suite, which usually also contains word processing, presentation programs, and database management systems. Programs in suite use similar commands for similar functions. Usually sharing data between components is easier than with a collection of functional programs that are not integrated. This is very beneficial when many personal computer systems use text and command display mode, not graphical user interfaces.

Maps Spreadsheet

History

Paper spreadsheet

The word "spreadsheet" comes from "spreading" in the sense of a newspaper or magazine item (text or graph) that includes two facing pages, stretches at the center of the fold and treats two pages as a single large page. The compound word "spread-sheet" means the format used to present the ledger - with the column for the expenditure category at the top, the invoice listed below the left margin, and the amount of each payment in the cell in which the lines and columns intersect - which, traditionally , "spread" on the page facing a bound ledger (book to keep accounting records) or on a large sheet of paper (called "analysis paper") ruled into rows and columns in that format and roughly twice as thick as plain paper.

Initial implementation

Batch maker spreadsheet report

The "spreadsheet" collection can not be distinguished from the batch compiler with additional input data, generating output reports, i , 4GL or conventional, non-interactive batch computer programs. However, the concept of electronic spreadsheets is described in the paper "Model Budgeting and System Simulation" in 1961 by Richard Mattessich. Subsequent work by Mattessich (1964a, Chpt. 9, Accounting and Analytical Methods ) and his companion volume, Mattessich (1964b, Company Simulation via Computer Budgeting Program ) applies a computerized spreadsheet to the system accounting and budgeting (on mainframe computers programmed in FORTRAN IV). The Spreadsheets collection primarily deals with adding or subtracting entire columns or rows (input variables) instead of individual cells .

In 1962, the concept of this spreadsheet, called BCL for Business Computer Languages, was implemented on IBM 1130 and in 1963 was ported to IBM 7040 by R. Brian Walsh at Marquette University, Wisconsin. This program was written in Fortran. Primitive timesharing is available on the machines. In 1968 BCL was ported by Walsh to the IBM 360/67 timesharing machine at Washington State University. It is used to assist in teaching finance for business students. Students can take the information prepared by the professor and manipulate it to represent and show the ratio etc. In 1964, a book entitled Business Computer Languages ​​ was written by Kimball, Stoffells and Walsh and both the book and the Program were copyrighted in 1966 and the year later the copyright was updated

Applied Data Sources have a FORTRAN preprocessor called Empires.

In the late 1960s Xerox used BCL to develop more sophisticated versions for their time sharing system.

LANPAR spreadsheet compiler

The main findings in the development of electronic spreadsheets were made by Rene K. Pardo and Remy Landau, who filed in 1970 US. Patent 4,398,249 in natural spreadsheet automatic order counting algorithm. While the patent was initially rejected by the patent office as a purely mathematical invention, after 12 years of appeals, Pardo and Landau won a court case at the Federal Circuit Intellectual Court (CCPA), overturned the Patent Office in 1983 - - stipulating that "something does not cease to be patented simply because the novelty point is in an algorithm. " However, in 1995, the US Court of Appeals for the Federal Circuit ruled that the patent had no legal force.

The actual software is called LANPAR3Ã, - LANguage for Array Programming in Random. It was conceived and fully developed in the summer of 1969, after Pardo and Landau's recent graduation from Harvard University. Co-inventor Rene Pardo recalls that he felt that a manager at Bell Canada did not have to rely on programmers to program and change the form of budgeting, and he thought of letting the user type in a form in any order and having an electronic computer compute the results in the correct order "Preliminary Forwarding/Natural Sequence Calculation"). Pardo and Landau developed and implemented the software in 1969.

LANPAR is used by Bell Canada, AT & amp; T and 18 telephone companies operating nationwide for their local and national budgeting operations. LANPAR is also used by General Motors. Its uniqueness is Pardo's joint discovery that incorporates forward reference/natural order calculation (one of the first "non-procedural" computer languages) as opposed to left-to-right, top-down to count results in each cell used. by VisiCalc, SuperCalc, and the first version of Multiplan. Without forward reference/natural sequence calculation, the user must manually recompile the spreadsheet as many times as necessary until the values ​​in all cells have stopped changing. Future reference/natural order calculations by the compiler are the necessary platform functions for each spreadsheet to be practical and successful.

The LANPAR system is implemented on GE400 and Honeywell 6000 online timesharing systems, which allow users to program remotely via terminals and computer modems. Data can be entered dynamically either by paper tape, a specific file access, on line, or even an external database. Sophisticated mathematical statements, including logical comparisons and "if/then" statements, can be used in any cell, and cells can be presented in any order.

Autoplan/Autotab spreadsheet programming languages ​​

In 1968, three former employees of the General Electric computer company headquartered in Phoenix, Arizona set out to start their own software development house. A. Leroy Ellison, Harry N. Cantrell, and Russell E. Edwards found themselves doing a large amount of calculations when creating tables for business plans they presented to venture capitalists. They decide to save themselves a lot of effort and write computer programs that generate their tables for them. The program, originally conceived as a simple utility for their personal use, will turn out to be the first software product offered by the company to be known as Capex Corporation. "AutoPlan" runs on the GE time sharing service; after that, the version running on the IBM mainframe was introduced under the name AutoTab . (National CSS offers a similar product, CSSTAB, which has a moderate time-sharing user base in the early 1970s.The main application is the tabulation of opinion research.)

AutoPlan/AutoTab is not a WYSIWYG interactive spreadsheet program, it is a simple scripting language for spreadsheets. Users specify names and labels for rows and columns, then formulas that define each row or column. In 1975, Autotab-II was advertised as an extension of the original document to a maximum of " 1,500 rows and columns, aggregated in whatever proportion the user needed... "

IBM Financial Planning and Control System

The IBM Financial Planning and Control System was developed in 1976, by Brian Ingham at IBM Canada. This is implemented by IBM in at least 30 countries. It runs on the IBM mainframe and is one of the first applications for financial planning developed with APL that actually hides the programming language of the end user. Through the IBM VM operating system, it is one of the first programs to update every copy of the app automatically when a new version is released. Users can define simple mathematical relationships between rows and between columns. Compared to contemporary alternatives, it can support a very large spreadsheet. It contains actual financial data taken from legacy batch systems to every user spreadsheet every month. It is designed to optimize the power of APL through the object kernel, increasing program efficiency by as much as 50-fold compared to traditional programming approaches.

modeling language APLDOT

The earliest "heavy industry" spreadsheet example was APLDOT, developed in 1976 at the United States Railway Association at IBM 360/91, run at the Johns Hopkins University Applied Physics Laboratory in Laurel, MD. This app has been successfully used for many years in developing applications such as financial models and fees for the US Congress and for Conrail. APLDOT is nicknamed "spreadsheets" because financial analysts and strategic planners use them to solve the same issues they talk about with paper spreadsheet pads.

VisiCalc

Because Dan Bricklin and Bob Frankston's VisiCalc implementations at Apple II in 1979 and IBM PCs in 1981, the concept of spreadsheets became widely known in the late 1970s and early 1980s. VisiCalc is the first spreadsheet to combine all the important features of modern spreadsheet applications (except for future reference/natural rule recalculation), such as the WYSIWYG interactive user interface, automatic recalculation, status and formula lines, copying coverage with relative and absolute reference, the formula by selecting the referenced cell. Not knowing about LANPAR when PC World magazine called VisiCalc as the first electronic spreadsheet.

Bricklin had talked about watching his university professor chart the calculations on the board. When the professor finds an error, he must delete and rewrite a number of sequential entries in the table, triggering Bricklin to think that he can replicate the process on the computer, using the whiteboard as a model to see the results of the underlying formula. The idea became VisiCalc, the first application that transformed personal computers from hobby to computer enthusiast into a business tool.

VisiCalc then becomes the first "killer application", a very interesting app, people will buy a particular computer just to use it. VisiCalc is not responsible for the success of Apple II. The program is then ported to a number of other early computers, especially CP/M machines, the Atari 8-bit family and various Commodore platforms. Nevertheless, VisiCalc remains known as the Apple II program.

SuperCalc

SuperCalc was a spreadsheet application published by Sorcim in 1980, and was initially bundled (along with WordStar) as part of the CP/M software package that came with portable Osborne 1. It quickly became the de facto standard spreadsheet for CP/M and ported to MS-DOS in 1982.

Lotus 1-2-3 and other MS-DOS spreadsheets

The acceptance of IBM PCs after its introduction in August 1981 began slowly, as most of the programs available to them were translations from other computer models. Everything changed dramatically with the introduction of Lotus 1-2-3 in November 1982, and was released for sale in January 1983. It was written specifically for the IBM PC, it had a good performance and became a killer app for this PC. Lotus 1-2-3 drives PC sales due to increased speed and graphics compared to VisiCalc on the Apple II.

Lotus 1-2-3, along with its competitor Borland Quattro, VisiCalc immediately evacuated. Lotus 1-2-3 was released on January 26, 1983, beginning to surpass the most popular VisiCalc in the same year, and for several years was a leading spreadsheet for DOS.

Microsoft Excel

Microsoft released the first version of Excel for the Macintosh on September 30, 1985, and then moved it to Windows, with the first version numbered 2.05 (to synchronize with Macintosh version 2.2) and released in November 1987. Windows 3.x platform in the early 1990s enabled Excel to taking market share from Lotus. When Lotus responds with a usable Windows product, Microsoft starts assembling their Office packages. In 1995, Excel was the market leader, edging out Lotus 1-2-3, and by 2013, IBM halted Lotus-1-2-3 altogether.

Web-based spreadsheets

With the advent of advanced web technologies such as Ajax around 2005, a new generation of online spreadsheets has emerged. Equipped with a rich user experience of Internet applications, the best web-based online spreadsheets have many features visible in desktop spreadsheet apps. Some of them like EditGrid, Google Spreadsheets, Microsoft Excel Online, Smartsheet, or Zoho Office Suite also have powerful multi-user collaboration features or offer real-time updates from remote sources like stock quotes and currency rates.

Other spreadsheets

Gnumeric is a free, cross-platform spreadsheet program that is part of the GNOME Free Software Desktop Project. OpenOffice.org Calc and the closely related LibreOffice Calc (using LGPL license) are free and open-source spreadsheets.

Current spreadsheet software:

• Calligra Sheets (formerly KCalc)
• Corel Quattro Pro (WordPerfect Office)
• Kingsoft Spreadsheets
• NeoOffice
• Figures are Apple Inc's spreadsheet software, part of iWork.
• Pyspread

Stopped spreadsheet software:

• 3D-Calc for ATari computer ST
• Framework by Forefront Corporation/Ashton-Tate (1983/84)
• GNU OleoÃ, - A traditional terminal mode spreadsheet for UNIX/UNIX-like systems
• IBM Lotus Symphony (2007)
• Javelin Software
• KCells
• Lotus Improv
• Lotus Jazz for Macintosh
• Lotus Symphony (1984)
• MultiPlan
• Claris Resolution (Macintosh)
• Complete One
• Borland Quattro Pro
• SIAG
• SuperCalc
• Q/Creator
• Calc Target Planner for CP/M and TRS-DOS
• Trapeze for Macintosh
• Wingz for Macintosh

Other products

A number of companies have tried to get into the spreadsheet market with programs based on a very different paradigm. Lotus introduced the possibility of the most successful example, Lotus Improv, which sees some commercial success, especially in the financial world where strong data mining capabilities remain respected to this day.

Spreadsheet 2000 tries to simplify the formula construction dramatically, but it generally does not work.

src: media.daysoftheyear.com

Drafts

The main concept is a cell grid, called a sheet, with raw data, called a value, or a formula in a cell. The formula says how to calculate a new value mechanically from an existing value. The value is generally a number, but can also be pure text, date, month, etc. Extensions of this concept include a logical spreadsheet. A variety of tools for programming sheets, visualizing data, connecting long-distance sheets, displaying cell dependencies, etc. are usually provided.

Cell

"Cells" can be considered as a grid to store data. A single cell is usually referenced by its columns and rows (A2 will represent cells containing the value 10 in the sample table below). Typically rows, representing the dependent variable, are referenced in decimal notation starting from 1, while columns representing independent variables using two-word numeric using A-Z as a number. Its physical size can usually be adjusted by its content by dragging the height or width at the junction box (or for an entire column or row by dragging column or row headings).

Cell arrays are called sheets or worksheets . This is analogous to the various variables in conventional computer programs (although certain values ​​that do not change, after entry, can be considered, by the same analogy, constants). In most implementations, multiple worksheets can be placed in a single spreadsheet. Worksheets are just part of a spreadsheet that is shared for clarity. Functionally, spreadsheets operate entirely and all cells operate as global variables in a spreadsheet (each variable has read access only except its own cell).

Cells can contain values ​​or formulas, or just be left blank. By convention, the formula usually starts with a = .

Value

Values ​​can be entered from the computer keyboard by typing directly into the cell itself. Alternatively, the value may be based on a formula (see below), which may perform calculations, display current date or time, or retrieve external data such as stock quotes or database values.

Spreadsheet Aturan Nilai

Computer scientist Alan Kay uses the term value rule to summarize the spreadsheet operation: the cell value depends entirely on the formula that the user types into the cell. The formula may depend on the value of other cells, but they are also limited to the data or formulas the user enters. There is no 'side effect' to calculate the formula: the only output is to display the calculated results inside the occupation cell. There is no natural mechanism for permanently modifying cell contents unless the user manually modifies cell contents. In the context of programming languages, this results in a finite form of first-order functional programming.

Automatic recount

A standard spreadsheet since the 1980s, this optional feature eliminates the need to manually request a spreadsheet program to recalculate the values ​​(currently usually the default option unless specifically 'turned off' for large spreadsheets, usually to improve performance). Some previous spreadsheets require manual requests to recalculate, because recounting large or complex spreadsheets often reduces data entry speeds. Many modern spreadsheets still retain this option.

Recalculation generally requires that there are no circular dependencies in the spreadsheet. The dependency graph is a graph that has a point for each object to be updated, and the edge connecting the two objects whenever one of them needs to be updated earlier than the other. Dependency graphs without the form of circular dependencies directed to acyclic graphs, partial order representations (in this case, across spreadsheets) that can be relied on to provide definite results.

Real-time updates

This feature refers to periodically updating cell content with values ​​from external sources - such as cells in "remote" spreadsheets. For sharing, Web-based spreadsheets, it applies to "soon" updating other user's cells has been updated. All dependent cells must be updated as well.

Locked cell

Once logged in, the selected cell (or the entire spreadsheet) can be optionally "locked" to prevent accidental override. Normally this will apply to cells containing formulas but may apply to cells containing "constants" such as the kilogram/pound conversion factor (2.20462262 to eight decimal places). Although individual cells are marked as locked, spreadsheet data is not protected until the feature is enabled in the file preferences.

Data format

A cell or range can be optionally specified to determine how the value is displayed. The default display format is usually set by its original content if not specifically specified, so for example "31/12/2007" or "31 Dec 2007" will default to cell format date . Similarly adding a% sign after a numeric value will mark the cell as a percentage cell format. The cell contents are not modified by this format, only the values ​​shown.

Some cell formats such as "numeric" or "currency" can also specify the number of decimal places.

This can allow unauthorized operations (such as multiplication of cells containing dates), resulting in illogical results without proper warning.

Cell formatting

Depending on the capabilities of the spreadsheet application, each cell (such as its "style" counterpart in a word processor) can be formatted separately using either content attributes (point size, color, bold or italic) or cell (border) thickness, background shadows, colors). To help spreadsheet readings, cell formatting can be applied conditional to data; for example, negative numbers can be displayed in red.

Cell formatting usually does not affect the content and depends on how the cell is referenced or copied to a worksheet or other application, formatting can not be done with the content.

Named cells

In most implementations, a cell, or group of cells in a column or row, can be "named" that allows the user to refer to those cells by name rather than by a grid reference. The name must be unique in the spreadsheet, but when using multiple sheets in a spreadsheet file, the range of cells named identically on each sheet can be used if differentiated by adding the name of the sheet. One reason for this use is to create or run macros that repeat commands on multiple sheets. Another reason is that formulas with named variables are readily checked against algebra that are meant to be applied (they resemble Fortran expressions). The use of named variables and named functions also make the spreadsheet structure more transparent.

Cell reference

In place of named cells, an alternative approach is to use a cell reference (or grid). Most cell references show another cell in the same spreadsheet, but the cell reference can also refer to cells in different sheets in the same spreadsheet, or (depending on the implementation) to the cell in another spreadsheet entirely, or to the value of the remote application.

The typical cell reference in style "A1" consists of one or two non-case sensitive letters to identify columns (if there are up to 256 columns: AZ and AA-IV) followed by line numbers (e.g., in the range 1-65536). One part can be relative (it changes when the formula is moved or copied), or absolute (indicated by $ in front of the corresponding part of the cell reference). The alternative "R1C1" reference style consists of the letter R, line number, letter C, and column number; the relative row or column number is indicated by attaching the number in square brackets. Most spreadsheets currently use A1 style, some provide R1C1 styles as compatibility options.

When a computer calculates a formula in one cell to update the displayed value of that cell, the cell reference (s) in that cell, naming some other cell (s), causes the computer to retrieve the value of a named cell (s).

Cells on the same "sheet" are usually referred to as:

 = A1  

Cells on different sheets of the same spreadsheet are typically designated as:

 = SHEET2! A1 (ie the first cell in sheet 2 of the same spreadsheet).  

Some spreadsheet implementations in Excel allow cell references to other spreadsheets (not currently active and open files) on the same computer or local network. It can also refer to cells in other open and active spreadsheets on the same computer or network that are defined as shareable. This reference contains full file names, such as:

 = 'C: \ Documents and Settings \ Username \ My spreadsheets \ [main sheet] Sheet1! A1  

In a spreadsheet, a reference to a cell will update automatically when new rows or columns are inserted or deleted. However, care must be taken when adding the row just before the total set of columns to ensure that the total reflects additional line values ​​- which are often not.

A circular reference occurs when the formula in one cell refers - directly, or indirectly through the cell reference chain - to another cell that refers back to the first cell. Many common errors cause circular references. However, some valid techniques use circular references. These techniques, after many recounts of the spreadsheet, (usually) meet at the correct value for those cells.

Cell range

Likewise, instead of using a named cell range, reference ranges can be used. Reference to the various cells is usually the form (A1: A6), which determines all cells in the range A1 through A6. Formulas like "= SUM (A1: A6)" will add all the specified cells and insert the result into the cell containing the formula itself.

Spreadsheets

In the earliest spreadsheet, the cell is a simple two-dimensional grid. Over time, the model has been expanded to include a third dimension, and in some cases a series of named grids, called sheets. The most sophisticated examples enable inversion and rotation operations that can slice and project the data set in various ways.

Formula

A formula identifies the calculations needed to place the results in the cells contained therein. Cells containing formulas therefore have two display components; the formula itself and the resulting value. The formula is usually only displayed when a cell is selected by "clicking" the mouse over a particular cell; otherwise it contains the calculation results.

The formula assigns values ​​to cells or ranges of cells, and usually has the format:

where the expression consists of:

• values, such as 2 , 9.14 or 6.67E-11 ;
• references to other cells, such as, for example, A1 for single cells or B1: B3 for ranges;
• arithmetic operators, such as , - , * , /, and others;
• relational operators, such as & gt; = , & lt; , and more; and,
• works, such as SUM () , TAN () , and many others.

When a cell contains a formula, it often contains references to other cells. Such cell references are a variable type. The value is the value of the referenced cell or some of its derivatives. If the cell in turn refers to another cell, its value depends on those values. Reference can be relative (eg, A1 , or B1: B3 ), absolute (eg $ A $ 1 , or $ B $ 1: $ B $ 3 ) or an absolute combination of row-or column-wise/relative (eg, $ A1 is column-wise absolute and A $ 1 is the line -a absolute bit).

The options available for valid formulas depend on a particular spreadsheet implementation, but, in general, most arithmetic operations and nested nested operations are complex enough to be performed by most of today's commercial spreadsheets. Modern implementations also offer functionality to access custom-build functionality, remote data, and applications.

Formulas can contain conditions (or layered conditions) - with or without actual calculations - and are sometimes used purely to identify and highlight errors . In the example below, it is assumed that the number of percentage columns (A1 through A6) is tested for validity and explicit messages are inserted into the adjacent right cell.

= IF (SUM (A1: A6) & gt; 100, "Over 100%", SUM (A1: A6))

Further examples:

= IF (AND (A1 & lt; "", B1 & lt; & gt; "", A1/B1, "") means that if both cells A1 and B1 are not & lt ; & gt; empty "", then share A1 by B1 and show, others show nothing.

= IF (AND (A1 & lt; & gt; "", B1 & lt; & gt; ""), IF (B1 & lt; & gt; 0, A1/B1, "Division by zero") , "") means that if cells A1 and B1 are not empty, and B1 is not zero, then divide A1 by B1, if B1 is zero, then show "Division by zero", and do not show anything if A1 and B1 are empty.

= IF (OR (A1 & lt; & gt; "", B1 & lt; & gt; ""), "Either event text A1 or B1", "") means to display text if either cell A1 or B1 is not empty.

The best way to construct conditional statements is a step by step compilation followed by testing and testing errors and refining codes.

A spreadsheet does not, in fact, have to contain a formula at all, in which case it can be considered only a set of data organized in rows and columns (databases) such as simple calendars, schedules or lists. Due to its ease of use, formatting capabilities and hyperlinks, many spreadsheets are used solely for this purpose.

Function

Spreadsheets typically contain a number of functions provided, such as arithmetic operations (eg, sum, averages and so on), trigonometric functions, statistical functions, and so on. In addition there are often provisions for user-defined functions . In Microsoft Excel, these functions are defined using Visual Basic for Applications in the provided Visual Basic editor, and those functions are automatically accessible on the worksheet. In addition, programs can be written that pull information from the worksheet, perform some calculations, and report the results back to the worksheet. In the picture, the name sq is user-defined, and the sq function is introduced using the Visual Basic editor that is included with Excel. Name Manager shows the spreadsheet definition of a variable named x & amp; y .

Subroutines

The function itself can not be written into a worksheet, but only by returning their evaluation. However, in Microsoft Excel, the subroutine can write the values ​​or text found in the subroutine directly into the spreadsheet. This number shows the Visual Basic code for the subroutine that reads each member of the column variable named x , calculates the square, and writes this value into the corresponding element of the column variable named y y column does not contain a formula because its value is calculated in the subroutine, not the spreadsheet, and is only written in.

Remote spreadsheet

Each time a reference is made to a cell or group of cells that is not in the current physical spreadsheet file, it is considered as accessing a "remote" spreadsheet. The referenced cell content can be accessed either in the first reference with manual or recent updates in the case of a web-based spreadsheet, as a value close to real time with a specified auto-refresh interval.

Diagram

Many spreadsheet applications allow charts, graphs, or histograms to be generated from specific cell groups that are dynamically recreated when the cell content changes. The resulting graph components can be embedded in the current sheet or added as separate objects.

Multi-dimensional spreadsheet

In the late 1980s and early 1990s, the first Javelin Software and Lotus Improv emerged. Unlike models in conventional spreadsheets, they use models built on objects called variables, not on data in report cells. This multi-dimensional spreadsheet allows viewing data and algorithms in various ways to self-document, including multiple synced views simultaneously. For example, Javelin users can move through the connection between variables on the diagram while looking at the roots and logical branches of each variable. This is an example of what might be his main contribution from the previous Javelin - the concept of traceability of user logic or model structure through its twelve views. A complex model can be dissected and understood by others who have no role in its creation.

In this program, a time series, or any variable, is the object itself, not a collection of cells that appear in rows or columns. Variables can have many attributes, including a complete awareness of their connection to all other variables, data references, and text and image notes. Calculations are performed on these objects, as opposed to the various cells, so adding two time series automatically aligns them in calendar time, or within a user-specified time frame. Data independent of the worksheet - variables, and therefore data, can not be destroyed by deleting rows, columns or entire worksheets. For example, January costs are deducted from January revenue, regardless of where or whether both appear on the worksheet. This allows the action to be used in the pivot table, except for the flexible manipulation of the report table, but one of the many capabilities supported by the variable. In addition, if costs are incurred per week and income per month, the program can properly allocate or interpolate. This object design enables variables and all models to reference each other with user-specified variable names, and to perform multidimensional and large analyzes, but consolidation is easy to edit.

Trapeze, a spreadsheet on a Mac, goes a step further and explicitly supports not only table columns, but also matrix operators.

Logical worksheet

Spreadsheets that have a formula language based on logical expressions, rather than arithmetic expressions known as logical spreadsheets. The spreadsheet can be used for deductive reasons about their cell values.

src: www.excelxlsx.com

Programming issues

Just as early programming languages ​​were designed to produce spreadsheet prints, the programming technique itself has evolved into process tables (also known as spreadsheets or matrices) from more efficient data on the computer itself.

End user development

Spreadsheets are popular end-user development tools. EUD shows activities or techniques in which people who are not professional developers create automated behaviors and complex data objects without significant knowledge of programming languages. Many people find it easier to do calculations in a spreadsheet than by writing equivalent sequential programs. This is because of some features of the spreadsheet.

• They use spatial relationships to determine the relationship of the program. Humans have a very growing intuition about space, and the dependence between items. Sequential programming usually requires a typing line after a line of text, which should be read slowly and carefully to be understood and changed.
• They are forgiving, allowing partial results and functions to work. One or more parts of a program may work correctly, even if other parts are not finished or damaged. This makes writing and debugging programs easier, and faster. Sequential programming usually requires each row and character of the program to be correct in order for the program to run. One mistake usually stops the entire program and prevents any results.
• Modern spreadsheets allow secondary notation. Programs can be annotated with colors, fonts, lines, etc. To provide visual cues about the meaning of elements in the program.
• Extensions that allow users to create new functions can provide functional language capabilities.
• Extensions that allow users to create and apply models from machine learning domains.
• Multipurpose spreadsheet. With their logic and boolean graphics capabilities, even electronic circuit design is possible.
• Spreadsheets can store relational data and spreadsheet formulas can express all SQL queries. There are query translators, which automatically generate spreadsheet implementations of SQL code.

Spreadsheet program

The spreadsheet program "" is designed to perform common calculation tasks using spatial relationships rather than time as the main organizing principles.

It is often easy to think of spreadsheets as mathematical charts, where nodes are spreadsheet cells, and the ends are references to other cells specified in the formula. This is often called the dependency graph of the spreadsheet. Intercellular references can take advantage of spatial concepts such as relative position and absolute position, as well as a named location, to make the spreadsheet formula easier to understand and manage.

Spreadsheets typically attempt to update cells automatically when the cell depends on changes. The earliest spreadsheets use simple tactics such as evaluating cells in a specific order, but modern spreadsheets count after the minimal recomputation sequence of the dependency graph. Then the spreadsheet also includes a limited ability to propagate values ​​in reverse, changing the value of the source so that certain answers are achieved in certain cells. Since the spreadsheet cell formula is generally irreversible, this technique is somewhat limited in value.

Many common concepts for sequential programming models have analogs in the world of spreadsheets. For example, consecutive models of indexed loops are usually represented as cell tables, with the same formula (usually just different where the cells they refer).

Spreadsheets have evolved to use scripting programming languages ​​such as VBA as a tool to extend beyond what makes the spreadsheet language easy.

src: i.ytimg.com

Disadvantages

While spreadsheets represent a major step forward in quantitative modeling, they have flaws. Their deficiencies include an unfriendly sense of alpha-numeric cell addresses.

• ClusterSeven's research demonstrates a major discrepancy in the way financial institutions and corporate entities understand, manage, and oversee their widespread land of frequent spreadsheets and unstructured financial data (including comma separated files (CSV) and Microsoft databases Access.). One study in early 2011 involving nearly 1,500 people in the UK found that 57% of spreadsheet users never received formal training on the spreadsheet packages they used. 72% said that there is no internal department that checks their spreadsheets for accuracy. Only 13% said that Internal Audit reviewed their spreadsheets, while only 1% received checks from their risk department.
• Spreadsheets have significant reliability issues. The study estimates that approximately 94% of the spreadsheets used in the field contain errors, and 5.2% of the cells in the unaudited spreadsheet contain errors.

Although the risk of high error is often associated with the author and the use of spreadsheets, specific steps can be taken to significantly improve control and reliability by structurally reducing the probability of error occurring at the source.

• The practical expressions of a spreadsheet can be restricted unless their modern features are used. Several factors contribute to this limitation. Implementing complex models on a cell-on-time basis requires dull attention to detail. The author has difficulty remembering the meaning of hundreds or thousands of cell addresses that appear in the formula.

This weakness is reduced by the use of named variables for cell use, and uses variables in formulas rather than cell location and cell-by-cell manipulation. Graphs can be used to indicate instantly how the results are changed by changes in parameter values. In fact, spreadsheets can be made invisible except for transparent user interfaces that ask for relevant feedback from users, display user-requested results, generate reports, and have a default error trap to request correct input.

• Similarly, formulas expressed in cell address form are difficult to keep straight and difficult to audit. Research shows that spreadsheet auditors who check numerical results and cell formulas find no more errors than auditors who only check numerical results. That's another reason to use variables and named formulas using named variables.
• Dimensional changes require major operations. When rows (or columns) are added or removed from the table, we must adjust the size of many downstream tables depending on the changed table. In the process, it is often necessary to move other cells around to make room for new columns or rows, and to adjust the graph data source. In large spreadsheets, this can be very time consuming.
• Adding or removing dimensions is very difficult, it usually has to start over. Spreadsheets as a paradigm really force one to decide the right dimension from the beginning of a person's spreadsheet creation, although it is often most natural to make this choice once someone's spreadsheet model has matured. The desire to add and remove dimensions also appears in parametric analysis and sensitivity.
• Collaboration in writing spreadsheet formulas can be difficult when the collaboration occurs at cell and cell address levels.

Other issues related to the spreadsheet include:

• Some sources recommend the use of special software rather than spreadsheets for some apps (budgeting, statistics)
• Many spreadsheet software products, such as Microsoft Excel (versions before 2007) and OpenOffice.org Calc (version before 2008), have a capacity limit of 65,536 rows with 256 columns (2 ¹⁶ and 2 < > 8 each). This can cause problems for people using very large datasets, and can result in data loss.
• Lack of audit and revision controls. It makes it difficult to determine who changes what and when. This can cause problems with regulatory compliance. Lack of revision control greatly increases the risk of error due to the inability to trace, isolate, and test changes made to documents.
• Lack of security. Spreadsheets do not control who can view and modify certain data. This, combined with the lack of audits above, can make it easier for someone to commit fraud.
• Since the structure is loose, it is easy for a person to introduce a mistake, either accidentally or intentionally, by entering information in the wrong place or expressing dependencies between cells (as in formula) incorrectly.
• The formula result (example "= A1 * B1") only applies to one cell (ie, cell formulas are actually in - in this case probably C1), though it can "extract" data from many other cells, real time and actual time. This means that in order to cause similar calculations on a cell array, an almost identical formula (but being in the cell's "output" itself) must be repeated for each row of the "input" array. This is different from the "formula" in a conventional computer program, which usually makes one calculation that applies to all inputs in turn. With the current spreadsheet, a forced repetition of this identical formula can have adverse consequences from a quality assurance standpoint and is often the cause of many spreadsheet errors. Some spreadsheets have an array formula to resolve this issue.
• Trying to manage the spreadsheet volumes that may exist in an organization without proper security, audit trail, accidental error recognition, and other items listed above can be overwhelming.

While there are innate and third-party tools for desktop spreadsheet apps that address some of these flaws, these awareness and usage are generally low. A good example of this is that 55% of Capital market professionals "do not know" how their spreadsheets are audited; only 6% invest in third-party solutions

src: images.standaloneinstaller.com

Spreadsheet Risk

Spreadsheet risk is the risk associated with deriving a materially incorrect value from a spreadsheet application that will be used in making related decisions (usually numerical based). Examples include asset valuation, financial account determination, drug dose calculation or load beam size for structural engineering. Risks may arise from entering false or false data values, from errors (or incorrect changes) in the spreadsheet logic or in the absence of relevant updates (for example, exchange rates not applicable). Some single-instance mistakes have exceeded US $ 1 billion. Because the risk of a spreadsheet is essentially related to the action (or inaction) of an individual it is defined as a sub-category of operational risk.

In its report on the trade loss of JPMorgan Chase 2012, the lack of control over spreadsheets used for important financial functions is referred to as a factor in a trade loss of more than six billion dollars reported as a result of derivative trading being poor.

Nevertheless, a study conducted by ClusterSeven revealed that about half (48%) of c-level executives and senior managers at the company reported annual revenues in excess of £ 50 million saying there was no usage control at all or manual processes were poorly applied during use spreadsheet at the company.

In 2013 Thomas Herndon, an economics graduate student at the University of Massachusetts Amherst found major coding errors in the spreadsheet used by economists Carmen Reinhart and Kenneth Rogoff in a highly influential journal article 2010. Reinhart and Rogoff's article is widely used as a justification for pushing Europe's 2010-2013 austerity program.

src: www.narutoct.com

See also

• Attribute value system
• Comparison of spreadsheet software
• Move and copy in spreadsheet
• List of spreadsheet software
• Model audit

src: i.imgur.com

References

src: i.ytimg.com

External links

• Spreadsheet Programming Articles about DevX
• comp.apps.spreadsheets FAQ by Russell Schulz
• Extending the Spreadsheet Concepts by Jocelyn Paine
• Spreadsheets in Curlie (based on DMOZ)
• SpreadsheetÃ, - Its First Computerization (1961-1964) by Richard Mattessich
• The history of CICS and the introduction of IBM 3270 by Bob Yelavich
• Autoplan & amp; Autotab Articles by Creative Karma
• Spreadsheets in Science

Source of the article : Wikipedia