Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## jerseydevil1387

2 posts

Wannabe Geek

 Topic # 96970 8-Feb-2012 13:04 Hi Everyone, I am currently trying to set up a spreadsheet for work which is able to calculate the area or the volume dependent on if the relevant categories are filled. The spreadsheet is set up with Column A being the Item, Column B as the Length, Column C as the Breadth, Column D as the Depth and Column E as the Quantity. I need to be able to calculate the area if Column B and C are filled and the area if Columns B,C and D are filled. I cannot figure it out >< Can someone please assist? Thank you!

## jonherries

863 posts

Ultimate Geek

 Reply # 578675 8-Feb-2012 13:10 =if(D1=""),C1*B1*E1,D1*C1*B1*E1) Assuming you start at row one and don't have a header. Jon

## jonherries

863 posts

Ultimate Geek

 Reply # 578677 8-Feb-2012 13:11 sorry remove the second parentheses like this: =if(D1="",C1*B1*E1,D1*C1*B1*E1)

## nzkc

303 posts

Ultimate Geek

 Reply # 578681 8-Feb-2012 13:16 As above, or.... =(IF(D1="",1, D1)*C1*B1*E1) Which is the same thing really...just replace "blank" with 1. Could also do =IF(D1=0, 1, D1)*etc

## NonprayingMantis

5927 posts

Uber Geek

 Reply # 578689 8-Feb-2012 13:32 It would also be sensible to format the output cell so that you can see whether it has calculated an area or a volume. You can either use conditional formatting on the cell itself to show the result as [output]m^2 or [output]m^3 depending on what you calculate, or (and this will be easier) insert a new column after A and put in the formula If(E1="","AREA","VOLUME") so when your depth column is blank, it puts AREA in row B and if the dpth column has a value, it puts in VOLUME (E1 is were you have the depth cell. It would be moved from D to E when you insert a new column)

## jerseydevil1387

2 posts

Wannabe Geek

 Reply # 578697 8-Feb-2012 14:00 Thank you guys sooo much. You are all superstars. It ended up being slightly different, but your formulas helped me figure it out. Cheers! Just a quick correction (my fault for not being clearer in the original post), E = quantity or the total, so that is where I want the area/volume to appear. Also, I needed to get the area for walls in a house and not the floor area, so that is a bit wonky as well. Unfortunately, I didn't know that until after I input the first version of the formula which you all kindly provided. In the end, we used the formula below to provide wall area OR volume dependent on the situation: =If(D1="",(B1+C1)*2,((B1+C1)*2)*D1) Worked like a charm. Thanks again :]

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.