How to Use Variable Formula in Google Sheets

So for the topic of this tutorial, I have chosen to talk about defining variables in formulas in Google Sheets and how to save temporary variables in Google Sheets formulas.

Hey there! Welcome to TeqTog! This is where you get to learn about all kinds of amazing tips and tricks you can use when you are working on Google Sheets.

I will use an example with my own data so that you can understand better.

So, let’s start.

How To Define & Use Variables In Google Sheets

Storing variables in Google Sheets is a very effective way to deal with large volumes of data and calculations since it can save you a lot of time and help you with long equations.

Suppose, I need to assign a variable for the number of patients diagnosed on a particular day, as well as for the visit fee charged from each patient; so that I can calculate the income for that doctor in one day.

use Variable Formula with 2 doctor income example

As you can see in my data, I have the number of patients who came and received diagnoses in one day; as well as the visit fee for each patient.

Now, all I have to do is, click on the cell which has the data for the number of patients checked and, goes to the small reference box at the very left corner on the top of the spreadsheet. This box will have the cell reference displayed and you need to just erase that and type in what you would like to call that cell. I, for instance, decide to call this cell “np_checked”; which stands for “number of patients checked”.

go to the small reference box at the very left corner on the top of the sheet

I can do the same thing for the cell which shows the fee charged for each patient. Let’s call that “np_charged”; which means “number of patients charged”.

Now, if I want to check the total income that the doctor earned for the day, I have to insert the formula “=np_checked*np_charged”. And there you go, I have my results.

To check the total
insert the formula
Get the total earnings of that example doctor data

I can now add a bit more information and use the name of those new variables too.

For instance, I could add new data which represents the number of patients checked at another hospital by the same doctor. So let’s say, I call this variable “np2_checked”. This means, the number of patients diagnosed at hospital no. 2.

Add another hospital data

I can also go ahead and introduce a new variable which represents the total number of patients diagnosed in both hospitals together. I can name this variable “np_total”; which obviously means the total number of patients checked.

to get both hospital data in total use new variable np_total
add the first two variables for the row

Now, I can add the first two variables for the row “total number of patients checked” also, multiply the visit fee charged by that number, and get the total income earned for that day.

If I want now, I can modify the data for the number of patients diagnosed or the fee charged.

Final results of the use of Variable Formula in Google Sheets

You should know that the example I have shared here, is very simple, however, this same technique is extremely helpful. Especially when it comes to performing calculations with long formulas; naming variables can be very convenient.

The Shortcut: Bonus Trick

A bonus trick I can share is, that you can open a new sheet where you can just type the reference cell from the first page; this way, your spreadsheet won’t be cramped with so much data, but only the information you want to see.

How to use Variable Formula in Google Sheets
type the reference cell

And there you go!

Now you know how you can define variables and, that every time you name a variable, Google Sheets will use that name and its auto-fill options.

Best of luck!

Read More Tutorials On Google Sheet:

How to Find Slope in Google Sheets (With a Graph and Without a Graph)

Google Sheets Formulas Not Working (Refresh Formulas)

How to Use the ISDATE Function in Google Sheets

How to Calculate Percentage in Google Sheets



Related More Articles

Leave a Comment