+ Reply to Thread
Results 1 to 3 of 3

Calculating Stock Changes Per Day

  1. #1
    Registered User
    Join Date
    04-28-2021
    Location
    London
    MS-Off Ver
    2011
    Posts
    2

    Calculating Stock Changes Per Day

    I'm working on a stock list and would like to calculate the stock difference day by day.

    I'm facing these issues.

    Scenario 1:

    Now every day I need to copy and paste the formula for the difference of the total stock from 2 different sheets.

    Here's the simple formula after the total stock is totaled.

    =Sheet_2!C1-Sheet_1!C1

    Now when I create sheet 3, I need to copy the formula from sheet 2 and copy it on sheet 3. Now the formula I copied won't be automatically updated, it will be

    =Sheet_2!C1-Sheet_1!C1

    and I need to udpate it to

    =Sheet_3!C1-Sheet_2!C1

    Is there a formula that will automatically update it to the new sheet names?

    E.g., when I create Sheet 3 then, it'll be

    =Sheet_3!C1-Sheet_2!C1

    E.g., when I create Sheet 4 then, it'll be

    =Sheet_4!C1-Sheet_3!C1


    Scenario 2:

    The stock units list changes every day depending on the sales.

    E.g.,

    Sheet 1 (Day 1) - Sorted by descending order of stock

    Oranges 30 units remaining
    Apples 15 units remaining
    Pears 12 units remaining

    Sheet 2 (Day 2)

    Oranges 30 units remaining
    Pears 11 units remaining
    Apples 7 units remaining


    Sheet 3 (Day 3)

    Apples 7 units remaining
    Pears 6 units remaining
    Oranges 5 units remaining

    This is a table with many different columns with different data (e.g., item name, SKU, stock, stock value, overstock, etc. 19 different fields).

    What is the formula for change in stock when the items keep moving up and down the table given different sales volumes, also due to errors in the data source sometimes the items disappear completely so a direct formula wouldn't work?


    This is what I did before but it wasn't practical given the issues above.

    1. On Sheet 3, I created a cell that got the stock from the previous day with the formula =VLOOKUP(Day_3!A3,Day_2!3:3,3,0)
    2. I used this formula to calculate the change in stock =(C3-T3)/C3

    This doesn't really work as the items on the list change order every day depending on the sales.

    What formula can I use to solve this issue?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Calculating Stock Changes Per Day

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-28-2021
    Location
    London
    MS-Off Ver
    2011
    Posts
    2

    Re: Calculating Stock Changes Per Day

    It's a work-related file so I didn't add any images.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Adding & average calculating stock
    By anu0512 in forum Excel General
    Replies: 0
    Last Post: 02-19-2019, 01:28 AM
  2. calculating out of stock dates
    By Lydia1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2018, 08:48 AM
  3. Calculating running total for in lab stock
    By ltelford51 in forum Excel General
    Replies: 10
    Last Post: 11-17-2015, 10:11 AM
  4. Help calculating stock investment returns vs S&P 500
    By mattmcg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-23-2013, 02:12 PM
  5. Calculating future stock values...
    By bpolli in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-25-2008, 05:23 PM
  6. calculating the volatility of a stock
    By Richard M 2006 in forum Excel General
    Replies: 1
    Last Post: 05-09-2006, 05:00 PM
  7. calculating days of stock
    By Cor in forum Excel General
    Replies: 1
    Last Post: 05-06-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1