You can use Four arithmetic operations (+, -, ×, ÷) formulas, functions such as SUM and AVERAGE, and formulas that reference other sheets in Excel in KANNA reports.
If you cannot import Excel files, please refer to this page.
*If you want to use arithmetic operations and functions in KANNA, you need to set the formulas in Excel first.
What is a function? ▶︎ A special formula provided in Excel to perform calculations automatically.
What is “Referencing other sheets”? ▶ To automatically reflect the contents entered in one sheet to other sheets in the same Excel file.
- Four arithmetic operations / function
- See other sheets
- Reference other sheets
- Notes (1)
- Notes (2)
- Notes (3)
Four arithmetic operations / SUM Functions
The following arithmetic operations/functions can be used on KANNA.
Regarding functions in particular, only the functions listed in this article can be used at this stage.
Four arithmetic operations
・Addition
①= Cell 1 + Cell 2
②=SUM (Cell1, Cell2)
・Subtraction
=Cell 1 - Cell 2
・Multiplication
= cell1*cell2
・Division
= cell1/cell2
Functions
・SUM Function
The SUM function is used to add up all the numbers in a specified range of cells.
《Formula》
=SUM(Cell1, Cell2)
This formula displays the total value of the numbers within the specified range of cells (for example, from "Cell1" to "Cell2").
・IF Function
The IF function is used to automatically display a result based on an initial condition, following the logic of "If XX, then △△; otherwise, □□."
《Formula》
=IF(Cell1 > 60, "Pass", "Fail")
This formula displays "Pass" if the value in "Cell1" is greater than 60. Conversely, if the value in "Cell1" is 60 or less, it displays "Fail."
・AND Function
The AND function checks whether multiple conditions are all met.
《Formula》
=AND(Cell1 > 50, Cell2 < 40)
This formula displays TRUE if "Cell1" is greater than 50 AND "Cell2" is less than 40. If either of these conditions is not met, it displays FALSE.
・AVERAGE Function
The AVERAGE function is used to calculate the average of numbers in a specified range. For example, you can use it to find the average of numbers in a group of selected cells.
Note: Be aware that the value 0 is included in the average calculation.
《Formula》
=AVERAGE(Cell1, Cell2)
This formula displays the average of the numbers within the specified range of cells (in this case, from "Cell1" to "Cell2"). Please note that if the range includes a 0, it affects the average value.
・ROUND Function
The ROUND function is used to round a cell's value. The number to the right of the comma "," in the ROUND function specifies the decimal place to which you want to round.
If the number to the right of the comma "," is a positive number, it rounds to that decimal place.
If the number to the right of the comma "," is a negative number, it rounds the integer part (before the decimal point).
《Formula》
=ROUND(123.45, 1)
The red text specifies the number of decimal places to display.
The blue text is the value to be rounded.
This formula rounds the value to the first decimal place and display "123.5."
・ISBLANK Function
The ISBLANK function is used to check if a specified cell is empty. "Empty" means that nothing has been entered into the cell.
《Formula》
=ISBLANK(Cell1)
This formula displays TRUE if "Cell1" is empty. If any value (text, number, etc.) has been entered into the cell, it displays FALSE.
・MAX Function
The MAX function is used to find the largest value within a specified range of cells. When you specify a range of numbers, it returns the maximum value within that range.
《Formula》
=MAX(Cell1, Cell2, Cell3)
For example, if "Cell1" contains 4, "Cell2" contains 3, and "Cell3" contains 6, this formula will identify "Cell3's 6" as the maximum value and display 6.
You can also specify a range, such as =MAX(Cell1:Cell3), to find the maximum value within the range from Cell1 to Cell3.
・MIN Function
The MIN function is used to find the smallest value within a specified range of cells. When you specify a range of numbers, it returns the minimum value within that range.
《Formula》
=MIN(Cell1, Cell2, Cell3)
For example, if "Cell1" contains 4, "Cell2" contains 3, and "Cell3" contains 6, this formula will identify "Cell2's 3" as the minimum value and display 3.
You can also specify a range, such as =MIN(Cell1:Cell3), to find the minimum value within the range from Cell1 to Cell3.
・COUNT Function
The COUNT function is a function that counts the number of cells containing numerical data within a specified cell range. Empty cells or non-numerical data (like text) are not counted.
《Formula》
=COUNT(Cell1, Cell2)
For example, if "Cell1" contains 5 and "Cell2" contains 3, the value displayed will be "2." This is because there are two cells with numerical data.
(１) Set up formulas in Excel
This time, we will introduce how to use it using addition as an example of arithmetic operations. Functions can also be used in the same way, so please feel free to use them.
*If you want to use arithmetic operations or functions in KANNA, you need to set the formula in Excel first.
1. Open the Excel file to be imported into the KANNA report and click on the cell where you want to display the calculation results.
2. Inputting formulas
In the image, set the formula for addition
B11 (blue cell) + B12 (red cell)
3. Save the file and you are done.
Formula setup in Excel is complete.
(2) Setting formulas in KANNA reports
1. From [Settings], select [Custom Report Setting]
2. Click [+Register Template]
3. Click [Choose File] and select the Excel file with the formulas set.
You can also insert a file by drag & drop.
4. Set the input item for the formula reference cell to [Numerical Value], and click [Save and continue] on the upper right corner.
In the case of the below photo, set B11 and F22, which are reference cells in the " Enter formula " section, to 【Numerical Value】.
5. Click the "Publish" button to complete.
*After the creation is complete, the site will remain private.
Formulas can be set up to be used when creating reports.
Reference other sheets
(1) Setting up references to other sheets in Excel
1. open the Excel file to be imported into the KANNA report and click on the cell where you want to reference the contents of the other sheet.
2. inputting formulas
Enter the formula into the cell you wish to reference.
Formula for referring to other sheets: = “Name of the sheet you want to refer to”! Cell you want to reference” (Symbols and numbers are single-byte characters)
As an example, in the below photo, the contents entered in cell A4 of the 【Reference Sheet】 sheet are referenced to cell B28 of the 【Report】 sheet, so enter “=Reference Sheet!
[Reference Destination Sheet].
[Reference Source Sheet].
3. Save the file and you are done.
Formula setup in Excel is complete.
(2) Setting up formulas in a KANNA report
1. from [Settings], click [Custom Report Settings]
2. click [+Template Registration]
3. Click [Select a File] and select the Excel file with the formula settings.
You can also insert a file by drag & drop.
4. Set the input items to the referenced cells and click [Save].
Referable input items are “String (single line)”, “String (multiple line)”, “Numerical value”, “Drop-down List”, “Date”.
5. After setting input items, click [Save and close].
5. Click the “Publish” button to complete.
After the creation is complete, the site will remain private.
◯Video of operation after setting
Referencing other sheets
1. Set up other sheet referencing in Excel
1. open Excel for KANNA report and click on the cell you want to refer to the contents of the other sheet.
Enter a formula
Enter a formula in the cell you want to reference.
Formulas for formulas referring to other sheets: = “name of sheet you want to refer to”! Cells to be referenced
Enter the formula in the cell you want to reference. Cell you want to reference" (Symbols and numbers are one-byte characters)
For example, in the following image, the contents entered in cell B2 of the 【Reference Sheet】 sheet are referenced to cell C18 of the 【Completion Report】 sheet, so enter "=Reference Sheet!Sheet!B2" in cell C18 of the [Construction Completion Report] sheet.
B2" in cell C18 of the [Construction Completion Report] sheet.
3. save the file and complete
Formula setting in Excel will be completed.
(2) Setting formulas in KANNA report
1. click on “Report Settings” from the “Settings” menu.
2.Click on "+ Template Registration
3. click 【Select File】and select the Excel file with the formula settings.
You can also insert the file by drag & drop.
4. set input items in the referenced cell and click [Save].
*Referenceable input items are “String (one line)”, “String (multiple lines)”, “Numeric”, “Drop-down”, ‘Date’, and “Date/Time”.
5. After setting the input items, click “Save & Close”.
6. Click the “Publish” button to complete the process.
*The information is not available to the public after the creation is completed.
Notes (1) (Unsupported formulas and functions)
Formulas other than the four arithmetic operations are currently not supported by KANNA Report. Therefore, if a formula other than the four arithmetic operations is set in Excel and imported into KANNA Report, an error will be displayed as [#UNSUPPORTED] or [Unsupported formula].
The same applies when a function other than the *SUM function is set.
On the other hand, if you export KANNA Report as Excel after filling it out, the Excel file will display the formulas because the functions are valid.
Example: DATE function (date)
Error screen when creating forms -> Excel exported from KANNA
Notes (2) (When creating forms)
If there is an error in the formula, "#ERROR" will be displayed.
Example: Error screen due to zero division (2÷0)
Notes (3) (When setting formulas)
If you upload the Excel file with a calculation in a cell but non-numeric values (text or images) are inputted in the cells used for the calculation to KANNA, the calculation result will be displayed as 0 when creating a report.
Before importing into KANNA, please make sure that nothing is entered in the formula reference cell.
In the case of the below photo, the cell in the red frame has a calculation (G12 + I12) but the result is 0 because the non-numerical values are inputted in G12 and I12.