String search
Word search

Create a formula

By default, a number of formulas are available. You can define custom formulas in the columns next to the default formulas up to and including column Z. Each column can contain one formula. Each column contains all information for one formula:

  • The name and the calculated result of the formula.

    The result of the formula is shown in row 3.

  • The calculation for the formula.

    The formula can use one of the default calculations, or a custom calculation.

  • The calculation collects its values from the concerning column in the result table.

  • The result table collects the values for all job ticket items from the concerning column in the job ticket table.

  • The job ticket table receives its values from the current job ticket definition.

NOTE

It is recommended that you do not remove the default formulas.

Before you begin

The required [Job ticket items] for the new formula must be available.

About this task

In this procedure a complete new formula is created in column Z of the worksheet "CostSheet". The new formula is based on two [Job ticket items]: [Copies] and [Cover]. In this example, the data of the formula in column E is used as starting point for the formula in column Z.

Procedure

  1. Fill column Z with data from an existing formula. In this example, the data of the formula in column E is used as starting point for the formula in column Z.
    1. Click the column label "E" to select all cells of column E.
    2. To copy the selected cells, click "Edit - Copy", or press CTRL+C.
    3. Click the column label "Z".
    4. Click "Edit - Paste", or press CTRL+V.

      The formulas and formatting of column E are copied into column Z. The cell references are automatically adjusted.

  2. Define the name of the new formula in cell Z1. In this example: "Job cost".

    The formula names in row 1 are displayed in the [Product and order editor].

  3. Define the name of the new formula in cell Z2. In this example: "Job cost".

    The formula names in row 2 are displayed in the web shop.

  4. Define the price for the [Item values] of the [Job ticket items] in column Z.

    Optionally, you can define a custom name for the new pricing column. In this example: "Job cost".

  5. Check the formulas for [Copies] and [Cover] in the job ticket table. The formulas must use the values from column [Amount] and the values from the new pricing column "Job cost". The [Amount] column receives its values directly from the submitted job tickets. The value in column [Amount] is multiplied with the price in the pricing column. The total price of a job ticket item is the sum of the prices of its [Item values].
    1. Collect the price of [Copies] in cell Z78: =$C77*Z77.
    2. Collect the prices for all [Item values] of [Cover] in cell Z141: =$C137*Z137+$C138*Z138+$C139*Z139+$C140*Z140.
  6. Collect the prices for all [Item values] in the result table.

    The result table collects the values for all job ticket items from the concerning column in the job ticket table.

    1. Collect the price of [Copies] in cell Z13: =Z78.
    2. Collect the price of [Cover] in cell Z26: =Z141.
  7. In this example, create a new calculation in cell Z7 of row 7: ONLY B&W. Add up the prices for [Copies] and [Cover].

    It is recommended to use the IF function for each new calculation: =IF ( logical_test, value_if_true, value_if_false ). If the logical_test is true, the job cost is calculated. Else, the calculation returns a default value. In this example: 9999.99.

    NOTE

    You can use the functions of most categories in Microsoft Office Excel to create a calculation. You cannot use the functions of category "Database".

    1. Add up the prices of [Copies] and [Cover] in cell Z7: =IF((Z13+Z26)>0,SUM(Z13, Z26),9999.99).
    2. If required, you can reduce the total price when the customer orders a certain amount of [Copies]: =IF((Z13+Z26)>0,SUM(Z13, Z26)*VLOOKUP(Z13,CopiesTable,2,TRUE),9999.99).

      The reduction of the price for a certain amount of [Copies] is taken from the [Copies table] in the "Utilities" worksheet.

      NOTE

      The formula syntax and usage of the VLOOKUP function is described in the Microsoft Excel help file.

  8. Collect the result for the formula in cell Z3: =Z7.

    The value in Z3 is returned to the customer.

What to do next

You must test each new formula for correctness.

Parent topic:
Working with the formulas
Privacy Terms of use Cookie information