# VisualAnalysis Advanced Tutorials: Spreadsheet Results

## Project Description:

The purpose of this tutorial is to demonstrate the use of the built in spreadsheet in the Report View. The spreadsheet report will help in the advanced post-processing of plate elements. A list of spreadsheet functions will also be referenced at the end of this tutorial.

## Modeling the Structure

Create a new Plane Frame. Create a 10 x 10 1ft square plate mesh. Make the plates 8 inches thick and 3000psi concrete. Pin the bottom nodes of the plate and your model should look like the picture below.

## Loading the Structure

Apply 10- 5 K nodal loads in the positive X direction, under the "Wind +X loads" Load case. Your model should look similar to the figure below:

Now, the model is ready to be analyzed.

## Analysis and Results

First check your model for errors and analyze the structure. Make sure to check the Load Case Manager to make sure "W +X" loads are included in analysis. You can click "Yes" for Analyze? under Service Cases in the Load Case Manager or select "Deflection Checks" for Load Combinations. For this project, we don't need any Building Code Combinations. After analysis, select the "W +X" load case in the Status Bar. You will see the deflected shape of the wall of plates.

Now, let's find the average stress across the middle of the Wall or at y = 5'. Select the plates just below the y = 5'. You will want to record the names of the Nodes that lie along the top line of the selected plates. For this particular model, they are: 12, 11, 28, 39, 50, 61, 72, 83, 94, 105, and 116. Your model should look similar to the figure below:

** Right | Click** and select "Report
Selected Plate". In the Report view, use the

**Report | Report Properties**command and change the Report Properties to report the Plate Global Stresses and Nodal Reactions.

Click on the Spreadsheet View check box in the Modify Tab while in the Report Viewer. Report spreadsheets can be copied and pasted into Excel spreadsheets for data extraction and manipulation. Your report spreadsheet should look similar to the figure below:

Data extraction and manipulation can also be performed in VisualAnalysis. The data in the grey cells of the spreadsheet can not be edited however calculations may be performed in the white cells. Below, the "VLOOKUP" function has been used to look up the Sigma X stresses at the previously recorded nodes and averaged them using an "AVG" function.

## Spreadsheet Function Reference

Below is a list of functions available in the VA spreadsheet report.

**Mathematical Functions**

=ABS(X) *The absolute value of
X.*

=ACOS(X) *The arc cosine of X.*

=ASIN(X) *The arc sine of X.*

=ATAN(X*) The 2-quadrant arc
tangent of X.*

=ATAN2(X, Y) *The 4-quadrant
arc tangent of Y/X.*

=CEIL(X) *The smallest integer
greater than or equal to X.*

=COS(X) *The cosine of X.*

=COSH(X) *The hyperbolic cosine
of X.*

=DEGREES(X) *Converts the angle
expressed in radians to degrees ( ).*

=DET(M) *The determinant of the
matrix range M, which must be a square matrix.*

=DOT(R1, R2) *The dot product
of the vectors R1 and R2.*

=EXP(X) *e raised to the X
power.*

=FACT(N) *The value of N!.*

=FLOOR(X) *The largest integer
less than or equal to X.*

=FRAC(X) *The fractional
portion of X.*

=GAMMA(X) *The value of the gamma
function evaluated at X.*

=GRAND *A 12th-degree binomial
approximation to a Gaussian random number with zero mean and unit variance.*

=INT(X) *The integer portion of
X.*

=LN(X*) The natural log (base
e) of X.*

=LNGAMMA(X) *The log base e of
the gamma function evaluated at X.*

=LOG(X) *The log base 10 of X.*

=LOG10(X) *The log base 10 of
X.*

=LOG2(X) *The log base 2 of X.*

=MOD(X, Y) *The remainder of
X/Y.*

=MODULUS(X, Y) *The modulus of
X/Y.*

=PI *The value of pi.*

=POLY(X, ...) *The value of an
Nth-degree polynomial in X.*

=PRODUCT(X, ...) *The product
of all the numeric values in the argument list.*

=RADIANS(X) *Converts the angle
expressed in degrees to radians ( ).*

=RAND *A uniform random number
on the interval (0,1).*

=ROUND(X, n) *X rounded to n
number of decimal places (0 to 15).*

=SIGMOID(X) The *value of the
sigmoid function.*

=SIN(X) *The sine of X.*

=SINH(X) *The hyperbolic sine
of X.*

=SQRT(X) *The positive square
root of X.*

=SUMPRODUCT(R1, R2) *The dot
product of the vectors R1 and R2, where R1 and R2 are of equal dimension.*

=TAN(X) *The tangent of X.*

=TANH(X) *The hyperbolic
tangent of X.*

=TRANSPOSE(M) *The transpose of
matrix M.*

=VECLEN(...) *The square root
of the sum of squares of its arguments.*

**Statistical Functions**

=AVG(...) *The average
(arithmetic mean) of its arguments.*

=CORR(R1, R2) *Pearson's
product-moment correlation coefficient for the paired data in ranges R1 and R2.*

=COUNT(...) *A count of its
non-blank arguments*.

=F(M, N, F) *The integral of
Snedecor's F-distribution with M and N degrees of freedom from minus infinity
to F.*

=ERF(L[, U]) *Error function
integrated between 0 and L; if U specified, between L and U.*

=ERFC(L) *Complementary error
function integrated between L and infinity.*

=FORECAST(...) *Predicted Y
values for given X.*

=FTEST(R1, R2) *The
significance level ( ) of the two-sided F-test on the variances of the data
specified by ranges R1 and R2.*

=GMEAN(...) *The geometric mean
of its arguments*.

=HMEAN(...) *The harmonic mean
of its arguments*.

=LARGE(R, N) *The Nth largest
value in range R.*

=MAX(...) *The maximum of its
arguments.*

=MEDIAN(...) *The median
(middle value) of the range R1.*

=MIN(...) *The minimum of its
arguments.*

=MODE(...) *The mode or most
frequently occurring value.*

=MSQ(...) *The mean of the
squares of its arguments*.

=PERCENTILE(R, N) *The value
from the range R that is at the Nth percentile in R.*

=PERCENTRANK(R, N) *The
percentile rank of the number N among the values in range R.*

=PERMUT(S, T) *The number of T
objects that can be chosen from the set S, where order is significant.*

=PTTEST(R1, R2) *The significance
level ( ) of the two-sided T-test for the paired samples contained in ranges R1
and R2.*

=QUARTILE(R, Q) *The quartile Q
of the data in range R.*

=RANK(E, R[, O]) *The rank of a
numeric argument E in the range R.*

=SSQ(...) *The sum of squares
of its arguments.*

=RMS(...) *The root of the mean
of squares of its arguments.*

=SMALL(R, N) *The Nth smallest
number in range R.*

=SSE(...) *The sum squared
error of its arguments. It is equivalent to =VAR(...) =COUNT(...).*

=STD(...) *The population
standard deviation (N weighting) of its arguments*.

=STDS(...) *The sample standard
deviation (N-1 weighting) of its arguments.*

=SUM(...) *The sum of its
arguments.*

=T(N, T) *The integral of
Student's T-distribution with N degrees of freedom from minus infinity to T.*

=TTEST(R, X) *The significance
level of the two-sided single population T-test for the population samples
contained in range R.*

=TTEST2EV(R1, R2) *The
significance level ( ) of the two-sided dual population T-test for ranges R1
and R2, where the population variances are equal.*

=TTEST2UV(R1, R2) *The
significance level ( ) of the two-sided dual population T-test for ranges R1
and R2, where the population variances are not equal.*

=VAR(...) *The sample variance
(N weighting) of its arguments.*

=VARS(...) *The sample variance
(N-1 weighting) of its arguments.*

=VSUM(...) *The visual sum of
its arguments, using precision and rounding of formatted cell values.*

**Conditional Statistical Functions**

=CAVG(..., C) *Conditional
average.*

=CCOUN(..., C) *Conditional
count.*

=CMAX(..., C) *Conditional
maximum.*

=CMIN(..., C*) Conditional
minimum.*

=CSTD(..., C) *Conditional
sample standard deviation (N weighting).*

=CSTDS(..., C) *Conditional
sample standard deviation (N-1 weighting).*

=CSUM(..., C) *Conditional sum.*

=CVAR(..., C) *Conditional
population variance (N weighting).*

=CVARS(..., C) *Conditional
population variance (N-1 weighting).*

**String Functions**

=CHAR(N) *The character
represented by the code N.*

=CLEAN(S) *The string formed by
removing all non-printing characters from the string S.*

=CODE(S*) The ASCII code for
the first character in string S.*

=EXACT(S1, S2) *Returns **true **(1) if
string S1 exactly matches string S2, otherwise returns 0.*

=FIND(S1, S2, N) *The index of
the first occurrence of S1 in S2.*

=HEXTONUM(S) *The numeric value
for the hexadecimal interpretation of S.*

=LEFT(S, N) *The string
composed of the leftmost N characters of S.*

=LENGTH(S) *The number of
characters in S.*

=LOWER(S) *S converted to lower
case.*

=MID(S, N1, N2) *The string of
length N2 that starts at position N1 in S.*

=NUMTOHEX(X) *The hexadecimal
representation of the integer portion of X.*

=PROPER(S) *The string S with
the first letter of each word capitalized.*

=REGEX(S1, S2) *Returns **true **(1) if
string S1 exactly matches string S2; otherwise returns **false **(0).
Allows "wildcard"' comparisons by interpreting S1 as a regular expression.*

=REPEAT(S, N) *The string S
repeated N times.*

=REPLACE(S1, N1, N2, S2) *The
string formed by replacing the N2 characters starting at position N1 in S1 with
string S2.*

=RIGHT(S, N) *The string
composed of the rightmost N characters of S.*

=STRCAT(...) *The concatenation
of all its arguments.*

=STRING(X, N) *The string
representing the numeric value of X, to N decimal places.*

=STRLEN(...) *The total length
of all strings in its arguments.*

=TRIM(S) *The string formed by
removing spaces from the string S.*

=UPPER(S) *The string S
converted to upper case.*

=VALUE(S) *The numeric value
represented by the string S; otherwise 0 if S does not represent a number.*

**Logic Functions**

=FALSE *The logical value 0.*

=FILEEXISTS(S) *1 if file S can
be opened for reading; otherwise 0.*

=IF(X, T, F) *The value of T if
X evaluates to 1, or F if X evaluates to 0.*

=ISERROR(X) *Returns 1 if X
"contains" an error, otherwise 0.*

=ISNUMBER(X) *1 if X is a
numeric value; otherwise 0.*

=ISSTRING(X) *1 if X is a string
value; otherwise 0.*

=TRUE *The logical value 1.*

=AND(...) *0* *if any
arguments are 0; 1 if all arguments are 1; otherwise -1.*

=NAND(...) *0 if all arguments
are 1; 1 if any arguments are 0; otherwise -1.*

=NOR(...) *0 if any arguments
are 1; 1 if all arguments are 0; otherwise -1.*

=NOT(X) *0 if X=1; 1 if X=0;
otherwise -1.*

=OR(...) *0 if all arguments
are 0; 1 if any arguments are 1; otherwise -1.*

=XOR(...) *-1 if any of the
arguments are not 0 or 1; otherwise 0 if the total number of arguments with the
value 1 is even; 1 if the total number of arguments with the value 1 is odd.*

**Financial Functions**

=COUPDAYBS(S, M, F[, B]) *The
number of days between the beginning of the coupon period to the settlement
date.*

=ACCRINT(I, Ft, S, R, P, F[, B]) *Accrued
interest for a security that pays periodic interest.*

=ACCRINTM(I, S, R, P[, B]) *Accrued
interest for a security that pays interest at maturity.*

=COUPDAYS(S, M, F[, B]) *The
number of days in the coupon period that the settlement date is in.*

=COUPDAYSNC(S, M, F[, B]) *The
number of days between the settlement date and the next coupon date.*

=COUPNCD(S, M, F[, B]) *The
next coupon date after the settlement date.*

=COUPNUM(S, M, F[, B]) *The
number of coupon payments between the settlement date and maturity date.*

=COUPPCD(S, M, F[, B]) *The
previous (most recent) coupon date before the settlement date.*

=CTERM(R, FV, PV) *The number
of compounding periods for an investment.*

=CUMIPMT(R, NP, PV, S, E, T) *The
cumulative interest on a loan between start period S and end period E.*

=CUMPRINC(R, NP, PV, S, E, T) *The
cumulative principal paid on a loan between start period S and end period E.*

=DB(C, S, L, P[, M]) *Fixed-declining
depreciation allowance.*

=DDB(C, S, L, N) *Double-declining
depreciation allowance.*

=DISC(S, M, P, R[, B]) *The discount
rate for a security.*

=DOLLARDE(FD, F) *Converts a
dollar amount expressed as a fraction form into a decimal form.*

=DOLLARFR(DD, F) *Converts a
dollar amount expressed as a decimal form into a fraction form.*

=DURATION(S, M, R, Y, F[, B]) *The
Macauley duration of a security assuming $100 face value.*

=EFFECT(NR, NP) *Returns the
effective annual interest rate.*

=FV(P, R, N) *Future value of
an annuity.*

=FVSCHEDULE(P, S*) The future
value of an initial investment after compounding a series of interest rates.*

=INTRATE(S, M, I, R[, B]) *The
interest rate for a fully invested security.*

=IPMT(R, P, NP, PV, FV[, T]) *The
interest payment for a specific period for an investment based on periodic,
constant payments, and a constant interest rate.*

=IRR(G, F) *The internal rate
of return on an investment. (See also =XIRR and =MIRR.)*

=MDURATION(S, M, R, Y, F[, B]) *The
modified Macauley duration of a security assuming $100 face value.*

=MIRR(CF, FR, RR) *The modified
internal rate of return for a series of periodic cash flows.*

=NOMINAL(ER, NP) *The nominal
annual interest rate.*

=ODDFPRICE(S, M, I, FC, R, Y, RD,
F[, B]) *The price per $100 face value of a security with an odd (short or
long) first period.*

=ODDFYIELD(S, M, I, FC, R, PR,
RD, F[, B]) *The yield per of a security with an odd (short or long) first
period.*

=PMT(PV, R, N) *The periodic
payment for a loan.*

=PPMT(R, P, NP, PV, FV, T) *The
payment on the principal for a specific period for an investment based on
periodic, constant payments, and a constant interest rate.*

=PRICE(S, M, R, Y, RD, F[, B]) *The
price per $100 face value of a security that pays periodic interest.*

=PRICEDISC(S, M, D, RD[, B]) *The
price per $100 face value of a discounted security.*

=PRICEMAT(S, M, I, R, Y[, B]) *The
price per $100 face value of a security that pays interest at maturity.*

=PV(P, R, N) *The present value
of an annuity*

=RATE(FV, PV, N) *The interest
rate required to reach future value FV.*

=RECEIVED(S, M, I, D, [, B]) *The
amount received at maturity for a fully vested security.*

=SLN(C, S, L) *The straight-line
depreciation allowance.*

=SYD(C, S, L, N) *The
"sum-of-years-digits" depreciation allowance.*

=TBILLEQ(S, M, D) *The
bond-equivalent yield (BEY) for a Treasury Bill.*

=TBILLYIELD(S, M, D) *The yield
on a Treasury bill.*

=TERM(P, R, FV) *The number of payment
periods for an investment.*

=VDB(C, S, L, S, E) *Fixed-declining
depreciation allowance between two periods.*

=XIRR(G, V, D) *Internal rate
of return for a series of cash flows with variable intervals.*

=XNPV(R, V, D) *Returns the net
present value for a series of cash flows with variable intervals*.

=YIELD(S, M, R, PR, RD, F[, B]) *Yield
of a security that pays periodic interest.*

=YIELDMAT(S, M, I, R, PR[, B]) *Annual
yield of a security which pays interest at maturity.*

**Date and Time Functions**

=DATE(Y, M, D) *The date value
for year Y, month M, and day D.*

=DATEVALUE(S) *The
corresponding date value for a given string S.*

=DAYS360(S, E) *The number of
days between two dates, based on a 30/360 day count system.*

=DAY(DT) *The day number in the
date/time value DT.*

=EDATE(S, M) *The date/time
value representing number of months (M) before or after start date (S).*

=EOMONTH(S, M) *The date/time
value representing the last day of the month M months after S, if M is
positive, or M months before if M is negative.*

=HOUR(DT) *The hour value
(0-23) of date/time value DT.*

=MINUTE(DT) *The minute value
(0-59) of date/time value DT.*

=MONTH(DT) *The number of the
month in date/time value DT.*

=NETWORKDAYS(S, E[, H]) *The
number of whole working days, starting at S and going to E, excluding weekends
and holidays.*

=NOW *The date/time value of
the current system date and time.*

=SECOND(DT) *The second's value
(0-59) of the date/time value DT.*

=TIME(H, M, S) *The time value
for hour H, minute M, and second S.*

=TIMEVALUE(S) *The
corresponding time value for a given string value S.*

=TODAY *The date value of the
current system date.*

=WEEKDAY(D) *The integer
representing the day of the week on which the day D falls. 1 is Sunday, 7 is
Saturday.*

=WORKDAY(S, D[, H]) *The day
that is D working days after S, if D is positive, or before S, if D is
negative, excluding weekends and all holidays specified as dates in range H.*

=YEAR(DT) *The year value of
date/time value DT.*

=YEARFRAC(S, E[, B]) *The
portion of the year represented by the number of days between start date (S)
and end date (E).*

**Miscellaneous Functions**

=CELLREF(N1, N2) *A reference
to the cell in column N1 and row N2.*

=CHOOSE(N, ...) *The Nth
argument from the list.*

=COL(C) *The column address of
the cell referenced by C.*

=COLS(R) *The number of columns
in the specified range R.*

=HLOOKUP(X, S, R) *The value of
the cell in range S that is R number of rows beneath X.*

=INIT(X1, X2) *The first
argument on the first recalculation pass and the second argument on all
subsequent recalculation passes when Objective Grid for Microsoft. .NET. is
performing iterative calculations.*

=INTERP2D(R1, R2, N) *The
interpolation value for a 2-dimensional vector.*

=INTERP3D(R, X, Y) *The
interpolation value for a 3-dimensional vector.*

=MATCH(V, R[, T]) *The relative
position in range R of value V based on positioning criteria T.*

=N(R) *The numeric value of the
top left cell in range R.*

=RANGEREF(N1, N2, N3, N4) *A
reference to the range defined by coordinates N1 through N4.*

=ROW(C) *The row address of the
cell referenced by C.*

=ROWS(R) *The number of rows in
the specified range R.*

=S(R) *The string value of the
top left cell in range R.*

=VLOOKUP(X, S, C) *The value of
the cell in range S that is C number of columns to the right of X.*

**NOTESome Objective Grid for
Microsoft. .NET. functions return a result that is a range or cell reference.
Objective Grid for Microsoft. .NET. does not include these indirect references
in determining the pattern of recalculation. Plan carefully before using these
functions.**

**Embedded Tools**

=DFT(R) *The Discrete Fourier
Transform of the range R.*

=EIGEN(M) *The eigenvalues of
the matrix M.*

=FFT(R) *The Discrete Fourier
Transform of the range R using a fast Fourier Transform algorithm.*

=FREQUENCY(R, B) *Returns a
frequency distribution for values R with a set of intervals B.*

=INVDFT(R) *The inverse of the
Discrete Fourier Transform of the range R.*

=INVERT(M) *The inverse of
matrix M.*

=INVFFT(R) *The inverse of the
Discrete Fourier Transform of the range R using a fast Fourier Transform
algorithm.*

=LINFIT(X, Y*) The straight
line least squares fit. This function is equivalent to =POLYFIT(X, Y, 1).*

=LLS(A, Y) *The linear least
squares solution X to the overdetermined system of equations AX=Y.*

=MMUL(M1, M2) *The product of
multiplying matrix M2 by matrix M1.*

=PLS(X, Y, d) *Analyzes the
least squares polynomial model Y=P(X), where P is a polynomial of degree d.*

=POLYCOEF(X, Y, d) *The least
squares coefficients for the polynomial fit Y=P(X), where P is a polynomial of
degree d.*

=TRANSPOSE(M) *The transpose of
matrix M.*

=TREND(NX, KX, KY) *The y
values for new x values given existing x and y values.*

**NOTEEmbedded tools should not be
contained within other functions or arithmetic operations in a single formula.
For example, the formula =INVERT(=MMUL(A1..C4,F1..I3)) is not allowed. You may,
however, copy, move and format embedded tools just as any other function.**