Calculate Formula

[Janita Locklin]

Id like to first calculate an average of the "Amount of Cyandin" for each of the same "Sample Number" (goal to have a column that calculates what I've placed in the "Average of three injections" column.

It's not a moving average... I thought maybe a conditional statement in a formula would work? Or perhaps a formula that uses some kind of third column like the "ID Code" column as an identifier to tell it which rows to average?

Followup question - for my full data table I will have obviously more data with rows that have a different cultivar name but then same sample numbers and injection numbers as shown in my example. Will these formulas still work? Will I need to account for the Cultivar name to separate them out?

When a formula is applied to a given column, JMP simply takes the formula and applies it to row 1, then row 2 and so on to the last row in the table. Therefore, the below formula will give you the answers you want

Basically the formula calculates the mean for the column "Amount of Cyanidin (ng/kg)" and write it out to the row for the column. Because there is also a By column specified, "Sample Number" it calculates the mean for each level found for the "Sample Number" and then writes it out to the row for the new column.

But your requirement was to only display the mean value on the last row for each of the "Sample Numbers". To identify which rows those are, there needs to be something that programmatically can be evaluated. In this case, I chose to check to see if the value of "Sample Number" changes in the row after the current row being processed. So that changes the formula to

The If() clause is interpreted as: if the current value of "Sample Number" is not equal to the value of "Sample Number" in the row 1 row beyond the current row( Row() + 1 ), then do the calculation. If this comparison is not true, then it does nothing.

So this works great except for the last row in the data table. When JMP attempts to look at a row 1 row beyond the last row in the data table, it has no row to compare with, so it can not make a decision.

Here the additional clause is checking to see if the current row ,Row() , is equal to the number of rows in the data table, N Rows( Current data Table(). The second clause is separated by "" which indicates that if either the first clause "OR" the second clause is true then process the calculation.

Concerning what needs to change to be able to use this on your full data table. I do not know what you are referring to when you say, "cultivar". The requirements for the formulas to work properly are for the data to be sorted in order of the "Developmental Stage" and "Sample Number" and that "Amount of Cyanidin (ng/kg)" is the name of the column that the means are calculated on. If any of those columns have a change in name, then the new names need to be swapped out in the formulae.

Other option is to use If with Col Min and Col Max functions, but be careful if you were to use this as this might break in any of the future JMP versions due to usage of Row() with Col Max/Col Min (Make using formula expressions in Set Each Value and using expressions as first argument in Col stat... )

While you're looking, check for indirect references. They happen when you put a formula in cell A1, and it uses another formula in B1 that in turn refers back to cell A1. If this confuses you, imagine what it does to Excel.

You can move between cells in a circular reference by double-clicking the tracer arrow. The arrow indicates the cell that affects the value of the currently selected cell. You show the tracer arrow by selecting Formulas, and then select either Trace Precedents or Trace Dependents.

When you close the message, Excel displays either a zero or the last calculated value in the cell. And now you're probably saying, "Hang on, a last calculated value?" Yes. In some cases, a formula can run successfully before it tries to calculate itself. For example, a formula that uses the IF function may work until a user enters an argument (a piece of data the formula needs to run properly) that causes the formula to calculate itself. When that happens, Excel retains the value from the last successful calculation.

Important In many cases, if you create additional formulas that contain circular references, Excel won't display the warning message again. The following list shows some, but not all, the scenarios in which the warning message will appear:

Unless you're familiar with iterative calculations, you probably won't want to keep any circular references intact. If you do, you can enable iterative calculations, but you need to determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Excel stops calculating after 100 iterations, or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the maximum number of iterations and the amount of acceptable change.

To set the maximum number of times that Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.

In the Maximum Change box, type the smallest value required for iteration to continue. This is the smallest change in any calculated value. The smaller the number, the more precise the result and the more time that Excel needs to calculate a worksheet.

Hire Date Anniversary (date-type helper column): What you want here is to find the most recent instance of your hire month and day. So we'll construct a date value based on whether the hire month and day are greater than or less than the current month and day:

In English: If a date value built from the current year, the hire date month, and the hire date day is greater than today's date, build a date value from the current year minus 1, the hire date month, and the hire date day values; otherwise, build a date from the current year, the hire date month, and the hire date day values.

In English - If the Hire Date is in the same year as the current year, then subtract the hire date from today's date and divide by 7; otherwise subtract January 1 of the current year from Today's date and divide by 7.

My pleasure. Since you're new to Smartsheet, bookmark the links in my signature. The Functions list with links to all the function help pages, and the error messages page are my Smartsheet bible. Also, click on the plus sign + in the left panel on your Smartsheet home page, and search the Solution Center for a template called Smartsheet Formula Examples and download it to your account. It's a sheet with a bunch of examples of formulas that you can interact with and try things out.

I'm trying include a date range with counting the number of applicants within various depts, in certain date ranges, but it's saying incorrect argument set. =COUNTIFS(DISTINCT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], AND(@cell > DATE (2023, 9, 30), @cell

The Dynamic Replace tool is what you're looking for. It has an option that allows you to specify that the replacement value are expressions and will evaluate them. The only thing to watch for is the the tool will apply all the formulas to all the records in the data, with the end result being last formula applied to all the rows. If you have different formulas on each row, you'll have to wrap the tool in a batch macro that processes the data one row at a time.

The main program just sets up the data so that it can be processed one record at a time by the batch macro. Add a recordId field and pass this to the control parameter of the macro. Pass the main data to the data input.

The macro is where the magic happens. The first part, up to the filter, is what processes the records one at a time. Every time the macro loops, the Update Value tool changes the formula in the Filter tool to select only one record. On loop one, its Record 1, loop 2, Record 2, etc. The Create Result formula adds in a field to store the value(Result) and initializes it with 0. The is sent into the D(ata) input of the Dynamic Replace. The bottom branch builds the 3 columns that are needed for the replacement operation to work. The Select selects only the Formula column, then the next Formula tool adds a int32 field called Boolean field and sets it to -1(True). It also adds a field called FieldName which stores the name of the Result field that was created earlier. This is passed to R(eplace) input of the Dynamic replace. Note that the Dynamic Replace tool doesn't actually accept Boolean fields as the Boolean field. You have to use an integer field and initialize it to -1

I am attempting to find a solution to perform calculations based on 2 tables. It's difficult for me to explain, so I have screenshots to show what I am trying to accomplish. The final base table will contain 1,000s of combinations to perform calculations from.

The field I called "Add values" contains the values that need to be added together from the values inthe lookup table. The screenshot is a very small sample. The lookup table will contain about 100 lookup columns.

Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at *

if it is daily. then you just need to have a column that calculates time out - time in , - 8. (anything over 8hrs is OT)

if it is weekly, each day you need a column to calculate hours worked by subtracting time in from time out.

then you need a separate table to add the sum of each days hours for the week. any hours over 40 are OT.

However, there is a cell that is throwing an exception. That cell is calculating the average of a series of cells that come after it and reference cells in other sheets. I guess that when the first cell is trying to calculate the average, the cells after are not evaluated yet.

3a8082e126