Yesterday's Data and Rolling 7 Day Avg Data

Colin C.

Hi - I am all-new to Smartsheet and have hunted high and low for the answer to these questions.....with no joy. When I think I am onto something the formula always doesn't work, so i thought i would ask directly...

I have a sheet that we update daily and want to create a summary at the bottom with two bits of info. This is then sucked into a dashboard for senior management reporting.

The sheet is prepopulated with dates and we add in daily sales and cumulative sales in the month.

What i need is the sheet to automatically grab yesterday's InDay number and separately calculate the rolling 7 day average InDay sales.

I would like to drop them onto a couple of cells at the bottom as illustrated and update automatically when the new numbers are added in daily.

The correct answers i need drop into the box would be 193 and 144 respectively.

Is this do-able in Smartsheet?

Thanks

Colin.

hollyconradsmith

Hi @Colin C. ,

This sounds like a great opportunity to use sheet summary reporting capabilities. Check out this article for how: https://help.smartsheet.com/learning-track/level-2-intermediate-users/sheet-summary

Colin C.

Thanks Holly - I had already looked at that in some detail and am afraid the 52sec video left me more bewildered...! it raised more questions than answers! V frustrating. It didn't explain how to do anything, just that this sheet summary reporting existed. as I said, I am a very novice user of Smartsheet exploring its possibility; if it does what i hope I want more of our team to explore its capabilities, but i don't think it has this capability from all my research.

I am interested in knowing if it can be doe on the sheet as i set out and if so how.

Many thanks for the idea though!

hollyconradsmith

Hi @Colin C.

You could review the Smartsheet functions list as well. That should get you well on your way to Smartsheet stardom. 🤗

For Yesterdays Sales you could use:

=SUMIF([InDay Sales]:[InDay Sales], Date@row<=Today(-1))

For the 7 day average, you could use:

=AVERAGEIF(date:date, Date@row<=Today(-7), [InDay Sales]:[InDay Sales])

https://help.smartsheet.com/functions

https://help.smartsheet.com/function/averageif

https://help.smartsheet.com/function/today

https://help.smartsheet.com/function/sumif

A review of how to use functions would probably be helpful to get you started on your journey!

https://help.smartsheet.com/learning-track/level-1-get-started/formula-basics

Colin C.

Hi Holly

Thanks for the pointers - I am very grateful!

I have looked at many of these functions, however the difficulty is getting the actual formulas right.

I used the two formulas that you shared. Both delivered a number, however I think it may be pointing in the wrong place?

On the first it delivered a '0'. I checked that my date field is a date and not text/ number and it is, so I can't understand why it is not displaying. I thought it may be due to today being a day further on from yesterday, however I inserted some data into the row for 9/12 and still get '0'.

On the second it showed a result of "170.333", which is the average of the top three lines in the spreadsheet rather than the most recent seven that hold data. I just copied and pasted - it seems to be correct in the cell and the logic in the formula makes sense to may untrained eye.

It feels like you have provided the solution, as they didn't error, just that it may be looking n the wrong place!

thanks again for any help you can share.

Colin.

hollyconradsmith

Hi @Colin C. ,

My apologies, revise yesterday's sales to =SUMIF([InDay Sales]:[InDay Sales], Date@row=Today(-1))

I had instead of =. That is probably causing the problem.

Flip the direction of the < in the other formula.

Take some time to work through some functions on your own, too! That's how we all learned. Then soon, you will be answering these questions on the forums as well. 🤩

hollyconradsmith

This might help, too:

Smartsheet formulas template

https://app.smartsheet.com/b/home?sc=mjjH3-k10qQ%2CmwFH3yU133o%2C1&amp;_&_ga=2.264255625.785462730.1638912146-314738254.1627559905

Colin C.

Thanks Holly - this is all very helpful. I managed to pull the numbers I was hoping.

Much appreciated.

Colin.