Not sure if Grammarly Business, or ASAP Utilities for Excel is the better choice for your needs? Check Capterra’s comparison, take a look at features, product details, pricing, and read verified user reviews. Still uncertain? Check out and compare more Productivity products.
Grammar
The work on the Excel grammar here is an extension of my HonoursProject and is mainly targeted at the structure of the Excel sheets (cells, formula references, etc). It is currently a work in progress and corrections/additions are welcomed (see http://www.mcs.vuw.ac.nz/~elvis/db/contacts.html).
- Invesitgation into Excel Syntax and a Formula Grammar Grammar The work on the Excel grammar here is an extension of my HonoursProject and is mainly targeted at the structure of the Excel sheets (cells, formula references, etc).
- Grammarly drives lasting language editing locomotion. Grammarly services are available online and offline. Choose either the free or paid subscription version. The free version is essentially a basic grammar and spell checker. Although, with 150 types of grammar checks in its arsenal, it is an exceptional one.
BNF Example
To parse a formula string into a List of tokens in [RPN] order something like the following would be used. (Inspired by 'Lets Build a Compiler', by Jack Crenshaw and extracted from source code of [POI>HSSF].)
Formula
Absolute/Relative? references
Absolute Column, Absolute Row, $A$1, is an absolute cell reference
Relative Column, Absolute Row, A$1, is a mixed cell reference
Absolute Column, Relative Row, $A1, is a mixed cell reference
Relative Column, Relative Row, A1, is a relative cell reference (default style)
Pressing the F4 key in Excel, while typing the cell address, will cycle it through the four variations.
R1C1 references
[Ref]. To toggle between A1 and R1C1 style referencing Tools > Options > General tab > R1C1 reference style.
In R1C1 style, Microsoft Excel indicates the location of a cell with an 'R' followed by a row number and a 'C' followed by a column number. For example, the absolute cell reference R1C1 is equivalent to the absolute reference $A$1 in A1 reference style. If the active cell is A1, the relative cell reference R[1]C[1] refers to the cell one row down and one column to the right, or B2.
The following are examples of references in R1C1 style.
ReferenceMeaning
AreaReference? operators
Operators
(Based on [WSFF] and help files from [EXCEL])
Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.
Operator Precedence
Based on [Excel Tutorial - SC 2000 - Basic Math] ([local]) and [Microsoft support].
When you combine several operators into a single formula, Microsoft Excel performs the operations in the following order:
- : Range RangeOperator?
- space Intersection IntersectionOperator?
- , Union UnionOperator?
- - Negation NegationOperator?
- % Percentage PercentageOperator?
- ^ Exponentiation ExponentiationOperator?
- * Multiplication or / Division ExpressionOperator?
- + Addition or - Subtraction TermOperator?
- & Text Operator ConcatinationOperator?
- = < > <= >= <> Comparison Operators ComparrisonOperator?
In Excel 97 Help: operators, evaluation order in formulas 'The order in which Microsoft Excel performs operations in formulas' topic: operators, precedence...
Primitives
Functions
To get list of functions in catagory. Find in help for 'about function'. Cut and paste list of functions to Word and do replace using ^wworksheet function^p with |
Then do replace using ^t with nothing
[Markus Clermont's] Grammar
Example Formulas
- =1
- =1+1
- =A1
- =$B$2
- =SUM(B5:B15)
- =SUM(B5:B15,D5:D15)
- =SUM(B5:B15 A7:D7)
- =SUM(sheet1!$A$1:$B$2)
- =[data.xls]sheet1!$A$1
- =SUM((A:A1:1))
- =SUM((A:A,1:1))
- =SUM((A:AA1:B1))
- The Function SUM, with a Intersection between a Column and a Range as the argument.
- =SUM((D9:D11,(E9:E11,F9:F11)))
- The Function SUM, with a Union between a Range and a smaller Union as the argument.
- =IF(P5=1.0,'NA',IF(P5=2.0,'A',IF(P5=3.0,'B',IF(P5=4.0,'C',IF(P5=5.0,'D',IF(P5=6.0,'E',IF(P5=7.0,'F',IF(P5=8.0,'G'))))))))
- From an actual spreadsheet.
- ={SUM(B2:D2*B3:D3)}
- This is an ArrayFormula?
Quotes
'Although the multidensional spreadsheet has attracted a lot of interest since it empowers the end user to analyze business data, this has not replaced traditional analysis by means of a managed query environment.' - [COMP442:ODWOT]
Help Stuff from XP
You can use Auto Fill Options to choose options for how to fill the selection. For example, you can choose to Fill Formatting Only or Fill Without Formatting.
Enable Grammarly For Excel
If a cell contains a formula that breaks one of the rules, a triangle appears in the top-left corner of the cell. You can prevent these indicators from being displayed. Clicking in these cells will display the trace error icon.
Error detection rules
Formula omits cells in region
The formula may not include a correct reference. If a formula refers to a range of cells, and you add cells to the bottom or right of that range, the references may no longer be correct. The formula does not always automatically update its reference to include the new cells. This rule compares the reference in a formula against adjacent cells. If the adjacent cells contain more numbers (are not blank cells), then the problem is noted.
For example, the formula =SUM(A2:A4) would be noted with this rule, because A6, A7, and A8 are adjacent, and contain data.
Errors, Checkers, Auditors
[FishBrain::SpreadsheetAuditing]
(From http://www.faqs.org/faqs/spreadsheets/faq/)
Grammarly For Word And Excel
Auditor tools and books
Professional's Guide to Robust Spreadsheets http://www.manning.com/Richardson/http://www.squ.edu.om/agr/OnlineCourses/Excel/Formul2.html
Notes and stuff
Download Grammarly For Excel
Google discussion group: http://groups.google.com/groups?group=comp.apps.spreadsheets
'The shortcut key to cell names is Ctrl + F3.' [OOPS] To make Excel calculate a circular reference, set iteration to on. Use Tools|Options|Calculation|Iteration. Also set the Maximum Iterations number to 50 or more.
UML Spreadsheet Strucutre
Links
Consider using a parser generator [[JavaCC]] or [JLex] to automatically create code directly from the definition.