[wintsway]

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

[Zack Barresse]

Are you just looking for your formula broken down and explained?

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

[wintsway]

Yes that would be a great start;

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

[Zack Barresse]

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.

[wintsway]

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

[Zack Barresse]

Glad to help. Don't forget to mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action.