Formula Tips

Jessica Howell

Hello! Looking to create a binary number system, due to the fact that dashboard charts must be created with numeric values. Ideally we would have a separate sheet, "the binary copy" and a seperate sheet with all of the original data. For example, if we have yes is entered on the main sheet, a 1 will appear on the binary copy, and if its no, a 2 will formulate.

Additionally, a second sheet may be too complicated, thinking additional columns could be better, just looking for feed back and help with formulas

Thanks!

Matt Johnson

Hi @Jessica Howell

Most of the dashboards I make have a "metric sheet" that basically summarizes the data from the main sheet using cross sheet formulas. If you provide a little more info or maybe some screen shots of what you are trying to create I could provide a little more assistance. However it sounds like you're on the right path already.

I hope that helps!

Jessica Howell

Hi @Matt Johnson,

That is exactly what I am trying to do!

I have attached a picture below of some drop down examples

Jessica Howell

Hello @Matt Johnson.

Do you have any ideas?

Thanks!

Matt Johnson

Hi @Jessica Howell

I was thinking maybe something like this:

The Howell data sheet would be your main tracker and the metric sheet would only need to be updated when a new Impact type gets added. Or you could datamesh that in with the Copy and Add Data feature. That might be overkill though. Formulas are below, nothing fancy.

=COUNTIFS({Howell Data Range 3}, Impacts@row, {Howell Data Range 2}, "Yes") this is the 2nd column above.

=COUNTIFS({Howell Data Range 3}, Impacts@row, {Howell Data Range 2}, "No") this is the 3rd column above.

From here you could build a Yes / No Graph or separate charts per Impact. Obviously these are the easy ones. Maybe if you have an Open or Closed Status column you could add a graph for say Reportable Impacts in the Asphalt category that are Open issues. Or, of all Open issues, how many are in each Impact group that are reportable. Those are pretty easy COUNTIFS formulas and making the graphs with the dashboard widget wouldn't be too difficult. Sometimes I put metrics together or graphs based on what I think would be beneficial and then I take feedback from my Team and update the Dashboard so it works for everyone. I hope this helps. On a side note though, your formulas may differ from mine if you have multi-select drop downs.

Jessica Howell

Hi @Matt Johnson

These 2 fields don't play into each other at all, and there are far more than this.

Can this number system be generated from the impact criteria on its own?

Jessica Howell

@Matt Johnson any ideas?

Genevieve P.

Hi @Jessica Howell

Are you just looking to receive a COUNT from how many each Impact is selected throughout the other sheet, regardless of if it says Yes/No?

If so, you could adjust Matt's formula above to just look for the one criteria:

=COUNTIF({Howell Data Range 3}, Impacts@row)

However I notice that you're using a Multi-Select dropdown, so you'll want to add in HAS, so it can find this criteria even if the selection is with another Impact:

=COUNTIF({Howell Data Range 3}, HAS(@cell, Impacts@row))

You could use the same formula for a count of how many say Yes or No:

=COUNTIF({Howell Data Range 2}, HAS(@cell, "Yes / Oui"))

If you don't want an overall COUNT but you're looking to assign a value to either Yes or No, then you can add in a helper column to your source sheet and have an IF statement that assigns values:

=IF(Reportable@row = "Yes / Oui", 1, IF(Reportable@row = "No / Non", 2, 0))

You can drag-fill this down the entire helper column and it will assign either a 1, 2, or 0 to the row, depending on the selection in the Reportable column. Does that make sense?

Should none of this work for what you're looking to do, it would be helpful to see a bigger screen capture of your sheet (but please block out sensitive data) and a more detailed explanation of the type of data you are looking to extract, and what the end goal is (for a chart in a Dashboard? Or a total?).

Thanks!

Genevieve

Jessica Howell

@Genevieve P Thank you for your help! I will definitely try some of these

All of the fields aren't based off of a yes or no. I have filled some random fields, most of them drop- downs

Looking for something like this

diesel fuel- 1

grease- 2

solvent-3 etc... but the numbers will populate on my metric sheet into the same field

Then i can use this numeric data to create my dashboard

Genevieve P.

Hi @Jessica Howell

Can I ask what you are mapping, though? For example, why do you need Solvent to be 3... what does that represent? What type of chart are you looking to build?

Chart widgets need numeric data, yes... but you have numeric data in your Quantity column, so if you wanted to chart the quantity of each Substance you don't need to change their titles into numbers. Does that make sense? The X Axis could be the Substance, and the quantity could be bars, with the number on the Y axis.

I will note that it may not work off of a Multi-Select field, so that Substance column may need to be single-select.

Let me know what your tests return and I'm happy to help further!

Cheers,

Genevieve

Jessica Howell

It was just an example, it doesn't need to be

This is quite a big project with many different charts, but more tables then anything.. (examples below)

This data is currently in excel, the team I am working for wants it to be converted to smart-sheet to allow them to be more "hands off".. but I am slowly coming to the realization that it could be far more work

I have extensive knowledge of smart sheet, but little to no experience with dash-boarding, so this is all new to me

Appreciate all the guidance

Genevieve P.

Hi Jessica,

This is great! So that first metric sheet can easily be created by using the exact same formula Matt suggested above, a COUNTIFS.

Ex: Number of leaks by Refrigerant type:

=COUNTIF({Source Sheet Refrigerant Type}, HAS(@cell, [Column 2017]@row))

Column 2017 would be where the Refrigerant type (ex. R404a) is typed out. Then you can drag this formula down the Q1 column and it will adjust per type. This will auto-update as new rows are added to the source sheet.

Now if you need this to also be broken down by Quarter, you'll need to have a Date Column in that source sheet somewhere. Then we can add in criteria to look between two dates (ex. Jan 1st - March 31st).

All of these calculations are possible, however it will require your source sheet having the identifiers or criteria somewhere in each row. So for your second report, it says "Number of Reportable Leaks". Somewhere there needs to be an identifier that shows whether or not it's reportable so that we can add this into a criteria for your COUNTIFS formula.

It's quite hard to give general formula suggestions and help as there are so many possible variables and solutions. I would suggest first deciding exactly what you want to see on the Dashboard, then go back to your source sheet and ensure there's a way to identify all these elements within each row. Once you have those two elements prepped, then you can set up an in-between calculations sheet to create formulas to gather the data. This is where the Community can help best... when there's a specific ask of "how do I create this calculation from this sheet to the other sheet, based on these criteria".

You may also want to review some of our webinars on Dashboards and Formulas as well:

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

Formulas: https://help.smartsheet.com/learning-track/best-practice-webinars/formulas-webinar-series

Hope this helps!

Cheers,

Genevieve