Formula to Calculate the Volume of a Log

[klofutaric]

Hi guys,

I obviously need some help.

I insert diameter of the log in cell a1 (lets say 40) and lenght of the log (lets say 400) in cell b1. In cell c1 I want to calculate volume of the log.

So the formula for the volume is (3.14*a1*b1)/100000.

But the value of cell a1 is changing, if the value is equal or less then 26 then the formula have to deduct 1 from the a1 cell value, if value is equal 27 but equal or less then 50 deduct 2, if value is equal or greater then 51 deduct 3.

So when I insert number 40 in cell a1 and insert number 400 in cell b1 I should calculate (3.14*38*400)100000, because from number 40 I have to deduct 2.

I hope this is understandable,

Thanks for your help

[AliGW]

Here you go:

=(PI()*(A1-LOOKUP(A1,{0,27,51},{1,2,3}))*B1)/100000

[Ghozi Alkatiri]

=pi()*(a1-match(a1,{0,27,51},1))*b1/100000

[klofutaric]

Sorry, that doesn't work, I get this

Brez naslova.png

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AliGW]

Probably your locale. Try this:

=(PI()*(A1-LOOKUP(A1;{0,27,51};{1,2,3}))*B1)/100000

I have replaced commas with semi-colons.

[klofutaric]

Sorry, another message

first I get this, change value in {} brackets

Brez naslova.png

when i click no this pops up

Brez naslova1.png

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[AliGW]

So did it occur to you to try this?

=(PI()*(A1-LOOKUP(A1;{0;27;51};{1;2;3}))*B1)/100000

[klofutaric]

Yes I tried to do this, but I didn't change a1 cell to a4, so it didn't work, stupid me

Thank you very much

[AliGW]

At last!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[klofutaric]

Hi guys,

So the basic formula for calculating volume of the log is (PI*((D/2)^2)*L)/1000000, D=diameter of log, L is lenght of log. My previous formula in my first post was (3.14*D*L)/100000, but is wrong because formula for calculating circle area is pi r(squared)2.

I tried to change formula to (PI()*(((B4/2)^2)-LOOKUP(B4;{0;27;51};{1;2;3}))*C4)/1000000, but this formula does't substract (1,2,3) from diameter.

For example result for diameter 60 and lenght 400 is 1,13 m3, but it should be 1,02, because formula should use diameter 57 (60-3=57).

Sorry for inconvenience,

Regards, Klo

[XLent]

So, I *think* you would just modify

B4/2

to

(B4-MATCH(B4;{0;27;51}))/2

with the above change in place you would get 1.016934 for 60x400

edit: I suspect, in fact, that's not quite correct...i.e. should remove the LOOKUP piece from earlier iteration - would return 1.020703

=(PI()*(((($B4-MATCH($B4;{0;27;51}))/2)^2))*$C4)/1000000