Help with a formula

I need to write a formula for Excel that calculates the amount of fuel that is in a tank.

Here is what it is for. We have cylinder tanks that lay horizontaly. Each month we put a dipstick into the tank to measure how many inches of fuel is left in the tank. I need a formula that will take that and calculate it into gallons. Here is the formula I have to work with.

/ -1 r - h \

V = L * | [ r^2 * cos (--------)] - [sqrt(2rh - h^2) * (r - h)] |

\ r /

Any Help is appreciated

(BTW I am not the best at math so if possible an explination of this formula would be greatly appreciated)

Microsoft ISV Community Center Forums1

Hey Tiger,

I never try to understand formulas like that... I just just code them up and give the test data and results to someone who does know and let them confirm the results are ok.

Public Function ToGallons(L As Double, r As Double, h As Double) As Double

ToGallons = L * ((r ^ 2 * Cos((-1 * (r - h)) / r)) - (Sqr((2 * r * h) - (h ^ 2)) * (r - h)))

End Function

I need for the formula to pull information from certain cells and then put the answer in another cell. I also need it to do it for 13 diffrent tanks.

My spreadsheet is laid out with 13 rows (one for each tank). If I put a number into lets say A1 I need the formula to take that number calculate the results and then put the answer into cell A2.

Could you help me with that

No worries Tiger,

Lets take the forumla I posted yesterday even if it might be wrong.

Public Function ToGallons(L As Double, r As Double, h As Double) As Double

ToGallons = L * ((r ^ 2 * Cos((-1 * (r - h)) / r)) - (Sqr((2 * r * h) - (h ^ 2)) * (r - h)))

End Function

If you paste that into a module you can call it from your sheet like a formula. If you go Insert->Function on Excel's menu, select User Defined in the category drop down list you'll hopefully see ToGallon. Select it, the dialog that appears will let you select a cell reference for L, r and h.

The function will be called with the values from the sheet and the answer will be returned and put back into the cell. You might want to polish up the function and add error handling but you can apply the same function to all your 13 tanks.

Hope thats what your needing.

Im going to try it out tonight. Ill let you know if it works. Thanks for the help.

Tiger

Sorry it took so long to reply. I tried what you wrote and I got this error.

Compile error

Statement invalid outside type block

It highlights the stuff in red.

Public Function ToGallons()

L As Double
r As Double
h As Double

ToGallons = L * ((r ^ 2 * Cos((-1 * (r - h)) / r)) - (Sqrt((2 * r * h) - (h ^ 2)) * (r - h)))

End Function

Not exactly sure what it means or what I did wrong. I tried putting it in just as you typed it but it didn't like that either.

HELP!!! LOL

You need to use the function exactly as given by Derek Smyth; just copy/paste the code into a module.
I've tried it and confirm that it works.

To use the function, on a worksheet enter values for L, r & h in A2, B2 & C2. In D2 enter =ToGallons(A2,B2,C2)

I got that to work now. However something in the formula is off. If I put in 10ft for length, 5ft for the radius, and .02ft for the height of fuel in the tank it comes out with 113.67. Now if i use the same length and radius then enter .05ft for the height of fuel in the tank it comes out with 102.26.

Something is definatly off but I think it is in the formula itself.

Hi tiger, just noticed you had a problem getting the formula to run, glad you got it sorted. The formula was never tested it was just to give you an idea of what it might look out.

Yeah I know. I do appriciate the help. All that is left is getting the formula right and I think it will work just fine. Again thanks for the help.

Tiger

PS Im glad to see that the forum isn't going to die off just because the buddy team can't help anymore. It is great to know there are people out there who are willing to help complete strangers with thier programming needs. =)

Ok I need help with one more thing (well I hope just one more thing LOL).

I am refrencing a mathmatical formula that is written in JavaScript. I've converted most of it except for two lines which I am unsure of how to create them in VBA.

var depth=1; depth<=f.diameter.value; depth++

var area=segment_area(depth, f.diameter.value)

f.diameter.value = d

segment_area = SA

Thanks!!!

Tiger

Hey man,

The line var depth=1; depth<=f.diameter.value; depth++

That's a loop in JavaScript, it translates to....

for depth = 1 to f.diameter.value

var area=segment_area(depth, f.diameter.value)

Thats just a method call....

Dim x as Integer

x = segment_area(depth, f.diameter.value)

the last line segment_area = SA I'm not that sure about to be honest. Post some more and put it into context and I'll work out.

Im sorry the segment_area = SA wasn't supposed to be in there. That is just a refrence for me for my code notes.

Here is the entire JavaScript code that I am trying to convert (or at least the section that I am trying to use).

dia=(f.diameter.value)

len=(f.length.value)

volume=Math.floor(dia*dia*3.141592/4*len/12/12/12*7.48)

for(var depth=1; depth <= f.diameter.value; depth++){

var area=segment_area(depth, f.diameter.value)

var vol=area*f.length.value/12/12/12*7.48

function segment_area(depth, dia) {

radius=dia/2

temp=radius-depth

chordl=2*Math.sqrt(2 * depth * radius-depth * depth)

ang=Math.acos(temp/radius)*2

arcl=ang*radius

return ((arcl*radius-chordl*temp)/2)

};// end segment_area

volume = I turned into a formula that resides in the spreadsheet.

radius, temp, chord1, ang, arcl and segment_area are in the VB script as public functions.

The ones highlighted in yellow are the ones I am having trouble understanding. Im not exactly sure what they mean or what relivance they have to the overall formula. Thanks again for the help.

Ok another problem i am running into is when I enter a public function for the formula and change the numbers in the cells that are refrenced for that formula it won't update.

Tiger, forgot to reply to this I'm sorry,

volume=Math.floor(dia*dia*3.141592/4*len/12/12/12*7.48)

Math.Floor is like a rounding function, it 'Returns the largest integer that is not greater than the argument',

floor(3.5) = 3

floor(-1.3) = -2

floor(4) = 4.

If your using Excel you have to use the FLOOR function like this...

answer = Application.WorksheetFunction.Floor(number, significance)

there really isn't any other equivalent in VBA that I know of.

for(var depth=1; depth <= f.diameter.value; depth++)

Thats is just a loop, for 1 to f.diameter.value step 1

{

var area=segment_area(depth, f.diameter.value)

thats calling a function and storing the value returned into variable 'area', looks like it might be calculating the surface area of a segment of a cylinder or pipe, maybe. ;)

function segment_area(depth, dia) {

thats like Public Function segment_area(ByVal depth as Double, ByVal dia as Double)