cubic yard IF formula

[gwoods]

i have 3 cells that multiply to calculate cubic yds (W x L x Depth)


I need a formula that will show lineal feet when L is populated and W & D are 0s. or show square foot W & L are populated and D is 0 or show cubic yards if W & L & C are all populated

 

[NARAYANK991]

Hi ,


The formula can be simplified if instead of the unused cells containing 0 , they are left blank.


Suppose your data is in the range F6 , G6 and H6 , where F6 contains the length , G6 contains the width and H6 contains the depth , then in any other cell , put in the formula :


=PRODUCT(OFFSET(F6,,,,COUNT(F6:H6)))


If any of the unused cells G6 or H6 contains 0 , then this formula will not work.


Narayan

 

[Hui]

Gwoods


Firstly, Welcome to the Chandoo.org forums


Assuming

A1: Width

A2: Length

A3: Depth


Then use:

=IF(AND(A1="",A3=""),A2,IF(AND(A1<>"",A2<>"",A3=""),A1*A2,A1*A2*A3))


or


=IF(AND(A1="",A3=""),A2&" m.",IF(AND(A1<>"",A2<>"",A3=""),A1*A2&" sq m.",A1*A2*A3&" m^3"))

 

[gwoods]

thanks guys the formulas work except that i failed to give you all the parameters. first off the width and length will be in feet and the depth will be in inches. so, the formula would normally look like this (A1*B1*(C1*12))/27


could you please help times the depth by 12 and divide it all by 27 to calculate a cubic yard.

 

[gwoods]

oops failed again; i meant (A1*B1*(C1/12))/27

 

[Luke M]

Restating just to be sure:

Width (W) in ft

Length (L) in ft

Depth (D) in in.


To convert depth using 12 in per 1 ft, we do D/12. Multiplying the 3 like so, W*L*D/12 gives us our value with unit of cubic ft. To convert ft to yd is a 3 to 1 ratio, so we divide by 3^3 (27). Formula then:

=(W*L*D/12)/27

Going with your cell references, it becomes:

=(A1*B1*C1/12)/27

This can be simplified to either of the following:

=PRODUCT(A1:C1)/324

=A1*B1*C1/324

=(A1*B1*(C1/12))/27

 

[gwoods]

perfect thanks