+ Reply to Thread
Results 1 to 4 of 4

Calculation Problem

  1. #1
    Forum Contributor
    Join Date
    04-11-2008
    Posts
    156

    Calculation Problem

    Hi there,

    I have no idea what function I need for this but I am hoping there is a quick way to do the calculation without creating a table...

    I am trying to estimate the cost of warehousing stock for a set period. To simplify what I am trying to calculate:

    If we use 10 items per week for 5 weeks and each item costs £1 per week to store then the total cost for storage over the period would be:

    Order Stock Storage
    Start - 50 -
    week1 10 40 £40
    week 2 10 30 £30
    week 3 10 20 £20
    week 4 10 10 £10
    week 5 10 0 £0
    TOTAL 50 100 £100

    Can his be done in a single formula without having to create a table?

    Thanks

  2. #2
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Create a function

    Hi dude,

    I created afunction in VBA for this:

    Please Login or Register  to view this content.
    This allows you to use the "=Stock(10,2,1.25)" like you would the =sum function.

    i would then simply add them like so

    =STOCK(10,5,1.25)+STOCK(15,2,4.55) etc etc

    Hope that helps

    Thought i'd give yo this idea as i'm currently learning more about custom functions!!

    If you need help adding this to VBA then let me know
    Mr MaGoo
    Magoo.Inc MMVII

    If i've helped please add to my Rep by Clicking on the Blue Scales in the top right hand corner of the post

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If you have the start number of items [50] in B1, number used per week in B2 [10], number of weeks [5] in B3 and the cost for storage in B4 [1] then you can get total storage cost with this formula

    =(B1-B2*(1+B3)/2)*B3*B4

    B3*B2 shouldn't exceed B1

  4. #4
    Forum Contributor
    Join Date
    04-11-2008
    Posts
    156
    Thanks both

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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