Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Formulas for tank volume calculation by using DATE

  1. #1
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formulas for tank volume calculation by using DATE

    I need to make a sheet that will let me know the volume in a container by using the date and i don't know how to proceed. I have columns with the date the container was last filled and the volume on that day, a column for the rate of consumption per day, a column for what is the volume today, a column for what the volume will be in x number of days (currently 7) and a column for the date that the volume will be 40 liters or less. I had tried this before at my previous job and had some help with this on this forum but am unable to find the post. Also, I am using Excel 2003 at this position and it doesn't have as much built-in help on building formulas. Any help is appreciated

  2. #2
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    I see there have been many views but no replies as of yet so maybe more info is needed.
    F=date filled
    G=Volume
    H=Rate
    I=Current volume today
    J=volume in x days
    K=date volume is < 40 liters

    Thanks for any help

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    E
    F
    G
    H
    2
    date filled
    04/17/2014
    F2: Input
    3
    capacity
    1000
    liters F3: Input
    4
    rate
    29
    liters/day F4: Input
    5
    current vol
    652
    liters F5: =F3 - (TODAY() - F2) * F4
    6
    x
    5
    days F6: Input
    7
    volume in x days
    507
    liters F7: =F3-(TODAY() + F6 - F2)*F4
    8
    date vol = 40
    05/20/2014
    F8: =F2 + (F3-40) / F4

  4. #4
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    By using the table above I have entered the formulas and I return #value for all of them. I don't know how to enter a table here like you have above but hopefully I can explain it good enough. For Current Volume column I I have =G2(TODAY()-F2)*H2. For Volume in 7 days column J I have =G2-(TODAY()+7-F2)*H2. For Date Volume is <40L I have =F2+(G2-40)/H2. Am I close to having them right or did I totally misunderstand your table?

  5. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    For Current Volume column I I have =G2(TODAY()-F2)*H2.
    Multiplication is not implicit in Excel: =G2*(TODAY()-F2)*H2

    But why not just use the formulas I used as a confidence-builder, and then make changes from there?

  6. #6
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    My sheet is setup differently than the one you posted. The items you have entered in the E column are all the items I have entered as individual columns F to K so the formulas don't work as they are above. I saved my sheet as a jpeg to better show what I need to do but it is asking for a URL when I try to insert image in this post.

  7. #7
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    You could enter the data as I have, and then drag the formulas to the cells where you want them.

    If you had posted your actual layout, I would have used it.

  8. #8
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    I've tried to post my layout but can't figure out how to, I can't paste the image into this reply. I put in the multiplication symbol like you added and am still get #value in all the cells. this the order of items I have in my sheet F=date filled
    G=Volume
    H=Rate
    I=Current volume today
    J=volume in x days
    K=date volume is < 40 liters

  9. #9
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,489
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    How about this?

    F
    G
    H
    I
    J
    K
    1
    date filled
    capacity [liters]
    rate [liters/day]
    vol today [liters]
    vol in x days [liters]
    date vol = 40
    2
    04/17/2014
    1,000
    29
    623
    478
    05/20/2014
    3
    4
    F2: Input G2: Input H2: Input I2: =G2 - (TODAY() - F2) * H2 J2: =I2 - J7 * H2 K2: =F2 + (G2-40) / H2
    5
    6
    x [days]
    7
    5
    8
    J6: Input

  10. #10
    New Member
    Join Date
    Feb 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formulas for tank volume calculation by using DATE

    Thanks for hanging in there helping me but I am still getting #value for all results. I don't have any spaces in the formulas I have entered, should there be some?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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