VisualAnalysis Advanced Tutorials: Spreadsheet Results
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.
=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.
=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).
=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.
=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.
=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).
=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.
=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.