1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

vlookup formula - storage tank volume

Discussion in 'Business Applications' started by wintsway, May 11, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. wintsway

    wintsway Thread Starter

    Joined:
    May 11, 2007
    Messages:
    3
    Hey there all,

    I'm looking to create a formula to be able to determine the volume of liquid in a storage tank. The tank is dip to determine the liquid height, then the formula needs to look at the tanks strapping points (only around 10 points with liters at 10 given points) and determine liters at that dip height. As the dip will 9out of 10 times fall outside the strapping points it need to find the average?

    I hope that makes sense? I have an example that works but I want to develop and understand how it goes together;

    =IF(ISBLANK(B12)," ",IF(MOD(B12,500) = 0, VLOOKUP(B12,TV1,2,1), VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1) + ((VLOOKUP((ROUNDDOWN(B12/500,0)+ 1) * 500,TV1,2,1) - VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1)) * (MOD(B12,500)/500))))

    Enter dip height (B12) and it returns liters at that point.

    Strapping points example;

    Tank 6
    mm Litres
    0 178195
    500 620360
    1000 1062524
    1500 1504689
    2000 1946853
    2500 2389021
    3000 2831325
    3500 3273629
    4000 3715933
    4500 4158236
    5000 4600537
    5500 5042750
    6000 5484964
    6500 5927178
    7000 6369391
    7500 6811601
    8000 7253750
    8500 7695899
    9000 8138048
    9500 8580197
    10000 9022346
    10500 9464466
    11000 9906568
    11500 10348669
    12000 10790771
    12500 11232873
    13000 11674976
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Are you just looking for your formula broken down and explained?

    Edit: And where are my manners, welcome to the board!
     
  3. wintsway

    wintsway Thread Starter

    Joined:
    May 11, 2007
    Messages:
    3
    Yes that would be a great start;

    I can see where the formula looks at another tab in the worksheet.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Well, it's enclosed in an IF() function, so there are three parts to it...

    IF condition test:
    ISBLANK(B12)

    IF true condition:
    " "

    IF false condition (1st):
    IF(MOD(B12,500) = 0, VLOOKUP(B12,TV1,2,1), VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1) + ((VLOOKUP((ROUNDDOWN(B12/500,0)+ 1) * 500,TV1,2,1) - VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1)) * (MOD(B12,500)/500)))

    .. breaking this one down further ..

    IF condition test:
    MOD(B12,500) = 0

    IF true condition:
    VLOOKUP(B12,TV1,2,1)

    IF false condition:
    VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1) + ((VLOOKUP((ROUNDDOWN(B12/500,0)+ 1) * 500,TV1,2,1) - VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1)) * (MOD(B12,500)/500))


    So far this is pretty straight forward. I think you're probably trying to get a grip on this last IF section (a False return on the first condition check and another False return on the second condition check). If this is the case, let's break down this last section a little more..

    ROUNDDOWN(B12/500,0)
    This will round the number down from B12 (towards the integer of zero) expressed as a whole number

    VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1)
    This will take the rounded down integer and multiply it by 500 again (apparently you are needing to take out any of the trailing decimals) and perform the Vlookup in TV1, return the second column under a non-exact match.

    I'm hoping that TV1 is a named range and you're not actually using the range TV1 (cell). Of course you would have to have version 2007 to do this as 2003 and prior do not even have this cell range as an option. But I felt I should mention it.

    (VLOOKUP((ROUNDDOWN(B12/500,0)+ 1) * 500,TV1,2,1)
    This Vlookup is taking the same rounded down integer, but adding a single digit to it and performing the same lookup. Basically it's getting the next highest number in the list.

    (MOD(B12,500)/500)
    The Mod function is kind of like a divisor function. This is the most confusion part of the function for me, that is if you are looking at using the numbers from column A (I'm assuming A, but the data which you posted) which are all in units of 500, which is your divisor part of the syntax of your Mod function. What that means in plain english is that the formula (Mod) will always return 0 if this is the case. This of course makes no sense, and if it is the case you may as well just have 0.

    I'm further assuming that since you are dividing it by 500 that you wouldn't intentionally set a 0 as the numerator portion of a division, hence you would get a #DIV/0 error. So you're more than likely not using a divisor of 500, but if you do that is where the error would be coming from.

    So the entire (what I'm referring to as) False/False portion of your formula looks like..

    VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1) + ((VLOOKUP((ROUNDDOWN(B12/500,0)+ 1) * 500,TV1,2,1) - VLOOKUP(ROUNDDOWN(B12/500,0) * 500,TV1,2,1)) * (MOD(B12,500)/500))

    So you're looking at the first entry found for your rounded down integer (second column returned value that is), then you're looking at taking the next highest value (from the second column again) and taking that away from the previous returned value (in other words finding the difference).

    This is the second part which doesn't make sense to me. If you are taking the first occurance and adding the difference between the first occurance and the second occurance, then you're basically returning the second occurance. I may be misunderstanding that, but if it is the case, you only need to return the second occurance (basically eliminating two Vlookup formulas you have there).

    Once you have that number you are multiplying it by the return of the Mod function.

    Hope this explanation helps. :)
     
  5. wintsway

    wintsway Thread Starter

    Joined:
    May 11, 2007
    Messages:
    3
    Thanks for taking the time to post the detail you have it's a great help.

    I love using Excel but I figure I'm using about 3% of it potential.

    Keep up the good work.

    Cheers, Jay
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Glad to help. Don't forget to mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action. :)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/572261