How to Write Formulas in Excel | Different Types of Formulas in MS Excel
Hello Friends! I hope you are doing well,May God peace be upon you.
Excel is a programmable calculator where you can easily calculate highs, lows, averages, percentages, and more. All calculations are performed using special formulas, but they must be entered correctly, otherwise, the result will be incorrect.
First of all, you need to activate any cell by double-clicking on it. You can also use the top line. Entering the formula must begin with the “=” sign, then enter a numerical value and press Enter. The result is displayed in the selected cell.
List of Operation:
- addition “+”;
- subtraction “-“;
- multiplication “*”;
- division “/”;
- degree “^”;
- less than “<“;
- more “>”;
- less than or equal to “<=”;
- is greater than or equal to “> =”;
- not equal “”;
- percent “%”.
You can use numbers, cell addresses, and even combine them in one formula.
if several different mathematical operators are used in the same formula, Excel processes them in mathematical order:
- “%” And “^”;
- “*” and “/”;
- “+” And “-“.
The sequence of mathematical operations is also changed with the help of parentheses – the program, first of all, calculates the value in parentheses.
Permanent and Absolute links:
By default, all cells in Excel are permanent, which means they can change when copied. Absolute is constant, that is, they cannot change during copying unless another condition is specified.
Permanent links help you “stretch” a single formula over any number of columns and rows. How it works in practice.
- Create a table with the required data.
- To find the total cost for each product, you need to multiply the number of units by the price. To do this, enter the formula in the first cell through the “=” sign and press Enter.
- The same manipulations can be performed for each product, or you can copy the first formula and paste it into the remaining cells in turn. But everything is made easier: click on the cell, a fill marker will appear in the lower right corner, click on it and, without releasing the mouse button, drag it down.
Formula Types in Excel:
Excel understands several hundred formulas that carry out not only calculations but also other operations. If you enter the function correctly, the program will calculate the age, date, and time, provide the result of comparing tables, etc.
Determines the sum of several numbers. Each cell is indicated in brackets individually or the entire range at once.
= SUM (value1, value2)
= SUM (range_start: range_end)
Multiplies all numbers in the selected range.
= PRODUCT (range_start: range_end)
Helps to round a fractional number up (ROUNDUP) or down (ROUNDDOWN).
Converts text to lowercase.
= LOWER (cell_reference_with_text)
= LOWER (“text”)
Counts the number of cells with numbers.
= COUNT (cell_range)
Removes extra spaces. This will be useful when data is being transferred to a table from another source.
= TRIM (cell_address)
In large-scale calculations, problems often arise with writing functions or errors as a result. In this case, you will have to learn a little about the functions.
Allows you to “get” the required number of characters from the text. Usually used when editing titles in semantics.
= MID (reference_to_cell_with_text; start_numeric_position; number_of_chars_which_to pull out)
Parses the selected cell and checks if the value meets the specified parameters. There are two possible results: if the answer is true, if the answer is not, it is false.
= IF (what_data_checked; if_value_met_conditional_condition; if_value_doesn’t_met_condition_condition)
Summation of numbers under a certain condition, that is, it is necessary to add not all values, but only those that meet the specified criterion.
= SUMIF (C2: C5, B2: B5, “90”)
Excel has broad capabilities, in particular, it allows you to combine functions in any way.
There is a task: find the sum of three numbers and multiply it by a factor of 1.3 if it is less than 80, and by a factor of 1.6 if it is greater than 80.
The solution is as follows: = IF (SUM (A2: C2) <80; SUM (A2: C2) * 1.3; SUM (A2: C2) * 1.6). If the sum of the range is more than 80, the program will calculate the condition SUM (A21: C2) * 1.3, if more – SUM (A21: C2) * 1.6. Combined formulas can include 10 or more functions.
It is not necessary to memorize all the functions. In the “Formulas” section on the left of the panel, there is an option “Insert function” – there is a list of all known functions with description and division into categories for convenience.
To use any function, you need to click on the desired cell, click on the icon of the above option, or use the Shift + F3 key combination to call the wizard dialog box.
Find a function in the list, click on it, in the window that opens, fill in the fields of the range (numbers), criterion, and select a condition.
I hope You have Learned Something from here
If you want to know more about MS Word and MS Excel then Click on Below Clicks:
How to make Brochure in MS Word: https://myustaadg.com/how-to-make-brochure-ad-in-word-how-to-write-a-book-in-ms-word/
How to Add Page Numbering in Excel:https://myustaadg.com/how-to-add-page-numbering-in-ms-excel-add-page-numbering-in-excel/