Trending March 2024 # How To Calculate Age In Google Sheets From Date Of Birth With Formulas # Suggested April 2024 # Top 3 Popular

You are reading the article How To Calculate Age In Google Sheets From Date Of Birth With Formulas updated in March 2024 on the website Hatcungthantuong.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 How To Calculate Age In Google Sheets From Date Of Birth With Formulas

If you have a list of people with their date of birth mentioned on Google Sheets, and you need to find their age, then this article will be helpful. This discussion is explained under the assumption that the dates are written dd/mm/yyyy format or dd-mm-yyyy format. It has been tested on Google Sheets and not any other similar software.

How to calculate Age in Google Sheets from Date of Birth

We can calculate the age of people on a Google Sheet page as follows:

Using the DATEDIF formula

Using the Array formula.

1] Using the DATEDIF formula

The syntax for the DATEDIF formula is as follows:

Eg. If you are creating the list of ages of the individuals in column C and the cell number of the first cell in the column of cells mentioning the dates of birth is B3, the formula would become as follows:

=IF(B3,DATEDIF(B3,TODAY(),"Y"),"")

Interestingly, the DATEDIF formula has an option if you wish to get the exact number of years, months, and days corresponding to the dates of birth. The syntax would be:

Eg. In the example mentioned above, the formula would become as follows:

=DATEDIF(B2,TODAY(),"Y")&" Years, "&DATEDIF(B2,TODAY(),"YM")&" Months, "&DATEDIF(B2,TODAY(),"MD")&" Days"

Some users might find it difficult to pull the formula across cells.

2] Using the Array formula

Unlike the DATEDIF formula, the Array formula requires you to mention all the details in the formula itself, thus making it easier to use.

The syntax for the Array formula is as follows:

Eg. If the dates of birth are listed in column B from B3 to B8, the formula will become:

=ArrayFormula(int(yearfrac(B3:B8,today(),1)))

If you wish to create the formula for an infinite number of rows, the syntax would be:

Eg. In the case mentioned above, the formula would become as follows:

=ArrayFormula(if(len(B3:B),(int(yearfrac(B3:B,today(),1))),))

The procedures mentioned in this post would be especially helpful for those who are managing employees, students, sports teams, etc.

In case you want to calculate the age containing only year (and no months and days) for a single cell only, then you can use the basic YEARFRAC function with cell number and current date. So, let’s say you want to calculate the age from the date of birth present in cell A5, then the formula will be:

=int(YEARFRAC(A5, today()))

Add this formula in a cell and hit the Enter key to get the result.

That’s it!

How do you calculate years between two dates in Google Sheets?

If you want to calculate the number of whole years between two dates in Google Sheets, then use the DATEDIF function along with cell #1 and cell #2 with year unit (Y). So, let’s say you have a date in cell C4 and the second date in cell C5, then to calculate the years between those two dates, the formula will be:

=DATEDIF(C4, C5, "Y") What is the formula age from date of birth in Excel?

To calculate the whole age from the date of birth in Excel, you can use the YEAR and NOW functions. As an example, if the date of birth is added in cell A7, then to calculate the total age from that date of birth, use the following formula:

=(YEAR(NOW())-YEAR(A7))

Make sure the cell where the date of birth is present displays a number (or formatted as a number or General), otherwise, the output won’t come.

Hope it helps!

You're reading How To Calculate Age In Google Sheets From Date Of Birth With Formulas

How To Search In Google Sheets

Last Updated on May 16, 2023

A lot of the functionality remains and Google Sheets can open Excel files too. Once you have used Microsoft Excel, you should be familiar with the search functions and they can prove exceedingly useful.

In this guide, we will look at the search functionality and how to use Google Sheets to find specific terms and words. You can even find them and replace them which can save you a lot of time.

1

Keyboard Shortcuts

You can simply use keyboard shortcuts to search for cells that contain the specific text that you want to find.

Step

1

Step

2

Keyboard Shortcut

Hold the Control key (Ctrl) and press F on a Windows keyboard. On a Mac, this is slightly different as you hold the Cmd key and then press F.

Step

3

Find Dialog Box

You should notice that a small Find dialog box opens in the top right corner of your sheet.

Step

4

Enter Search Word

Enter the term that you want to search for. In this case, ‘may’ is the name of the month you want to find to search the whole sheet and highlight any matches.

If there are multiple matches then you press the upward and downward-pointing arrows in the Find field.

Step

5

Result

As you can see, the cell containing the term ‘may’ has been highlighted as green proving that this is a really handy method to search for a specific term.

Only one result was found which was what we expected. In the next column to the highlighted field, you can see that May has 31 days.

2

Find And Replace

If you wanted to quickly find a specific term then replace it with another, there is a simple method of doing that.

Using the ‘Find and replace’ functionality can save a lot of time from having to find each matching term and then having to manually replace each one.

You can also be assured that every matching term has been found and replaced as you may miss a couple if you have hundreds to search through.

Step

1

Find And Replace Option

Step

2

Keyboard Shortcut

There is another way of opening the ‘Find and replace’ dialog box and that is by using another keyboard shortcut.

On a Windows keyboard, you can do that by holding the Ctrl button and pressing H. For a Mac, again this is slightly different and you will press the Cmd button with H.

Step

3

Find And Replace Dialog Box

Once you have opened the ‘Find and replace’ dialog box, you can easily find your desired term.

For example, let’s say it is a leap year and you want to find the 28 days in February and replace it with 29. Only one month has as few as 28 days so this should be straightforward.

Step

4

Replace All

Step

5

Found And Replaced

As expected, there was only one field to replace with our desired term and that has been completed.

Final Thoughts

If you realize that you have to replace one specific term with another then this can be easily done with the ‘Find and replace’ functionality too.

Using the ‘Find’ and ‘Find and replace’ functionality can make a tedious task seem very straightforward. Instead of having to trawl through an entire sheet (or sheets) of data, you can let the application do the hard work for you.

How To Create A Waterfall Chart In Google Sheets

Update December 2023: Google has added Waterfall Charts to the native charts in the Chart Tool of Google Sheets, obviating the need for you to manually create your waterfall charts (or use apps script) per my original post.

This original post that follows was first published in late 2024, and I’m leaving it here for anyone who wants to look under the hood at how waterfall chart data is constructed and how to do that using apps script.

Original article:

In this post, we’ll look at how to create a waterfall chart in Google Sheets.

Waterfall charts are real. And useful. They show the cumulative effect of a series of positive and/or negative values on an initial starting value.

The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value:

It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar).

The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below).

I’ll show you how to create both of these cases, starting with the easier, positive-bar case.

After creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts.

Templates are available for all three methods, with links at the end of each section and at the end of this post.

Case 1: Simple Waterfall Chart

In this case, all the bars are above the x-axis.

Step 1: What does our waterfall chart data look like?

The data for our simple waterfall chart looks like this initially:

What happens if we simply create a chart from the data table above?

Well, we end up with a standard column chart, which doesn’t show what’s happening as clearly as the waterfall chart. We can’t change the color of the bars, since they’re all part of the same series:

We can do better than that!

Step 2: get the data into the right shape

Create a new data table for the waterfall chart.

Directly adjacent to the original data, make a copy of the row labels, then add four new columns: Base; Endpoints; Positive; Negative, as shown below:

Step 3: Formulas for our waterfall chart

I’ve added color-coding to distinguish the different parts of the table.

In the first and last rows, rows 2 and 8 in this example, I’ve put 0 in the Base column and the count value in the Endpoints column. The Positive and Negative columns are left blank.

For the middle rows, rows 3 to 7 in this example, put this IF formula into the Base column:

Leave the Endpoints column blank.

Put this formula in the Positive column:

=max(B3,0)

Put this formula in the Negative column:

=-min(B3,0)

Drag these formulas down.

Step 4: Create a stacked column chart

Make sure you select the stacked column chart:

Step 5: Make the base transparent

Set the Base column color to none:

Step 6: Format the chart for presentation

Now it’s simply a matter of selecting suitable colors for the other series, formatting axes and titles.

You should also remove the legend as the series labels are essentially meaningless.

The final chart looks like this:

Case 2: Complex Waterfall Chart

In this scenario, let’s look at what happens if we have negative columns or columns that span the x-axis. For example:

It doesn’t look too different from the simple one above, so what’s the big deal?

Well, you’ll notice we have positive-value columns that cross the x-axis (e.g. Revenue in above chart), negative-value columns that cross the x-axis (e.g. Cost of Sales in above chart), as well as positive- and negative-value columns above and below the axes.

So there are more variations for our formulas to handle, which makes it more complex than the first example above.

Step 1: Complex waterfall data

This time our dataset is more complex:

Step 2: Use MIN and MAX formulas to shape the data

Naturally, so are the formulas.

I embarked on this project trying to create one single formula that I could drag across and down my columns and rows that would just fill in the values. I got bogged down in seriously wacky IF statements and decided the ROI wasn’t worth it (I still think it’s possible tho!). So I created an in-between version with special formulas for the first and last row, and then general formulas for the middle rows.

The formulas I created were hideous love-children of Array Formulas, IF formulas, SIGN formulas, ROW formulas, etc… Suffice to say, sub-optimal.

So I googled around to see how other people had solved this problem and found a far more succinct, understandable set of formulas from Excel visualization guru Jon Peltier. These are the formulas I’ve included in the waterfall chart template below.

Assuming the data from Step 1 above is in range A1:B8, add the following formulas to the following cells:

Header-row:

J1: Negative Cols, Below

End-rows:

F2 to J2: Leave blank

Middle-rows:

J3: =min(0,MAX(sum($B$2:$B3),B3))

Your final data table should look like this:

Step 3: Create a stacked column chart

This will add a waterfall chart to your page, from where you can follow steps 5 and 6 of the Simple example above to finish the chart.

How-to create waterfall charts automatically with Apps Script

As you saw in the example above, the formulas are pretty finicky for a generic, catch-all scenario when we have positive and negative bars, some of which may also cross the x-axis.

Arguably a better way to solve this problem is to write a small macro, using Google Apps Script, to do all the heavy lifting, wrangle the data and insert the chart directly into our Sheet.

For example, imagine we have the following data that we want to display as a waterfall chart:

It’ll take some time to manually figure out which bars cross the x-axis and what the base bars need to be for each one. However, once I’ve written a small macro, it takes only a few seconds each time to create the chart.

I’ve added the following code to my Script Editor:

function onOpen() { ui.createMenu('Waterfall Chart') .addItem('Insert chart...','waterfallChart') .addToUi(); }; function waterfallChart() { var range = sheet.getActiveRange(); var data = range.getValues(); var newData = [['Label','Endpoints','Base','Postive Cols above','Positive Cols below', 'Negative Cols above','Negative Cols below']]; var tempTotal = 0; var tempTotalPrior = 0; var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); for (i = 1; i < data.length; i++) { tempTotalPrior = tempTotal; tempTotal += data[i][1]; newData.push([data[i][0],data[i][1],0,'','','','']); } else { var baseVal = Math.max(0,Math.min(tempTotal,tempTotalPrior)) + Math.min(0,Math.max(tempTotal,tempTotalPrior)); var val1 = Math.min(tempTotal,data[i][1]); var val2 = Math.max(tempTotal,data[i][1]); var posValAbove = Math.max(0,val1); var posValBelow = Math.min(posValAbove - data[i][1],0); var negValBelow = Math.min(0,val2); var negValAbove = Math.max(negValBelow - data[i][1],0); newData.push([data[i][0],0,baseVal,posValAbove,posValBelow,negValAbove,negValBelow]); } } sheet.getRange(lastRow - data.length + 1, lastCol + 2, data.length, newData[0].length).setValues(newData); var chartData = sheet.getRange(lastRow - data.length + 1, lastCol + 2, data.length, newData[0].length); sheet.insertChart( sheet.newChart() .addRange(chartData) .setChartType(Charts.ChartType.COLUMN) .asColumnChart() .setStacked() .setColors(['grey','none','green','green','red','red']) .setOption('title','Waterfall Chart') .setLegendPosition(Charts.Position.NONE) .setPosition(lastRow - data.length + 4,lastCol + 4,0,0) .build() ); }

View this code on GitHub.

The onOpen() function adds a custom menu to the Sheet, so you can run the waterfallChart() function from the Sheet.

This runs the chart function, wrangles the data, pastes the new data into your Sheet and finally creates a draft chart for you:

Steps to setup and use this apps script waterfall chart template:

1. Open the view-only version of this file: Embedded Waterfall Chart with Apps Script

5. Go back to your Google Sheet and you should now have a new menu option, called Waterfall Chart.

This will create new table of data and waterfall chart.

Here’s the full process again:

Links to the Google Sheet templates

Complex Numbers In Google Sheets

Complex numbers are numbers in the form a + bi, where i is the square root of -1.

What on earth does that mean?

Imaginary And Complex Numbers

To start, consider an integer, say the number 4. The square root of 4 is 2. Easy peasy.

Now consider -4. What’s the square root of that?

It’s not -2, because -2 * -2 = 4 (a minus multiplied by a minus is a positive in mathematics).

No real number will equal the square root of – 4, so we need a new number.

This new number is called an imaginary number (no, I didn’t just make that up!) and is denoted by i and defined as the square root of -1.

i = √-1

or put another way:

i² = -1

A complex number is a number that has real and imaginary parts, and can be expressed in the form:

a + bi

where a and b are real numbers and i is the imaginary number defined above.

We can look at complex numbers visually in a 2-dimensional plane, with the x-axis representing the real numbers and the y-axis representing the imaginary numbers:

Complex numbers are usually represented by the letter z, and written:

z = a + bi

Let’s go and create one in Google Sheets!

Complex Numbers In Google Sheets

Let’s create a complex number in Google Sheets.

Starting in a blank Sheet, add the numbers 3 and 2 into cells A1 and B1 respectively.

In C1, add this formula with the COMPLEX function:

=COMPLEX(

A1

,

B1

,

"i"

)

This is what our complex number looks like in Google Sheets:

Size Of A Complex Number

We do this using the Pythagorean formula for the longest side of a right angle triangle, namely the square root of the squares of the other two sides.

Google Sheets has a handy function, called IMABS, that will calculate this value for us.

Put this formula into cell D1:

=IMABS(

C1

)

It gives the result 3.605551275.

This is the same as doing the square root of the squares of the other two sides:

=SQRT(3^2+2^2)

Answer 3.605551275.

Adding Complex Numbers

The next operation we need to know how to do with complex numbers is how to add them.

What’s 3 + 2i added to 5 + 7i?

To get the answer we add the real parts and imaginary parts separately, and combine into a new complex number.

So

= 5 + 9i

Back in our Google Sheet, I’ve added a new row for the new complex number and used the IMSUM function to add them together:

=IMSUM(

C1:C2

)

which looks like this in our Google Sheet:

Squaring Complex Numbers

Another operation we need to perform on our complex numbers is to square them.

The IMPOWER function does the trick. It returns a complex number raised to a power.

In our Google Sheet, use this formula in cell D1:

=IMPOWER(

C1

,2)

which gives the answer 5 + 12i, calculated as follows:

= 5 + 12i

Extracting Real And Imaginary Parts

You can use the IMREAL function to return the real coefficient of a complex number (the “a” part).

And IMAGINARY function returns the imaginary coefficient (the “b” part).

There are other, more specialized complex numbers functions in Google Sheets too, but they’re beyond the scope of this article.

Why Are Complex Numbers Useful?

For mathematicians, complex numbers are just as “real” as real numbers.

They have applications in many areas of mathematics, physics, and engineering.

One area where you may have seen them without realizing, is in fractal geometry and specifically pictures of a beautiful set of complex numbers called the Mandelbrot set:

Here’s How To Draw The Mandelbrot Set In Google Sheets.

How To Automatically Generate Charts And Graphs In Google Sheets

Charts and Graphs are useful tools used for conveying information to clients. Complex numerical data are often presented in graphical form so that they can be easily understood and remembered. The Graphical representation of data helps to present the information and its developing trends over a period of time in a quick way.

Visual images like charts and graphs also help to highlight the important facts which can be easily remembered and understood. Charts are most often used in business and for daily assignments allowing you to easily see trends and comparisons in a visually appealing way. Whether you want to deliver a presentation to frame a report, charts take an important role in visualizing a complex data analysis to your audience.

In this article, we explain how to automatically generate charts in Google Sheets.

Can you make graphs from data in Google Sheets?

Yes, Google Sheets comes with a native feature to automatically make graphs from the data present in a Google Sheets document. You need to select the graph or chart type (Line graph, Bar chart, etc.) in the Chart editor, set the data range, and other options, and a graph is generated based on the selected data range and graph type. You can also change the chart type later.

Generate Charts in Google Sheets

Fill the sheets with data into the cells and organize the numerical facts with a column header.

Navigate to Insert and select the option Chart from the drop-down menu. A chart editor window pops up.

In the Chart type field, select the type of chart you want to create from the drop-down menu. Google Sheets provides 30 variety of charts that you can choose to create for your data.

In the Data range field, select the range of cells that you want to be displayed on the table. You can either manually enter the range or use the pointer to select the data range.

Google Sheets will now create and display a chart with all the data selected from the range of cells in the sheet.

Now if you make any changes to the data in the sheets, the chart will be automatically updated with the new results without having to create a new chart for the updated value.

You can also change the type of chart for the same data easily in Google Sheets.

Go to Setup and in the Chart type field select a new type from the drop-down menu.

Customize charts in Google Sheets

Additionally, Google Sheets allow you to fully customize your charts based on the data. The customizing options allow you to configure the chart series, style, background color, axis labels, legend, and more.

The window displays options like Chart style, axis titles, series, axis labels, gridlines, and many more which you can choose based on the type of chart you want to create.

All the adjustments you make in data and charts editor are automatically updated without having to repeat the entire process. The changes are reflected without you having to even refresh the page.

How do I make Google Sheets automatically update charts?

You don’t have to do anything to make Google Sheets automatically update charts. As soon as you edit the data in a Google Sheets document, the chart is updated accordingly. For example, if you remove data from some cells and that data is a part of the data range for the chart, then the chart is changed and updated instantly.

Hope this helps.

How To Calculate The Roi Of Your Seo Campaigns Using Google Analytics

It can be difficult to determine the exact monetary value of SEO.

A successful SEO marketing strategy typically results in more search traffic and better rankings, and those things are what lead to more money in sales.

So how do you convince a company to hire you for SEO services without being able to promise them an exact dollar amount increase in their sales?

The answer is simple: you calculate ROI, or return on investment.

But before you can calculate ROI, you need to get some conversion data from your potential clients.

Conversion Tracking for Ecommerce Sites vs. Lead-Based Businesses

It’s important to understand that tracking conversions (and ultimately ROI) will look different if you’re an ecommerce site (you sell a product) or a lead-based business (you provide a service).

Ecommerce sites have data from their online transactions that show exactly how much they make from web sales, whereas lead-based businesses have to assign a monetary value to their types of conversions.

While it’s easier and more accurate to track conversions for ecommerce, it certainly is possible for lead-based businesses.

It’s just a little more complex and the set up itself looks different. Regardless of which type of business you run, the first step in determining ROI is to set up conversion tracking.

1. Set up Conversion Tracking Ecommerce Sites

For an ecommerce site, the first thing you want to do (if you haven’t already) is set up ecommerce tracking in Google.

Even if you aren’t planning to start an SEO campaign, you’ll need this data to determine the overall success (or failure) of your website.

Lead-Based Businesses

Setting up conversion tracking for lead-based businesses is a little trickier because there aren’t actual “transactions” going on on the website that result in dollars.

BUT all you have to do to get around this is to figure out what actions your clients are taking on your site and figure out how much they’re worth.

For example, if someone lands on your site and fills out a form to request more information, you could assign that a value of $100 (how you determine this number will be explained later on).

All of these actions are considered your company “goals” and should be entered into the “goals” section in Google Analytics (Admin- View- Goals in Analytics).

In the “goal details” section you’ll turn the value marker to “on” and then type in an estimated numeric value. How do you determine how much a lead is worth in dollars?

Follow these next steps:

How You Determine the Actual Worth of Your Conversions in Dollars

Let’s say for example you get 100 people each month to sign up for your company newsletter. If 25 of those customers end up hiring you to provide them with a service, then the conversion rate there is 25 percent.

If each of these 25 customers spends approximately $500 in services, then your average value of each sale is $500.

Do this for each of your “goals” and plug in the dollar value so that you have some concrete data to work with in order to calculate ROI.

2. Analyze Your Conversion Tracking

Once you’ve tracked your conversions using the above steps for approximately a month or two, you can begin to examine the data to see what kind of ROI you’re getting from SEO.

If you run a conversions report through Google Analytics, you’ll receive data on all your website traffic (i.e., how many conversions come from paid search, organic search, emails, referrals, social media, and more).

You’ll see a tab with the number of conversions listed as well as the value of these conversions. The value is essentially how much revenue has been generated from each search channel.

Compare these values with the amount of money you’ve spent on SEO during the same time period, and you can start to get a sense of your ROI.

For example, organic traffic comes from customers typing keywords into Google or another search engine, so this is directly related to SEO.

If your revenue from organic traffic is $100,000 in one month, and you paid an SEO company $20,000 to do keyword research and publish new content during that same time frame, then your ROI is $80,000.

Not sure exactly where to start?

Which digital marketing channels produce the highest ROI for websites? Check out this article.

3. Calculate Your ROI Percentage

If you want your ROI percentage, use this formula:

(Gain from Investment – Cost of Investment) / Cost of Investment.

Then, multiply the resulting number by 100 to get your ROI in terms of percentage.

For the above example, you would do:

100,000 – 20,000 / 20,000

80,000 / 20,000 equals 4

4 x 100 = 400

Your ROI is 400 percent.

Conclusion

Once you know your percentage of ROI, you can take it back to your clients and show them what the return on their investment is.

If they invested $20,000 in your SEO services, they don’t want to know that they got a 300 percent increase in organic traffic; they want the dollar amount of that traffic.

And with conversion tracking, you can give that to them. ROI typically changes from month to month, and it’s pretty common to have a negative ROI in the beginning.

It’s important to keep clients informed of the progress and to give them detailed reports of your SEO efforts vs. ROI every quarter, if not every month.

How do you calculate your company’s ROI?

What is your experience with conversion tracking if you’re an ecommerce site or a lead-based business?

We’d love to hear from you! Comment in the section below.

More Resources:

Image Credits

Update the detailed information about How To Calculate Age In Google Sheets From Date Of Birth With Formulas on the Hatcungthantuong.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!