Google Spreadsheets: Functions that I love (part 1)

On May 17th, 2022 in Tech

Yesterday I read an article from Wired (Spreadsheets Are Hot—and Cranking Out Complex Code) and it seems that spreadsheets are the new black.

That made think about how much do I use it on my everyday tasks and I noticed that I’m quite a heavy user and a good one. So I decided to write about it and I decided to start talking about my most used and loved ones.

Most of time I use spreadsheets to record activities and some numeric values. I’m not a heavy user on math functions or beautiful and elaborated charts. I’ve been playing with data science, but it’s a different activity mostly done using Plotly, Pandas and Python.

I wrote “Google Spreadsheets” despite “Spreadsheets” because I’m not using Excel any longer. Google Spreadsheets have a wider range of functions than Excel and the cloud allows you to run heavier functions without worring if you’ll brick your Excel file.

The most used one

UNIQUE + MATCH

Since I learned UNIQUE + MATCH my life spreadsheets had changed. UNIQUE + MATCH is much better than VLOOKUP and it let you work with a large ammount of data at once.

Tldr: UNIQUE + MATCH let’s you find a value on another sheet using a value/cell as reference.

E.g. You have a list of fruits and their weights in one sheet. On the other sheet, you have the list of the fruits and their prices. With UNIQUE + MATCH you can create a list with the fruits, weight and price, all at once.

Why UNIQUE + MATCH?

  • UNIQUE + MATCH is flexible. You can do horizontal/vertical lookups, 2-way lookups, left lookups, case-sensitive lookups and even lookups based on multiple criteria.
  • It works on Excel and Google Spreadsheet

INDEX function

Returns the content of a cell, specified by row and column offset.” (from Google)

Usage: “INDEX(reference, [row], [column])” (row and column are optional)

E.g.: “INDEX(A1:C20, 5, 1)” this function will get value on row 5, column 1 of the reference “A1:C20”. It will get the value from A5.

MATCH function

Returns the relative position of an item in a range that matches a specified value.” (from Google)

Usage: “MATCH(search_key, range, [search_type])” (search type is optional but it is usually defined)

E.g.: “MATCH(“Sunday”, A2:A9, 0)” this function will search for “Sunday” from cell A2 to cell A9. If, for e.g., “Sunday” is on cell A4, the function will return 3.

Now let the magic happen

The result is $689.00

When you join this 2 functions together, magic happens.

From this example, INDEX select, from C2:C5, the 4th value/row. That happens because MATCH search for Nissan (B7) from B2:B5 and finds that it is on the 4th row. The “0” after “B2:B5” means that I’m looking for an exact MATCH.

Where can I use INDEX + MATCH?

INDEX + MATCH allows you to use Google Spreadsheets or Excel like a small database. When you are working with a lot of data, it’s usually useful to separate it on multiple sheets. E.g.: you have a sheet listing all your contracts and, on another sheet, a list of all your clients. A client may have more than 1 contract with you and if you keep all the data in one sheet, you’ll have to change multiple fields every time that the client info is updated.

You can also use INDEX + MATCH to filter data from a large table where you are looking for a specific data, without the use of the filter function.

You’ll notice that INDEX + MATCH can be applied in many different cases that you need to check and validate data.

Tags: digital life spreadsheets

2022 goals: April progress

On May 6th, 2022 in Myself

Weeks ago I finally set my goals for 2022 looking for a more productive year. This is my 1st progress report.

2022 goals

  1. Keep writing down my projects and researches in this blog;
  2. Keep reading, listening and writing in English;
  3. Have all my photos from Japan edited. Have some of them at Shutterstock;
  4. Have Home Assistant fully functional at my new home;
  5. Try again to learn to write in Arduino and make some DIY projects;
  6. Study programming, data science and machine learning;
  7. Do periodical physical exercises;
  8. Meditate;
  9. Keep reading books;
  10. Avoid getting involved with Work;
  11. Do (small) courses/projects related to Work.

Overall Review

Things are still warming up. I’ve began some activities, but I’m not fully committed yet.

What I could do better in May?

Migraines are become frequently again and now I have more space at home. I’ll start again my Lian Gong and Yoga exercises.

Write down my projects and researches in this blog

Goal for this month: 2 posts
Achieved this month: 2/2 (100%)
Total achieved: 2/24 (29.16%)

Keep reading, listening and writing in English;

I’ve been listening a lot of content in English, but I’m almost not writing anything. I think that I’ll start to write my notes in English. I’ll cheat this month saying that I achieved 100% on this task because I truly consumed a lot of English content.

Goal for this month: 12 times listening and reading; 8 times writing
Achieved this month: 12/12 (100%)
Total achieved: 12/144 (8,33%)

Have all my photos (from Japan) edited

I didn’t begin that yet.

Goal for this month: 4 hours
Achieved this month: 0/0 (%)
Total achieved: 0/48 (0%)

Have Home Assistant fully functional at my new home

Home is still a mess, I have to install my server back to begin configuring my Home Assistant again.

Total achieved: 0%

(Try again to) learn how to write in Arduino

On halt until I have my office fully functional.

Total achieved: 0%

Study programming, data science and machine learning

I’m reading and studying a lot of programming and data science. On March I tried some machine learning, but I’m still at the beginning.

Goal for this month: 8 hours
Achieved this month: 12/8 (150%)
Total achieved: 36/96 (37.5%)

Do periodical physical exercises

Nothing on April. I’ll begin on May.

Goal for this month: 12 days
Achieved this month: 0/12 (0.0%)
Total achieved: 0/120 (0%)

Meditate

I meditated 1 day this month. A beginning, but far from the desired.

Goal for this month: 12 days
Achieved this month: 1/12 (8,33%)
Total achieved: 1/120 (0%)

Avoid getting involved with Work

Yes, I did it. I thought that it would be difficult (and it is), but I’m being able to handle quite good.

Goal for this month: 4 stress free weeks
Achieved this month: 3/4 (75%)
Total achieved: 14/48 (29.16%)

Keep reading books

I think that this is the only goal that I’m being able to follow. Reading is one of the best time of my day. I relax and forget about things to do at home or at work.

Despite reading almost every day this month, I couldn’t complete any of the books that I’m reading.

Goal for this month: 0 book
Achieved this month: 0/1 (0%)
Total achieved: 3/12 (25.0%)

Do (small) courses/projects related to work

This month I took a 20 hour course at work to learn how to use Microsoft Power BI. I’d taken the same course in 2018, but I mostly forgot about the tool. Also, nowadays my data science and programming skills are much better than 4 years ago. It was an excellent course.

I’m also implementing a lot of programming on my daily work. It’s being great.

Goal for this month: 1 course
Achieved this month: 1/1 (100%)
Total achieved: 1/12 (8.33%)

Tags: 2022 goals