MS Excel it is characterized by with a big number of functions that allow you to work with different type of data. But what is striking is that it does not have a tool that can subtract directly.
For it, it is necessary to use different ones that will help you get the result you are looking for. In addition, you should consider that you need to use them according to the type of data you have.
In this article we will show you, the functions you should use to achieve subtract two or more items. We will also indicate the steps you must take to subtract dates, time and any other format.
What are all the formulas used to subtract data in Excel?
In Excel you won’t be able to find one function that is used directly to subtract. Instead you can use the tool ADDITION and place a algebraic addition in which you must distinguish its positive and negative signs so that the program can fetch the values.
Another way you have subtract is to use the function PRODUCT for cases where you need subtract a certain percentage. You should bear in mind that to use this tool you will have to convert the percentage into a number of so much for one.
You will also have the function IF DATED with which you can subtract datesbut we will explain this tool to you in the following paragraphs.
Finally, if you need to subtract complex numbersyou must use the function IM.SUBST with which you must indicate a number required and the others optionally. This tool can be used like this example IM.SUBST(complex_num1, , , …).
Learn step by step how to subtract data or any element in Excel
We will show you below how you should subtract different data in Microsoft Excel:
dates
In case you want subtract datesyou will have the possibility of express it in years, months or days. The first thing you should do is put most recent date first and then subtract that cell with an earlier date. The result will give you the number of days between the two dates.
But there is a way much more practical to do so in which you can use a conditional formatting and setting values easier to understand. In this case the oldest date to see to go first. When you want to calculate day you must use the formula =DATEDIF(Oldest Date; Most Recent Date; “D”) in this case the number you will get will be represented in days.
If what you are looking for is find the months that exist between certain dates you must use the formula =DATEDIF(Oldest Date; Most Recent Date; “M”) and for years the formula will be =DATEDIF(Oldest Date; Most Recent Date; “Y”).
Data
To subtract data you must use a function SUM, but with negative signs. That is, the number that you want to remove from the other must go with the opposite sign. The easiest formula is to make the cells A1-B1or where the data is located.
percentages
For subtract percentages you must express them in so much for one by the first thing you should do is have the cells divided by 100If they are already expressed in this terminology, you should not perform this step.
Suppose the cell A1 it is equal to 95%which is the same as saying that its value 0.95. The cell A2 has a percentage of 65%or as much for one would be 0.65.
If both cells are subtracted that would give 0.95 – 0.65 = 0.30. That is, 30%.
Instead, if you want subtract a percentage from a number you should use the function PRODUCT. so if you want extract 15% to 90you will have to perform this formula: 90 x (1-0.15) = 76.50
cells
For subtract cells you will have to use practically one cell minus the other. For example, A1-B1.
Columns and rows
If what you are looking for is subtract The first thing you should do is total them. Namely, you will have to perform a general sum of each of the results of each column or row. then you must apply formula to subtract cellsas we mentioned in the previous section.
In case you want subtract multiple rows or columns you will have to do A1 – B1 – C1 – D1 one for each element.
Hours
when you need subtract hours or time datayou must make a subtraction between two columns using the format h:mm.
In case you have to subtract more than 24 hours you will have to go to Category and then click on Cell format. Next, you will choose Custom and in Type to choose m/d/yyyy h:mm am/pm. and then click on To accept.
Once you have made the formula you will have to go to a third cell to extract the result. so you will have to make the nearest date minus the previous by 24 hours.
Therefore, the example would look like this:
- In cell A1 you will have 03/19/2020 6:30.
- In A2 the value will be 03/10/2020 14:00, so the result in A3 (A2 – A1) * 24 will be 31.5 hours.
Facebook Comments
Disqus