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 ><

Thank you!

## jonherries

1037 posts

Uber Geek

Subscriber

=if(D1=""),C1*B1*E1,D1*C1*B1*E1)

Assuming you start at row one and don't have a header.

Jon

## jonherries

1037 posts

Uber Geek

Subscriber

sorry remove the second parentheses like this:

=if(D1="",C1*B1*E1,D1*C1*B1*E1)

## nzkc

490 posts

Ultimate Geek

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

6434 posts

Uber Geek

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

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.

Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.