<![CDATA[Chandoo]]> Follow Chandoo, filter it, and define how you want to receive the news (via Email, RSS, Telegram, WhatsApp etc.) https://follow.it/chandoo Tue, 18 Jan 2022 09:11:16 +0200 <![CDATA[FREE 2022 Calendar & Monthly Planner Printable Template]]> https://api.follow.it/track-rss-story-click/v3/TluhBzTWdhdxFIL_z-ipTQ

Do you want a stylish, interactive and useful monthly calendar & planner template? Then please use below link to download it. A new year gift to you from Chandoo.org ?

Download the Calendar + Planner Workbook

Features of the Calendar:

  • Works for any month of the year 2022
  • Big cells with enough space to jot down notes when printed
  • Works in any version of Excel

Features of the Planner:

  • A customizable data entry worksheet for activities + events by date
  • A set of icons to tag activities
  • Works for any month
  • Needs Excel 365

Want to know how this is created?

I made a video explaining the calendar + planner logic, formulas and construction. Please watch it here.

Need more templates?

The post FREE 2022 Calendar & Monthly Planner Printable Template appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]> Wed, 05 Jan 2022 10:17:00 +0200 https://api.follow.it/track-rss-story-click/v3/TluhBzTWdhdxFIL_z-ipTQ <![CDATA[Merry Christmas & Happy New Year 2022]]> https://api.follow.it/track-rss-story-click/v3/TluhBzTWdhfHXtGg6PfMFQ

Hello readers & fans,

On behalf of my family, our staff, volunteers & well-wishers, Let me wish you a very happy Christmas & prosperous new year 2022. Here is a small holiday card from Chandoo.org HQ.

About this year’s card: (From left: Nishanth, Jo, Nakshu & Chandoo) We went to the beautiful Queenstown this October to celebrate my 40th (XL Birthday!!!) and took this picture just before a sunset. The kids are now taller than mum and as tall as me. We will be taking family pictures sitting down from now on, so that they don’t look like giants. ?

Thank you

2021 has been a very positive and inspiring year for me. I found a renewed sense of mission to learn, grow & share with the world. Even though I did not blog much this year, I have been very active on YouTube, building a community of Excel / Power BI learners. I want to take a minute to acknowledge some of the people & organizations that helped me this year.

  • You – for reading my articles, watching the videos or learning from a course. Thank you for making me your learning partner.
  • My family – for supporting & loving me all this while. My kids & Jo also played an important role in filming or giving feedback on my videos.
  • Microsoft – for creating Excel & Power BI.
  • YouTube – for providing an awesome platform to learn & share
  • My teachers – too many to name, but thanks for guiding and inspiring me. Some quick shout outs
  • Conference & events: This year I had the fortune of presenting virtually at many meetups and events. Special thanks to Vena Corp & Global Excel Summit.
  • My staff – Ravindra & Pothi for keeping the gears turning and helping me focus.
  • My customers – thanks to more than 1,200 people who bought a course, template, e-book or live cohort membership this year and supported me.
  • My Supporters – Thanks for more than 145 people who donated a superchat / supersticker during my YouTube live streams this year.
  • Our forum regulars & mods – thanks for taking time to help and share your knowledge.
  • Blog & channel friends – for recommending & sending people my way. Big thanks to Bill Jelen, Danielle, Ken Puls, Alan Murray, Sumit Bansal & Boriana for sharing a few tips on the channel
  • Software providers – for helping me run the site & channel. Big thanks to WordPress, Elementor, Stripe, E-junkie, PayPal, Wishlistmember, KnownHost, ConvertKit, Amazon, Gmail, Techsmith, EpidemicSound, Unsplash, Pexels & Pixabay

This holidays…

We (Jo & I) are taking some time off the work and traveling with kids to South Island on a road trip. We will be visiting Christchurch & Kaikoura for 10 days. I will be sipping beer on the beaches or going for a swim between now & 4th of Jan.

Once again, I wish you a very happy Christmas & a prosperous New Year 2022.

The post Merry Christmas & Happy New Year 2022 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]> Thu, 23 Dec 2021 09:06:00 +0200 https://api.follow.it/track-rss-story-click/v3/TluhBzTWdhfHXtGg6PfMFQ <![CDATA[How to write complex Excel formulas (hint: it’s a lot like LEGO)]]> https://api.follow.it/track-rss-story-click/v3/TluhBzTWdhfVIFhuEuDMFw

Do you want to know the secret to building complex, advanced Excel formulas? Here it is… 👇

Get inspiration from LEGO.

Confused? Let me demonstrate.

Calculating Student with Highest GPA

Let’s build a formula to calculate the student with best GPA out of a table like this:

students-and-gpas

Our data is in columns C& D, in the range C6:D45.

  1. We can easily lookup the name of a student with GPA of say, 3.53, using lookup formulas like XLOOKUP.
  2. We can also calculate the maximum (best) GPA using the MAX formula.

When you combine the Ideas 1 & 2, just like you combine LEGO, you create a third formula that gets the name of student with highest GPA.

So the final formula would be:

				
					=XLOOKUP(MAX(D6:D45),D6:D45,C6:C45)

And now, you can do the happy dance.

dancing legoman

How about second highest GPA Student?

We can extend this idea to get the name of student with second highest GPA. Instead of MAX, we need to use LARGE function. Like this:

				
					=XLOOKUP(LARGE(D6:D45, 2),D6:D45,C6:C45)

In the above formula, LARGE(…, 2) returns the second highest GPA.

You can use the same concept to get third highest or second lowest (use SMALL(…,2)).

The LEGO way of building complex formulas...

Just as there are a few basic building blocks in LEGO (well, nowadays, you can find a weird block shaped like dragon’s eyeball to fit in your harry potter set, but you get my point), we can also construct a complex formula from few basic ingredients.

Keep this construction tips in mind…

  • Break down the complex problem to small manageable chunks
  • Write down formulas for these chunks
  • If you need to repeat some logic, consider using either helper cells, or the new LET() function
  • Utilize the power of Excel references (absolute - $A$1, relative A1, mixed - A$1, $A1 and finally structural (table[column]) to write shorter & simpler formulas
  • Try newer formulas like FILTER, UNIQUE, SORT, LAMBDA etc. to get results easily
  • Take time to learn new techniques - such as spill ranges, LAMBDAs, data type driven formulas
  • When you have time, challenge yourself to figure out another formula to solve the problem

5 Examples of how to build complex formulas

If you are curious about this, then definitely watch my video. It shows how to write advanced formulas with Excel. See it below or on my YouTube channel.

Sample Workbook - 5 Complex Formulas

If you would like to practice these formulas, here is the sample workbook. 

Want more inspiration? Check out below examples:

There are heaps of advanced formula examples on chandoo.org. Start with below and see how you can twist the same 2×2 block in umpteen ways.

The post How to write complex Excel formulas (hint: it’s a lot like LEGO) appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]> Thu, 09 Dec 2021 08:41:19 +0200 https://api.follow.it/track-rss-story-click/v3/TluhBzTWdhfVIFhuEuDMFw