Do you have multiple lists in Excel or Google Sheets that you want to combine together? With new functions such as VSTACK and HSTACK, you can do just that. In this post, I’ll show you how you can also filter out duplicates and apply sorting so that your data is organized after consolidating all of your lists.
Combining multiple stock lists into a large one
In this example, I’ll use various stock lists that I want to combine into one large list. On Yahoo Finance, you can find an assortment of different lists to help filter stocks. Below, I’ve pulled the lists of stocks that recently hit new 52-week highs, smart money stocks, medical device and research stocks, and e-commerce stocks:
The advantage of keeping the lists separate is that you can more easily update them. And by using VSTACK, you can combine these lists into a larger one so there’s no worry about having to consolidate them later on.
Based on the lists above, this is the formula that I use to combine them all together, using VSTACK:
=VSTACK(A2:A31,B2:B10,C2:C31,D2:D20)
Since I don’t want to include the headers, I start from row 2. You’ll notice that I’ve hardcoded the ranges here. One way to make this more dynamic would be to use a COUNTIF or COUNTA function for the individual lists, and then use the INDIRECT function to limit the scope of the list. Another option involves converting the lists into tables. That way, you only have to list the table column and you don’t have to worry about the ranges. The one caveat here is that if you have lists that have different lengths, you’ll want to make each list its own table. Otherwise, Excel will automatically fill in the gaps with blank values:
While the data looks correct, if I were to use the VSTACK formula for these different table columns, I would get a consolidated list that involves many zero values. To keep it cleaner, it’s easier to just separate them into their own tables, and then reference them afterwards.
To reference these columns, my formula becomes much simpler:
=VSTACK(Table1[Recent 52-Week High],Table2[Smart Money Stocks],Table3[Medical Device and Research Stocks],Table4[E-commerce])
The advantage of doing it this way is that now I don’t have to worry about hardcoding the ranges, and thus, it’s easier to update.
Whichever method you prefer, the end result should look like a consolidated list:
Removing duplicates and sorting the list
In some of these lists, there is some overlap. AMZN and META are two stocks that show up twice. This means that my consolidated list will include those values multiple times. To get around this, I can embed the formula within the UNIQUE function:
=UNIQUE(VSTACK(A2:A31,B2:B10,C2:C31,D2:D20))
If you also want to sort your list, then you can add the SORT function as well:
If you have the same lists but instead have them going horizontally, then you can use the HSTACK function. It works the same way as the VSTACK but as the H suggests, it will require horizontal arrays. Here are the same list of stocks as in the first example, this time transposed so that they go horizontally:
In this case, the formula for HSTACK would be as follows:
=HSTACK(B1:AE1,B2:J2,B3:AE3,B4:T4)
You can apply the same steps as for the VSTACK to eliminate duplicates and to sort the results.
These formulas work the same in Google Sheets as in Excel
Whether you’re working in Google Sheets or Excel, these formulas will be the same. The VSTACK, HSTACK, SORT, and UNIQUE functions are all available on the latest version of Excel and on Google Sheets. There is no need to change any of the formulas besides just adjusting for any difference in ranges. The formulas themselves work in the same ways, making it easy to transfer data between Google Sheets and Excel and to replicate these formulas wherever makes sense for you.
If you liked this post on How to Use VSTACK and HSTACK in Excel and Google Sheets to Consolidate Lists, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Excel has many different functions that can help you parse out text from cells. This includes the LEN, MID, LEFT, and RIGHT functions. By utilizing these and other functions, you can get just the values you want. And by determining the number of blank spaces within a cell, you can also determine the number of words that a cell contains. There are multiple ways you can count cells in Excel, I’ll start with using the easier, and newer TEXTSPLIT function.
Method 1: Counting words using the TEXTSPLIT function
The TEXTSPLIT function is available for users who have Microsoft 365 and so if you do not see that function available as you type it in, you’ll need to move to the second approach. Using the TEXTSPLIT function, you can turn a single text value in a cell into multiple cells or columns. And you can specify how you want to split a cell; which delimiter you want to use.
In the example of counting words, the delimiter you would use is a blank space, as specified with ” ” in the delimiter argument. Here’s a list of article titles that I am going to use for this example:
The article titles are in column A. The formula to split the text every time there is a blank space would be as follows, assuming the first value is in cell A2:
=TEXTSPLIT(A2,” “)
This formula, however, would simply put all the words in different columns. Thus, it is incomplete when your goal is to count the number of words. To fix this, the formula needs to be embedded within the COUNTA function. How COUNTA works is that it simply counts the number of nonblank values.
=COUNTA(TEXTSPLIT(A2,” “))
Copying this formula down, these are the resulting values and the number of words found in each cell:
Here’s a closer look at how the formula in B2 works, using the Evaluate Formula feature in Excel:
The TEXTSPLIT function is breaking out each word as its own separate value. And the COUNTA function is then counting each one of those values. When combined, these functions allow you to count the number of words in a cell.
If you’re using Google Sheets, you can use the exact same formula as shown above, with the only difference being that instead of TEXTSPLIT, you’ll use the SPLIT function. It works in the exact same way.
Method 2: Using the LEN and SUBSTITUTE functions to count words
If you are on an older version of Excel where TEXTSPLIT isn’t available, there’s still a way that you can count the number of words within a cell. It will be a slightly more complex formula that will use the LEN and SUBSTITUTE functions.
The first part of the formula will involve counting the number of characters in a cell, which is what the LEN function does. This is accomplished through the LEN(A2) formula — assuming that A2 is where the article name is.
Next, you’ll need to use the SUBSTITUTE function to replace the blank values ” ” with an empty string that just contains two quotes: “”. To do that, the formula for that portion would be: SUBSTITUTE(A2,” “,””). This formula will need to be enclosed within a LEN function. What this accomplishes is it counts the number of characters in the cell after you’ve replaced all the blank values. If you take the total cell length and subtract this second piece, you’ll be left with the number of blank values in the text.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)
However, this isn’t entirely correct as you will be off by 1 word. This is because since the formula is counting the number of blanks, it won’t include the first word, which doesn’t come with a space before it. That also means if you only have one word, you’ll have a value of 0 instead of 1. To fix this, you’ll simply need to add a +1 to the end of your formula.
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””)+1
This would mean, however, that even blank cells would return a value of 1. And this would technically be the same problem when using the TEXTSPLIT function as well, since it doesn’t check for blanks, either. To correct this, you can simply add an IF function to check if the value is indeed blank. Here’s how the full formula looks:
This will return nothing if the cell is blank. If the cell isn’t blank, then it will go ahead and perform the rest of the calculation. As mentioned, this IF function can also be added to the start of the TEXTSPLIT function as well.
If you liked this post on How to Count Words in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
If you’re an accountant, you know that working with large amounts of data can be a daunting task. But with Excel, that work can get a whole lot easier and more efficient. Understanding Excel’s advanced features and functions can improve productivity, reduce errors, make your work more accurate, and most importantly — save you time. Below, I’ll go over some of the most important Excel functions that accountants should know, and provide examples of how to use them. For this example, I’ll use the following spreadsheet. Feel free to download it and follow along with the calculations.
1. SUM
The SUM function is a basic but essential function in Excel. It allows you to add up a range of values, which is helpful when calculating totals, such as revenue, expenses, and profits. Suppose you have a spreadsheet with sales data. In the above example, the total sales are in column G. If you wanted to sum up the entire column, the formula would be as follows: =SUM(G:G)
2. AVERAGE
The AVERAGE function calculates the average of a range of values. It is useful when analyzing data and preparing financial statements. In the above example, suppose you wanted to calculate what the average sale was. To do this, you can just use the AVERAGE function on column G, similar to the SUM function before. Here’s the formula: =AVERAGE(G:G)
3. IF
The IF function allows you to test a condition and return one value if the condition is true and another value if the condition is false. This can be useful because it can send your formulas to the next level. By knowing to use the IF function, you could also use SUMIF, AVERAGEIF, and many other functions that involve an if statement. In the above example, let’s say you only wanted to know if a value in cell M2 was part of the Motorcycles product line. The formula would be as follows: =IF(M2=”Motorcycles”,1,2). If it is part of Motorcycles, you would have a value of 1, otherwise, it would be 2.
4. SUMIF
By knowing the SUM and IF functions, you can combine them together with SUMIF, which is an incredibly popular function. It gives you a quick way to tally up the totals that meet a criteria. For example, let’s say you want all sales that relate to the Motorcycles category. The formula for that would be as follows: =SUMIF(M:M,”Motorcycles”,G:G). If the criteria is met in column M, then the formula will sum up the corresponding values in column G. There’s also the super-powered SUMIFS function, which allows you to combine multiple criteria.
5. EOMONTH
The EOMONTH function calculates the last day of the month for a specified number of months in the future or past. It is useful when working with data that is organized by date. For accountants, this can be useful when you’re calculating when something is due. Let’s say in this example, we need to calculate the date orders need to go out on, and that needs to be the end of the next month. Using the ORDERDATE field in column H, here’s how that calculation would look in the first cell, which would then be copied down for the rest: =EOMONTH(H2,1)
6. TODAY
The TODAY function is helpful for accountants in calculating deadlines and knowing how many days are remaining or past a certain date. Suppose that you wanted to know how many days have past since the ORDER DUE DATE that was calculated in the previous example. Rather than entering in a static date that every day you would need to change, you can just use the TODAY function. Here’s how a formula calculating the days since the deadline for the first cell would look like, assuming the due date is in column N: =TODAY()-N2. The next day you open up the workbook, the calculations will update to reflect the current date; there’s no need to make any changes. There are many more date calculations you can do in Excel.
7. FV
The FV function calculates the future value of an investment based on a fixed interest rate and a regular payment schedule. You can use it to calculate the future value of an investment or savings account. Let’s say that you wanted to save $10,000 per year and expect to earn a return of 5% per year on that investment. Using the FV calculation, you can do that with the following formula: =FV(0.05,5,-10000). If you don’t enter a negative for the payment amount, the formula will result in a negative value. You can also specify whether payments happen at the beginning of a period (1) or end (0 — this is the default) with the last argument in the function.
8. PV
The PV function lets you do the opposite and work backwards from a future value to the present. Knowing that the calculation in example 7 returns a value of $55,256.31, that can be used in the PV calculation to check our work: =PV(0.05,5,10000,-55256.31). The formula returns a value of 0, which is correct, as there was no starting value in the FV calculation.
9. PMT
The PMT function calculates the periodic payment required to pay off a loan with a fixed interest rate over a specified period. It is helpful when determining the monthly payments required to pay off a loan or mortgage. Let’s take the example of a mortgage payment where you need to pay down $500,000 over the period of 30 years, in monthly payments. At a 5% interest rate, here’s what the payment calculation would be: =PMT(0.05/12,12*30,-500000,0). Here again the ending value needs to be a negative to avoid a negative value in the result. And since the payments are monthly, the periods need to be multiplied by 12 and the interest rate is dividend by 12.
10. VLOOKUP
The VLOOKUP function allows you to search for a value in a table and return a corresponding value from another column in the same row. It’s one of the most common Excel functions because of how useful and easy to use it is. It is helpful when working with large data sets and performing data analysis. Let’s suppose in this example that you want to find the sales related to order number 10318. The formula for that calculation might look like this: =VLOOKUP(10318,C:G,5,FALSE). In a VLOOKUP function, you need to specify the column number you want to extract from, which is what the 5 represents. If you’re using Office 365, you can also use the newer, flashier XLOOKUP function. I put VLOOKUP on this list because it’ll work on older versions of Excel — XLOOKUP won’t.
11. INDEX
The INDEX function allows you to return a value from a data set by specifying the row and column number. It’s also helpful if you just want to return data from a single row or column. For example, the sales column is in column G. If I know the order number is on row 20 (which relates to order number 10318), this formula would do the same job as the VLOOKUP in the previous example: =INDEX(G:G,20,1).
12. MATCH
The MATCH function allows you to find the position of a value within a range of cells. Oftentimes, Excel users deploy a combination of INDEX and MATCH instead of VLOOKUP due to its limitation (e.g. VLOOKUP can’t extract values to the left of the lookup field). In the previous example, you had to specify the row belonging to the order number. But if you didn’t know it, you could use the MATCH function within the INDEX function. The MATCH function would look like this: =MATCH(10318,C:C,0). Placed within an INDEX function, it can replace the argument where in the previous example, we set a value of 20: =INDEX(G:G,MATCH(10318,C:C,0),1). By doing this, you have a more flexible version of the VLOOKUP function. You can also create dynamic formulas using INDEX and MATCH that use lookups for both the column and row.
13. COUNTIF
The COUNTIF function allows you to count the number of cells in a range that meet a specified condition. Let’s count the number of values in the data set that are Motorcycles. To do this, you would enter the following formula: =COUNTIF(M:M,”Motorcycles”).
14. COUNTA
The COUNTA function is similar to the previous function, except it only counts the number of non-empty cells. With no criteria, it is helpful to just the total number of values within a range. To calculate how many cells are in this data set, you can use the following formula: =COUNTA(C:C). If there are no gaps in data, then the result should be the same regardless of which column is used. And when combined with the UNIQUE function, you can have an easy way to count the number of unique values.
15. UNIQUE
The UNIQUE function returns a list of unique values within a range, and it’s a much easier method than the old-school way of extracting unique values. If you wanted to extract all the unique product lines in column M, you would enter the following formula: =UNIQUE(M:M). If, however, you just wanted to count the number of unique values, you could embed it within the COUNTA function as follows: =COUNTA(UNIQUE(M:M)). You can adjust your range if you don’t want to include the header.
This is just a sample of some of the useful Excel functions that accountants can utilize. If you are familiar with them, you’ll put yourself in a great position to improve the efficiency of your workflow and make your spreadsheets easier to use. Plus, you can confidently say that you are highly competent with Excel, which can make your resume more attractive and make you better suited for accounting jobs that require advanced Excel skills — and there are many of them that do!.
If you liked this post on 15 Excel Functions Accountants Should Know, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
Want to show your data in reverse order, and want to do so without having to sort it? Using just a formula, you can change the way your data looks. Instead of going from oldest to newest, you can display it from newest to oldest. And you don’t have to alter your existing data set to do it. In this post, I’ll show you can how can flip your data through just a single formula.
In the below example, I have data going in descending order by year.
If I wanted to change the order so that it’s in ascending order, I could use the sort button. But what if you needed to keep the data the way it is and I just wanted to put in different order, say for the sake of a chart or report. I can accomplish this using the functions INDEX, ROW, and COUNTA. Here’s how it works.
Creating the formula to flip data
The first function I’m going to use is the INDEX function. This is important because this function will include all the data that I need. Since my data is in the range A1:B6, I’ll start with a formula to get the years in column A and reverse them. The formula will start as follows
=INDEX(A$1:A$6
I’m freezing the row numbers because I want to ultimately copy this formula over to the revenue column, and flip those values as well. In the next part of my formula, I will need to grab a count of the values in my range. This can be achieved by using the COUNTA function:
=INDEX(A$1:A$6,COUNTA(A$1:A$6)
The above formula would grab the last value in the range. And while that’s technically what I want, the formula wouldn’t work if I were to copy it down. Thus, I need to add a 1 to it and I also need a way to also deduct 1 — for the first instance, anyway. To make it dynamic, I’m going to use the ROW function.
=INDEX(A$1:A$6,COUNTA(A$1:A$6)+1-ROW(A1))
How this works is that the formula will grab a count of the rows in the data set. Then the formula will add 1 but it will also deduct 1 using the ROW function. In this first formula, my value will be 6 — the same as the count of cells. However, if I drag it down, the formula for the next cell will be as follows:
=INDEX(A$1:A$6,COUNTA(A$1:A$6)+1-ROW(A2))
In the above formula, I’ll now be deducting 2 since the row value of A2 is 2. In this case, the row value I will be extracting is 5. The COUNTA function returns value of 6, then 1 is added, and the ROW function deducts 2. By dragging this formula down, it will now continue going backwards and so that the last value will be the first row. If I also copy this over to column B for revenue, I will now have both columns flipped and going in the opposite direction:
If you liked this post on How to Flip Your Data in Excel Without Sorting, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Did you know that you can filter data by just using a single function in Excel? There’s no longer a need to use advanced filters or to manually select the data you want, you can just use the FILTER function, and it will give you an array of values that have met your criteria. By using the function, you can save time and make it easier to extract the data into another place on your spreadsheet.
Applying a single filter
In the data set below, I have a list of makes and models of cars, and their prices.
Suppose you wanted to filter the data so that you only saw all the Ferraris on this list. You could use a normal filter in Excel to only see certain data. But by doing so, you will change the look of your data set. With the FILTER function, you can create a formula in another part of your spreadsheet and apply the filter you want to use.
Here’s what the formula could look like:
=FILTER(A:B,A:A=”Ferrari”)
In the above formula, I’m selecting both columns A and B (brand and model.number), but I’m only filtering column A where it equals the value of Ferrari. Now, in the area where I’ve entered my formula, I get an array back of all the Ferraris, with the values that were in both A and B.
You can apply more advanced filters than this.
Applying multiple filters
Suppose you wanted to also apply a filter so that you can see the Ferraris that are over $500,000. This part can be tricky because there’s only one argument field for what to include. The key here is to create multiple rules, and then multiply them by one another to determine if the result is true (i.e. both criteria are met).
The second criteria will be to look at column C and check if the value is more than $500,000:
C:C>=500000
To combine both of the rules, the criteria need to multiply against one another. If a criteria is met, the result is 1 (True). If it isn’t met, then it is 0 (False). So if both criteria are met, it would be multiplying 1 x 1. And if it’s a 1, then the value gets included. Here’s how the full formula looks like:
=FILTER(A:B,(A:A=”Ferrari”)*(C:C>=500000))
By deploying multiple criteria, the list of Ferraris becomes much smaller:
You could also expand this criteria even further. To add another criteria, simply add another condition to multiply against. That way, you can have even more specific criteria to apply.
At the same time, you can use OR criteria. And this can be accomplished by adding instead of multiplying criteria. If instead of multiplying I add my criteria, now my filter will look for either a Ferrari or a car that is priced at $500,000 or over:
This now covers a much broader list that includes non-Ferrari vehicles. By using multiplication and addition, you can create a variety of different rules. The key to remember is that if the end result will be a 1 (True), then it is included. If it will be a 0 (False), then it won’t be.
If you liked this post on How to Use the Filter Function in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Doing a simple summation in Excel is as easy as clicking on the AutoSum button or just using the SUM function. But in some cases, you don’t want to be summing up everything within a range. In those situations, you may want to use the SUBTOTAL function instead. In this post, I’ll go over how that function works, and illustrate the differences between it and SUM function.
What’s the difference between SUM and SUBTOTAL in Excel?
Suppose you have the following data set, which sows airport delays by carriers at different airports:
Using the SUM function on column header for carrier_delay, the total value comes out to 133,453,066. Even if you were to filter the data based on a single airport (in this example, JFK), the total value would remain the same:
If you were to use the SUBTOTAL function, however, then it would only perform a calculation on the cells that are visible and filtered. If you’re using SUBTOTAL, you just need to specify the type of calculation you want to perform:
To do a summation, you just enter 9 for the first argument. As you can see, there are options to do COUNT, COUNTA, MAX, MIN, PRODUCT, AVERAGE, PRODUCT, standard deviation, and variance calculations. Once you specify the first argument, all you need to do after that is select the range as you would in a normal SUM formula. Here’s what the SUBTOTAL formula looks like in my spreadsheet, where I am adding up the values in column Q:
=SUBTOTAL(9,Q7:Q500000)
Now, there’s a difference between the SUM and SUBTOTAL formulas in their results:
If you were to change your filters and selections, the SUBTOTAL value would change while the SUM value would remain the same.
If you liked this post on When to Use Subtotals In Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Microsoft Excel has been around for decades but Microsoft continues to roll out new features to enhance its software. Today, I’ll cover three recently released features that you need to know to be a more efficient Excel user.
1. A new shortcut that allows you to paste values
If you’re copying and pasting values in Excel and just want to paste the values, up until now, you’ve had to take the extra step of right-clicking and selecting values.
Although that doesn’t add a whole lot of time to the process, if there’s a more efficient way to do something, that’s what this website is all about. And the new way to copy as values requires just using the shortcut of CTRL+SHIFT+V when pasting. Whether you’re copying values from a website and don’t want to include formatting, or if you just want to copy a value from another cell and don’t want the formatting or formula, this new shortcut will be what you want to use.
2. The ability to search right from a menu
When Excel added the Ribbon, it grouped commands into different tabs. That can make it difficult to sometimes find commands because if you’re not on the right tab, you have to first navigate there before finding the command you want. One way around this has been to use the Quick Access Toolbar, where you can save your frequently used commands.
But even that isn’t ideal because you can’t add everything in there. The good news is that Excel has now added a search feature right into the default right-click menu. Simply right-click anywhere on your worksheet and you’ll now see a place to search for commands and functions:
3. An image function that allows you to pull in images from a URL
A new function that you can make use of in Excel will make it easier to load images into your spreadsheet. Rather than saving them and then uploading them into your workbook, all you need now is just the URL to the image you want to use. Then, within the new IMAGE function, just enter the URL in the first argument within quotation marks.
You can also specify an alt text and indicate whether you want the images to fit or fill the cell, or if you want to apply a custom height and width. In the below example, I use a URL that points to Netflix’s logo and have it fill in the cell. And as the cell expands, so too does the image:
Don’t have these options? Join the Office Insider program
These are the latest and greatest Excel features and so if you don’t have them and you’re using Microsoft 365, make sure you sign up for the Office Insider program. Through that program, you will have access to the newest features before the general public. Once joining, it may take a few days before you get the updates and start to see these features.
If you like this post on 3 New Excel Features You Need to Know in November 2022, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Excel’s date and time functions make it easy to calculate the difference between two dates. And in this post, I’ll show you how you can calculate age in Excel. This can include a person’s age, or the interval between two dates. You can also break this difference into years, months, days, minutes, and seconds.
Use the YEARFRAC function to calculate the time in terms of fractions of years
One of the easiest ways to calculate age is by using the YEARFRAC function. As the name suggests, it will give you the fraction of a year. Suppose you wanted to calculate the difference between the start of the year 2000 and Christmas 2022. This is what your formula would look like:
=YEARFRAC("1/1/2000","12/25/2022")
Note that depending on your regional settings, you may need to enter date values in different formats. Alternatively, you could simply reference cells that contain date values so that you don’t need to do any hardcoding here.
The above formula will return a value of 22.983. Since Christmas falls towards near the end of the year, the number is close to 23. If instead you choose Jan. 31, 2022 as the end date, then the formula would return a value of 22.083.
Use the TODAY function to make your formula dynamic
To calculate age so that it is always going to be up until today’s date, you can use the TODAY function. This avoids you having to enter the current date each time you want an up-to-date calculation. For example, if you wanted to calculate the fractional years between the start of 2000 and today, your formula would look like this:
=YEARFRAC("1/1/2000",TODAY())
The TODAY value will automatically update so you don’t need to do anything to trigger that calculation. Just by opening your workbook, Excel will pull in the current date value, and your formulas that contain the TODAY function will adjust accordingly.
Calculating month, day, hour differences
If you want to calculate the difference in months rather than fractions of years, there’s an easy way you can do that as well. Excel has a DATEDIF function that can make that process quick and easy. The logic is the same as with the earlier formula, but the main difference is that you enter “m” for a third argument, indicating month. Here’s the formula, using the same values as earlier:
=DATEDIF("1/1/2000","1/31/2022","m")
This formula gives a result of 264, which equates to 22 years. You’ll notice the drawback here is there are no fractions or rounding, just 264 months. If I adjust the end date to the start of February (“2/1/2022”), then it will return a value of 265 months. Until the month is complete, the formula won’t add the extra month, even if you’re selecting a date that’s nearly at the end (e.g. January 31).
One alternative you can make is to calculate the difference in days:
=DATEDIF("1/1/2000","1/31/2022","d")
This formula will return a value of 8,066. If you were to divide this by 365, you would get 22.09863. That’s the same answer I would get using the YEARFRAC function if I entered the last (optional) argument in that function to specify that I wanted to use 365 days for my calculation (the default calculation uses 360).
DATEDIF doesn’t have an argument that lets you calculate hours or minutes. However, with the number of days, you can approximate that by multiplying by hours. If you did want to get to that precise level of detail, you would need to create a separate formula for hours and minutes — and you would also need to ensure your date values included that level of detail to avoid approximation.
Using the HOUR, MINUTE, and SECOND functions, you can subtract the starting date from the ending date to arrive at a difference for each of those time calculations.. For these types of details, you should reference the cells as opposed to key in the hour, minute, and second values to ensure everything is entered correctly.
If you liked this post on How to Calculate Age in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Switching back between Celsius and Fahrenheit can be cumbersome if you haven’t memorized the formula. There are multiple steps involved and you can easily make a mistake. And while you could use shortcuts to try and approximate roughly how it is, you can be more precise if you create a formula that you can use over and over again. If you’re using Excel, you can save yourself a lot of time as you can convert from Celsius to Fahrenheit (as well as in the other direction) using a formula which can compute the results in an instant.
What the formulas look like
The formula to convert from Fahrenheit to Celsius is as follows:
C = 5/9 x (F-32)
And this is the formula for the reverse:
F = (C x 9/5) + 32
You could set these formulas up in Excel just with simple arithmetic. The downside of that is then you have to create multiple formulas (one for Celsius and one for Fahrenheit), or even set up a small template just to do that. But Excel makes it even easier do to that as it has a function which can save you all those steps — it even knows the formula so you don’t have to memorize it!
Using the CONVERT Function
There’s convenient function right within Excel that can convert between different measurement values, called CONVERT. As the name suggests, it can convert values for you. It takes three inputs: the current value, what unit you’re converting from, and the unit you want to convert to. The formula to convert Fahrenheit into Celsius is as follows:
=CONVERT(A1,”F”,”C”)
Where A1 is the value in Fahrenheit. To do the reverse, you just need to flip the symbols:
=CONVERT(A1,”C”,”F”)
The key thing you just have to remember is that the unit you’re converting from comes first, followed by the unit you’re converting to. Those values need to be in quotes so that Excel reads them correctly as a text values.
This function is a lot more powerful and there are even more items you can convert between, simply look through the list of possibilities as you are entering the arguments.
You can flip between date values, measurements, weights, and many other things. The CONVERT function is much more powerful and converting between Celsius and Fahrenheit is just one of the many things it can do.
If you like this post on How to Convert Celsius to Fahrenheit Using an Excel Formula, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Do you want to calculate how quickly it will take for something to double in value? In this post, I’ll show you how to calculate that using the doubling time formula. By utilizing variables, it can also be easily updated in Excel to factor in different growth rates, making it easy to do what-if calculations.
What is the doubling time formula?
The doubling time formula utilizes logarithms and takes an assumed growth rate to determine how long it will take for a value to double in value. For example, if your investment were to rise at a rate of 10% per year for 10 years, it would be worth roughly 2.59 times what it is now. But rather than doing trial and error to try and determine exactly at what point it will double in value, you can use a formula to do that for you.
In essence, all the doubling time formula involves is taking the logarithm of the change in value you’re trying to get to (e.g. 2) and dividing that by the logarithm of the current growth rate plus 1 (e.g. 1 + 0.1 = 1.1). By doing this calculation, you get an answer of 7.27 for this example. You can plug that into the following formula to check:
1.1^7.27
And the result will 1.9995. The more decimal places you keep in the above calculation, the closer you will get to precisely 2. This formula can also be adapted if you want to calculate how long it will take to triple, or quadruple. In those cases, you can just change the numerator so that instead of taking log 2, you’re taking log 3 or log 4, if you want to calculate tripling or quadrupling time, respectively.
Setting up the formula in Excel
As you can see, this isn’t a terribly complex formula. The key is really just using logarithmic functions in Excel. And whether you use a natural log or not doesn’t matter, your results will be the same. You can use the LOG function for these purposes. In Excel, the earlier formula would be calculated as follows:
=LOG(2)/LOG(1.1)
To make it more versatile, I’ll also add some variables here. One for the current growth rate, and one for the target growth (this is where you can specify if you want to double, triple, quadruple, etc.). Here’s how that looks:
A value of 2 will read as 200% in Excel. The formula to calculate the years to double will simply need to be adjusted to factor in for these variables, which I’ve named TargetGrowth and GrowthRate in my file:
=LOG(TargetGrowth)/LOG(1+GrowthRate)
By utilizing these variables, I can now easily update my calculations.
Creating a LAMBDA function to make it even easier
Another thing you can do is to create your own LAMBDA function. If you’re on the latest version of Excel, these are custom functions you can ease, without the need to even set up a template and separate cells. All this involves is going to the Name Manager in Excel as if you were creating a new named range (the long way). Except when you create it, the name you’re assigning is the name of the function. And rather than referencing cells, you’re entering in a formula.
This particular function should contain two variables, one for the current growth rate, and one for the target. It will then plug them into the formula I referenced above. Here’s what the formula will need to look like within the Name Manager:
You’ll notice it needs the LAMBDA prefix so that Excel knows to treat this differently. Here’s how it looks within the Name Manager:
I called it DoublingTime even though it can do more than just calculate that. You can of course call it whatever you prefer. Now, this formula can be used in Excel to do the exact same calculation as above, without the need for extra cells:
You’ll notice here I’m just entering in raw values as opposed to percentages. This is just because of how I structured the formula and to keep it as simple as possible.
If you liked this post on Calculating the Doubling Time Formula in Excel Functions, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
