SMARTSHEET FORMULAS 2023-50 QUESTIONS WITH 100% CORRECT ANSWERS.

Formulas: What is absolute cell referencing? Add a $ in front of the column name, row number, or both and it will not change when moved, copied, or filled Formulas: What is relative cell referencing? As you copy, the formula will automatically change its respective field references Brainpower Read More Previous Play Next Rewind 10 seconds Move forward 10 seconds Unmute 0:00 / 0:15 Full screen 1. How do you calculate the sum of a range of cells in Smartsheet? 1. To calculate the sum of a range of cells in Smartsheet, you can use the SUM function. For example, if you want to sum up the values in cells A1 through A5, the formula would be: =SUM(A1:A5) 2. What is the formula to calculate the average of a range of cells in Smartsheet? 2. To calculate the average of a range of cells in Smartsheet, you can use the AVERAGE function. For example, if you want to find the average of the values in cells A1 through A5, the formula would be: =AVERAGE(A1:A5) 3. How do you use the IF statement in Smartsheet to display a message based on a condition? 3. To use the IF statement in Smartsheet, you can use the following syntax: =IF(condition, true_result, false_result). For example, if you want to display a message "Yes" if the value in cell A1 is greater than 10, and "No" otherwise, the formula would be: =IF(A1>10, "Yes", "No") 4. How do you use the COUNTIF function in Smartsheet to count the number of cells that meet a specific criterion in a range of cells? 4. To use the COUNTIF function in Smartsheet, you can use the following syntax: =COUNTIF(range, criterion). For example, if you want to count the number of cells in the range A1:A5 that contain the text "Apple", the formula would be: =COUNTIF(A1:A5, "Apple") 5. What is the formula to calculate the percentage of total in Smartsheet? 5. To calculate the percentage of total in Smartsheet, you can use the following formula: =value/total*100%. For example, if you want to calculate the percentage of sales made by each salesperson in a table, you could divide each salesperson's sales by the total sales and multiply by 100%. 6. How do you use the VLOOKUP function in Smartsheet to look up a value in a table and return a matching value in another column? 6. To use the VLOOKUP function in Smartsheet, you can use the following syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). =VLOOKUP(Product@row, {Pricing_Sheet_Lookup_Range}, 5, false) 1. Look for the value in Product@row, in the first column of the cross-sheet reference {Pricing_Sheet_Lookup_Range} 2. Return the value in the 5th column of the range Kalum has a sheet in which they are tracking dates a project is due. The project should be due 30 days after the initial start date but if there is not a start date yet, then it will be "TBD". What formula would you use to calculate the due date based on the start date? =IF(ISDATE(Date@row), Date@row + 30, "TBD") 1. If Date@row is a valid date, Then Date@row + 30 days 2. Otherwise, "TBD" 8. How do you combine text from multiple cells into one cell? = [Task Name]1 + " " + [Task Name]2 Result: First Task Second Task OR = [Task Name]1 + "," + [Task Name]2 Result: First Task,Second Task 1. Click in the cell in which you want the result. 2. Press = (the equal sign). 3. Click the first cell that you want to refer to in the join and then press + (the plus sign). 4. Click the second cell that you want to refer to (to join the values together) and press Enter. 9. How do you use the SUMIF function in Smartsheet to sum the values in a column that meet a specific criterion? 9. To use the SUMIF function in Smartsheet, you can use the following syntax: =SUMIF(range, criterion, [sum_range]). For example, if you want to sum up the sales made by a particular salesperson, you could use the formula: =SUMIF(A1:A100, "Salesperson1", B1:B100) Write a formula that calculates the total cost for an item based on its unit price and quantity. Total cost formula: =Unit Price x Quantity Write a formula that calculates the sum of a range of cells. =SUM([Transaction Total]:[Transaction Total]) This sums the "Transaction Total" column Write a formula that calculates the percentage of completion for a project based on the number of tasks completed and the total number of tasks. Percentage completion formula: =Completed Tasks / Total Tasks