Question

In case the total cost of a machine less installation charges is greater than Rs 2,00,000, then 20% depreciation is charged, and if total cost of the machine is less than Rs 2,00,000 then 10% depreciation is charged.
Write the steps to create the 'IF' function using the Formula tab and dialogue box on a given spreadsheet. Also, write the syntax of the result.

Hint:

The \texttt{IF} function allows conditional calculations: \texttt{IF(condition, value\_if\_true, value\_if\_false)}.
\begin{itemize} \item Use \texttt{num\_digits $>$ 0}: Rounds right of decimal (decimal places). \item Use \texttt{num\_digits = 0}: Rounds to nearest whole number. \item Use \texttt{num\_digits $<$ 0}: Rounds left of decimal (nearest 10, 100, etc.). \end{itemize} Excel also has \texttt{ROUNDUP} (always rounds away from zero) and \texttt{ROUNDDOWN} (always rounds towards zero).

Answer 1

Scenario: Calculate depreciation based on the cost of a machine (excluding installation). Let's assume the 'total cost of a machine less installation charges' is located in cell `C2`. The rule has a slight ambiguity: "if total cost of the machine is less than Rs 2,00,000, then 10% depreciation". This might imply the *total* cost including installation, or it might mean the cost base (less installation). We will assume the condition check is on the value in cell `C2` (cost less installation).
Steps to create the 'IF' function using the Formula Tab and Dialogue Box (e.g., in Excel):
1. Select the Cell: Click on the cell where you want the depreciation result to appear (e.g., cell D2).
2. Go to the Formulas Tab: Click on the 'Formulas' tab in the Excel ribbon.
3. Insert Function: In the 'Function Library' group, click on 'Logical', and then select 'IF' from the dropdown list. This opens the 'Function Arguments' dialogue box for the IF function.
*(Alternatively, click 'Insert Function' (fx symbol) next to the formula bar or in the Formulas tab, search for 'IF', select it, and click OK).
4. Enter Logical\_test: In the 'Logical\_test' field of the dialogue box, enter the condition to be checked. Based on the problem, this is whether the cost (in cell `C2`) is greater than 2,00,000. Type: `C2 $>$ 200000`
5. Enter Value\_if\_true: In the 'Value\_if\_true' field, enter the calculation to perform if the logical test is TRUE (i.e., cost $>$ 200,000). The depreciation is 20\% of the cost (C2). Type: `C2 * 20\%` or `C2 * 0.2`
6. Enter Value\_if\_false: In the 'Value\_if\_false' field, enter the calculation to perform if the logical test is FALSE (i.e., cost = 200,000). The depreciation is 10\% of the cost (C2). Type: `C2 * 10\%` or `C2 * 0.1`
7. Confirm: Review the formula preview in the dialogue box. Click 'OK'. The formula will be inserted into the selected cell (D2), and the calculated depreciation will be displayed.
Syntax of the Resulting Formula:
The formula created in the cell (e.g., D2) would be:
\texttt{=IF(C2 $>$ 200000, C2 * 20\%, C2 * 10\%)}
Or using decimals:
\texttt{=IF(C2 $>$ 200000, C2 * 0.2, C2 * 0.1)}
Explanation of Syntax:
* \texttt{=} : Starts the formula.
* \texttt{IF} : The function name.
* \texttt{( ... )} : Encloses the function arguments.
* \texttt{C2 $>$ 200000} : The logical test (condition). Checks if the value in cell C2 is greater than 200,000.
* \texttt{,} : Separates the arguments.
* \texttt{C2 * 20\%} : The value or calculation returned if the test is TRUE. Calculates 20\% depreciation on the cost in C2.
* \texttt{,} : Separates the arguments.
* \texttt{C2 * 10\%} : The value or calculation returned if the test is FALSE. Calculates 10\% depreciation on the cost in C2.