How do I sum cell values in a column up to a specific sum, and return the adjacent cell value?

ewestesen · 04/23/24

First time posting a question, fairly new to smartsheet. I have a spreadsheet in googlesheets that does what I need, but as we are moving over to smartsheet with a lot of our other work, I would like to get this set up in smartsheet as well.

We grow sweet corn, I have data that I use to make planting schedules and predict and model harvest dates. Each variety takes different amounts of heat to mature and be ready to harvest. Additionally, the earlier you plant, the longer it takes to get to harvest. I have averages for every variety by planting. I can take current weather data and use that to calculate how far along a variety is. I want to take the difference (how much further it has to go) and return the date at which it will reach full maturity based on predicted weather information.

The unit we are working in is Growing Degree Days (GDD). I want to take the current total GDD, and find how many days it will take to get to the desired GDD to be ready to harvest. As the days get longer, the daily GDD value gets higher.

I have two columns, dates and daily GDD values. I need to find a date in the column, and find how many days predicted GDD it will take to reach a total value, and return that date. I'll link to my google sheet here if it helps, though I'm using XMATCH and as far as I can tell there isn't any good equivalent in smartsheet (I would love to be corrected).

In the example on the 'GDD Import' tab- I have 1339 GDD to go. Starting from todays date (4/22) it will take until 7/16 to accumulate enough GDD to be ready to harvest. I went through multiple steps working it out in google sheets, and you can see the versions as I progressed. I have found so many advantages to using smarthsheet, and would love to pull this across to integrate with other things we are doing.

https://docs.google.com/spreadsheets/d/1im0ZVn_jjw-f8SeH6q_W7gvvYMyxLzBaVgo-GLC9zcQ/edit?usp=sharing

Bassam Khalil · 04/23/24

Hi @ewestesen,

please try the following:

1-ensure that your current Google Sheets data is imported into Smartsheet. You can do this by:

Exporting the Google Sheet to an Excel format.
Importing the Excel file into Smartsheet.

2-Create two columns in Smartsheet, one for dates and one for daily GDD values similar to your Google Sheets setup.

3-In Smartsheet, you will need to calculate the cumulative GDD up to each date. Since Smartsheet does not have a direct equivalent to Excel's XMATCH, you’ll use a combination of helper columns and functions:

Cumulative GDD Column: Use a formula in this column to sum GDD from the start date to the current row. You can achieve this using the SUM function in a formula that references the top cell of the GDD column and expands down to the current row.

=SUM([Daily GDD]$1:[Daily GDD]@row)

4-To find the first date where the cumulative GDD meets or exceeds the target GDD, use the following formula:

=INDEX([Date Column]:[Date Column], MATCH(TARGET_GDD, [Cumulative GDD Column]:[Cumulative GDD Column], 1))

5- Smartsheet's strength lies in its ability to integrate and automate workflows:

Automation: Set up alerts, reminders, or requests that trigger when certain conditions are met (e.g., when GDD reaches a certain threshold).
Integration: Use Smartsheet’s connectors to integrate with weather data APIs or other systems to pull in real-time data automatically.

Bassam Khalil · 04/23/24

Hi @ewestesen,

please try the following:

1-ensure that your current Google Sheets data is imported into Smartsheet. You can do this by:

Exporting the Google Sheet to an Excel format.
Importing the Excel file into Smartsheet.

2-Create two columns in Smartsheet, one for dates and one for daily GDD values similar to your Google Sheets setup.

3-In Smartsheet, you will need to calculate the cumulative GDD up to each date. Since Smartsheet does not have a direct equivalent to Excel's XMATCH, you’ll use a combination of helper columns and functions:

Cumulative GDD Column: Use a formula in this column to sum GDD from the start date to the current row. You can achieve this using the SUM function in a formula that references the top cell of the GDD column and expands down to the current row.

=SUM([Daily GDD]$1:[Daily GDD]@row)

4-To find the first date where the cumulative GDD meets or exceeds the target GDD, use the following formula:

=INDEX([Date Column]:[Date Column], MATCH(TARGET_GDD, [Cumulative GDD Column]:[Cumulative GDD Column], 1))

5- Smartsheet's strength lies in its ability to integrate and automate workflows:

Automation: Set up alerts, reminders, or requests that trigger when certain conditions are met (e.g., when GDD reaches a certain threshold).
Integration: Use Smartsheet’s connectors to integrate with weather data APIs or other systems to pull in real-time data automatically.

ewestesen · 04/23/24

Thank you! I was thinking to complicated. I am still learning how to creatively use helper columns, and it really simplifies things. A perfect solution.

Formulas and Functions

Formulas and Functions

How do I sum cell values in a column up to a specific sum, and return the adjacent cell value?

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Index-Collect

Formula Order of Operations Problem

Partial Formula Working