Formulas

We are going to tell you the 19 Essential Excel Formulaswith a compilation for you to squeeze out the spreadsheets of Microsoft Office. Excel. This tool is together with Google Sheets one of the best alternatives to create spreadsheets, and these resources are initial ideas so you can see what you can do with it. And if you don’t know enough, remember that we have also shown you the best excel tricks. Excel formulas are the heart of the program, since they are what will allow you to do all types of calculations, and they can adapt the program to your needs. Today we will review the most useful formulas for the general public, with examples of how to use each. Excel files can be opened in other appsremember it, but if you use another one, the way you proceed with the formulas could be different. And these formulas are our basic proposals, but don’t be afraid to modify or combine them. And if you think there is any other that should be on the list, we invite you to tell us in the comments section so that other readers can benefit from the knowledge of our xatakeros. Simple math operations Before getting into more complicated formulas, let’s see how to do the simpler math operations: addition, subtraction, multiplication and division. Technically only the sum is a formula, since in the rest of the cases special operators are used. ADDITION– This formula adds the values ​​of the cells inside it. Supports both separate cells and intervals. Example: =SUM(A1:A50) Subtractions: To subtract the values ​​of two cells you must use the subtraction symbol “-” between them. Example: = A2 – A3 Multiplications: to multiply the values ​​of two cells you must insert an asterisk * between them. Example: = A1 * A3 * A5 * A8 Divisions: to divide the values ​​of two cells you must include the line / between them. Example: = A2 / C2 Excel respects the logical order of mathematical operations (multiplications and divisions first, then addition and subtraction) and supports the use of parenthesis to give priority to some operations over others. This way, you can create formulas like = (A1 + C2) * C7 / 10 + (D2 – D1). AVERAGE The average formula returns the value of arithmetic average of the cells you pass or range of cells you pass as a parameter. This result is also known as the mean or arithmetic mean. Use:=AVERAGE (cells with numbers) Example:=AVERAGE (A2:B2) MAX and MIN If instead of wanting to know the arithmetic mean you want to know what the highest value or lowest value of a set, you have at your disposal two formulas with predictable names: MAX and MIN. You can use them with separate cells or ranges of cells. Use: =MAX(cells) / =MIN(cells) Example: =MAX(A2:C8) / =MIN(A2,B4,C3,29) YES.ERROR SI.ERROR is a formula that will get you out of more than one trouble. With it you can avoid mistakes #DIV/0! and the like. This formula allows you return a value in case another operation fails. This is quite common with divisions, since any division by zero will give an error, which can cause a chain reaction of errors. The operation in question can be an operation or any other formula. Use: =IF.ERROR( operation, value if there is an error) Example: =IF.ERROR (MAX(A2:A3) / MIN(C3:F9),”There has been an error”) YEAH SI is one of the most powerful formulas in EXCEL and with it you can return a different result depending on if the condition is met. This way, you could use it to have one cell say “APPROVED” if another is a number greater than 5, or “SUSPENDED” if it is less. Use: =IF(condition, value if the condition is met, value if it is not met) Example: =YES(B2=”Madrid”,”Spain”,”Other country”) WILL COUNT COUNTA is one of the formulas for counting values. Unlike simple COUNT, WILL COUNT also count values ​​that are not numbers. The only thing it ignores are empty cells, so it can be useful for you to know how many entries a table has, regardless of whether the data is numeric or not. Use: =COUNT(range of cells) Example: =COUNT(A:A) COUNTIF The COUNTIF formula is a kind of mixture of the previous two. It will count the specified range of cells as long as when they meet certain requirements. These may have a certain value or meet certain conditions. Use: =COUNTIF(cell range, criterion) Example: =COUNTIF(C2:C, “Pepe”) SUM IF This is the equivalent for sums of the previous one. SUMIF is just as essential and very intuitive for users who already understand COUNTIF: it adds only the values ​​that meet a given criterion, without the need to manually filter. Use: =SUMIF(criterion_range; criterion, sum_range) Example: =SUMIF(B2:B50; “Madrid”; C2:C50) → adds all the values ​​of C only when the corresponding cell in B says “Madrid”. RANDOM.BETWEEN This formula generates a random number between two other given numbers, and is therefore ideal when you need to choose something at random. The generated number changes each time the sheet is regenerated (for example, when you type a new value). Use: =RANDOM.BETWEEN(smallest number; largest number) Example: =RANDOM.BETWEEN (1;10) DAYS The date calculations They are always a tricky subject if you have to do them by hand, but everything is much easier when a formula does the hard work for you. DAYS he tells you number of days difference between two dates. Use: =DAYS(first date, second date) Example: =DAYS (“2/2/2018”, B2) NOW Another Excel essential is the NOW formula. This generates the date for the current moment and it is data that will be automatically updated every time you open the sheet or its values ​​are recalculated (because you change a cell, for example). This formula does not require any parameters. Use: =NOW() Example: =NOW() DAYSEM SEMDAY is another useful formula related to dates, which returns numerically the day of the week of a date. Monday is 1, Tuesday is 2, and so on, although there are several ways to start … Read more