Week Over Week Change

Cristian

I have a sheet that has orders entries from the last 6 months. This sheet continues to populate as more orders are made. This is just my repository sheet.

I am looking to get the week over week change metrics for the last 7 days. Here's the formula that can provide me that week over week change:

Week over week %% = ((current week - previous week) / previous week) x 100

I am not sure how to insert a formula that will provide me that info for the last 7 days. I would like this to appear in one cell and changes as changes are made in the main sheet.

Any help would be grateful. Thanks

Cristian

I GOT IT.

=(COUNTIFS({Project}, @cell <> "", {Created}, AND(@cell >= TODAY (-7), @cell <= TODAY())) - COUNTIFS({Project}, @cell <> "", {Created}, AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))) / COUNTIFS({Project}, @cell <> "", {Created}, AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))

Since I am referencing another sheet, no need to add [Project]:[Project].

@Ashley Knight / @Genevieve P. Thank you both to getting me to this point.

Thanks again. 🙂

AKnight

Hi @Christian !

Are you defining a week as the last 7 days? If so try the formula below:

=(SUMIFS([days worked]:[days worked], [False created]:[False created], AND(@cell >= TODAY (-7), @cell <= TODAY())) - SUMIFS([days worked]:[days worked], [False created]:[False created], AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))) / SUMIFS([days worked]:[days worked], [False created]:[False created], AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))

Then I would switch the cell type to percentage in the number formatting. There might be a more concise way of doing this, but this will work.

However, if you are looking at weeks with Saturday being 1, Monday as 2, etc. and excluding any days prior to that Saturday as the "current week", I would do some reformatting using the WEEKDAY function.

Cristian

Thanks for the info.

I have a column called Created Date but not days worked or false created. Would I need to create those two columns as well? Yes, this is looking at the last 7 days.

AKnight

Oh shoot that's embarrassing, I took this off my tester sheet and forgot to remove the test names. Try this instead using the following column types:

If doing cost change:

Cost: Text/Number Column Type with the cost of the order

Created: Created Date Column Type

=(SUMIFS([Cost]:[Cost], [Created]:[Created], AND(@cell >= TODAY (-7), @cell <= TODAY())) - SUMIFS([Cost]:[Cost], [Created]:[Created], AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))) / SUMIFS([Cost]:[Cost], [Created]:[Created], AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))

If doing number of orders I would change SUMIFS to COUNTIFS and then you can use the primary column name instead of the cost column.

AKnight

Additionally if you need the number of orders and switch to COUNTIFS, your format will need to change a little, like so:

=(COUNTIFS([Order]:[Order], @cell <>"", [Created]:[Created], AND(@cell >= TODAY (-7), @cell <= TODAY())) - COUNTIFS([Order]:[Order], @cell <>"", [Created]:[Created], AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))) / COUNTIFS[Order]:[Order], @cell <>"", [Created]:[Created], AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))

Assuming your primary column is named "Order"

Cristian

Thanks for the info. I don't have an Order column. Would it be worth creating one? I do have a Created column. I think the COUNTIFS would work best.

AKnight

You don't have to! I just assumed your primary column was named "Order", you can substitute the name "Order" in the formula with what ever your primary column is named. I included brackets in the formula so even if your primary column's name has spaces it will still work

Cristian

Ah, ok. Here's my formula but I get #unparseable error.

=COUNTIFS([Project Name]:[Project Name], @cell <>"",[Created]:[Created], AND(@cell >=TODAY (-7),@cell <=TODAY()) - COUNTIFS([Project Name]:[Project Name], @cell <>"", [Created]:[Create], AND(@cell >=TODAY (-14),@cell <=TODAY(-8)))) / COUNTIFS([Project Name]:[Project Name],@cell <>"", [Created]:[Created], AND(@cell >=TODAY (-14), @cell <=TODAY(-8)))

AKnight

Try this (removed some spaces, fixed some spelling):

=(COUNTIFS([Project Name]:[Project Name], @cell <> "", [Created]:[Created], AND(@cell >= TODAY(-7), @cell <= TODAY())) - COUNTIFS([Project Name]:[Project Name], @cell <> "", [Created]:[Created], AND(@cell >= TODAY(-14), @cell <= TODAY(-8)))) / COUNTIFS([Project Name]:[Project Name], @cell <> "", [Created]:[Created], AND(@cell >= TODAY(-14), @cell <= TODAY(-8)))

Cristian

I still get #unparseable

AKnight

Some trouble shooting questions:

• Did you directly copy the formula above
• Is your Created column a titled "Created date" Column type and is it named "Create" or "Created"
• There are no spaces after your TODAY function and well as no spaces in your TODAY()
• [Project Name] is a text/number column type
• Parenthesis have been added around the subtraction portion of the function

Cristian

Yes. I copied the formula directly to my sheet.

Yes. My Created column is named Created.

Yes. There are no spaces after TODAY

Yes. [Project Name] is a text/number and its also a Primary Column.

Yes. Parenthesis have been added around the subtraction portion of the function.

Genevieve P.

Hi @Cristian

Can you post a screen capture of the formula open in the cell (showing the different colours) and the column names being referenced? Like this:

But please block out any sensitive data.

Thanks!
Genevieve

Cristian

For some reason its not referencing my origin sheet.

Cristian

Forgot to indicate that I am also doing this in a Metrics Sheet and not the origin sheet aka my repository sheet where all my orders go to.

Cristian

I GOT IT.

=(COUNTIFS({Project}, @cell <> "", {Created}, AND(@cell >= TODAY (-7), @cell <= TODAY())) - COUNTIFS({Project}, @cell <> "", {Created}, AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))) / COUNTIFS({Project}, @cell <> "", {Created}, AND(@cell >= TODAY (-14), @cell <= TODAY(-8)))

Since I am referencing another sheet, no need to add [Project]:[Project].

@Ashley Knight / @Genevieve P. Thank you both to getting me to this point.

Thanks again. 🙂