Formula Training

MCorbin

I'm training a guy to support Smartsheet for our company.   He's pretty good with most of the functionality, but he has no experience with formulas.

I thought I'd put together a training program for him to help him learn them - starting with basics, then building into nested formulas, etc.   I want to give him some samples to work with and he has to figure out how to create the formula  (along the lines of the Certification Exam sample worksheet)

I thought I'd ask - has anyone done that for someone on their team and would you be willing to share what you did?    If I don't have to re-create the wheel, I'd be happy not to   :-)

 

Thanks!

Nick Burrus

This exists in a way

https://app.smartsheet.com/b/home?sc=mjjH3-k10qQ,mwFH3yU133o

Smartsheet Formula Examples, click "Use" This is a live preview and example of every single formula to exist  

MCorbin

Cool - that's a start.    I want to do something where he's required to think and work out how the formulas should look and work - which function should he use, why is he getting this error?, etc.     I've got a start on it.   That sheet should help give me ideas of functions I might not have thought of using.

 

Nick Burrus

I honestly would love to build one of these! Maybe we should build a community based "certification" for formulas! 

Smartsheet's center of excellence doesn't really cover them in that much detail. I am certified in it. 

MCorbin

I noticed the same thing.   There's an assumption that someone knows what to do with formulas and functions going in.   But if you have someone who's had minimal exposure, they need some help   :-)

Andrée Starå

Hi,

To start, I'd recommend enrolling your guy in the Center of Excellence e-Academy course and then continue with the other resources.

There are excellent resources in the Center of Excellence, Solution Center, Help Center, and last but not least here in the Community.

The Solution Center has Templates and Template sets that are a great way to learn how everything works.

The prompt that pops up when your adding/editing formulas are also a great way to learn how it works.

More info and excellent resources:

https://excellence.smartsheet.com/

https://help.smartsheet.com/

https://help.smartsheet.com/learning-track/solutions

https://help.smartsheet.com/learning-track/best-practice-webinars

https://help.smartsheet.com/engage

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I worked up a self paced workspace for a coworker. I started with one basic function and left a checkbox for them to let me know when they were ready for me to check their work.

 

I would only add one piece at a time to help avoid "reading ahead" and causing confusion.

 

To determine each next piece, I had previously built out some rather complicated messes. From there I would give them one piece at a time from the first mess. Then I would give them the task of combining pieces (without actually telling them which pieces to combine). Eventually they end up building out an entire (rather complicated) solution. 

 

Then I re-word everything and tell them to give me a solution to a problem which ends up being very similar to the one they just pieced together except with a different context.

 

The practice sheet for the individual pieces were built out similar to the Smartsheet Formula Examples template with sample data for them to use.

 

I then used copies of other sheets I had built for them to get used to cell linking and cross sheet referencing and had them create reports, alerts and actions, dashboards, metrics sheets, and all kinds of crazy formulas.

 

I would never outright give them the answer if they were struggling, but I would give them hints.

 

The best advice I can give for learning formulas... Be patient, stock up on painkillers for the migraines and repair materials for the wall, think outside of the box, and start small.

.

Start small with EVERYTHING no matter how advanced. Break things down into as small of pieces as you can. Make sure each individual piece is working before putting it all together.

.

Thinking outside of the box comes with time. Need a number for a particular function? Sure... You can manually enter the number, but then you have to manually change it.

How do you automate a number? There are A TON of possibilities.

Cell references.

Is there a pattern that enables the use of COUNTIFS or SUMIFS?

Are you working from a table that allows you to use a MATCH function to generate a number?

That MATCH function needs specific data to look for. Can you automatically tell it "what" through cell references or other formulas/functions/cell references so that you don't have to manually enter data there?

Are you also working with dates? If so, can you use DAY(), MONTH(), YEAR(), WEEKNUMBER(), etc.... to automate the number in your first formula?

.

When you start small, you can answer all of these questions quickly and effectively and test/troubleshoot each piece individually.

Spread each piece out across multiple cells and use cell references to link them all together.

Make sure everything is working smoothly THEN you can nest following your cell references.

This makes very complex formulas very simple to build because you are only focused on the syntax of one at a time.

While breaking it out using cell references, you are already determining where to nest each piece within the main formula, so you can just follow the cell references when putting it all together.

MCorbin

Thank you Paul!!   This is a great outline.  I'm definitely going to keep this in mind when putting my training together   :-)

I couldn't agree more with breaking formulas up into individual components.  That's how I work also and it's really helpful.

Usually, when I'm trying to explain to someone how I put it together, I find a faster way - ha ha....

Paul Newcome

Happy to help!