Excel - How to do Area and Volume in the same cell?

Forums › Microsoft Windows › Excel - How to do Area and Volume in the same cell?

jerseydevil1387

2 posts

Wannabe Geek

#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

1395 posts

Uber Geek

Trusted

Subscriber

#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

1395 posts

Uber Geek

Trusted

Subscriber

#578677 8-Feb-2012 13:11

sorry remove the second parentheses like this:

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

nzkc

1572 posts

Uber Geek

#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

6434 posts

Uber Geek

#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

#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 :]