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

923 posts

Ultimate Geek

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

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

Jon

## jonherries

923 posts

Ultimate Geek

sorry remove the second parentheses like this:

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

## nzkc

347 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

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

News »

Security concerns reach new peak, Unisys Security Index
Posted 27-Jun-2017 14:11

Behind Spark’s slow-burn 4.5G plan
Posted 26-Jun-2017 16:23

Red Hat unveils production-ready open source hyperconverged infrastructure
Posted 23-Jun-2017 22:10

Whatever ailed Vodafone broadband … seems to be fixed
Posted 23-Jun-2017 14:10

VMware NSX Meets Stringent Government Security Standards with Common Criteria Certification
Posted 22-Jun-2017 19:05

Brother launches next-generation colour laser printers and all-in- ones for business
Posted 22-Jun-2017 18:56

Intel and IOC announce partnership
Posted 22-Jun-2017 18:50

Samsung Galaxy Tab S3: Best Android tablet
Posted 21-Jun-2017 12:05

Wellington-based company helping secure Microsoft browsers
Posted 20-Jun-2017 20:51

Endace delivers high performance with new 1/10/40 Gbps packet capture card
Posted 20-Jun-2017 20:50

You can now integrate SMX security into Microsoft Office 365, Google and other cloud email platforms
Posted 20-Jun-2017 20:47

Ravensdown launches new decision-making tool HawkEye
Posted 19-Jun-2017 15:38

Spark planning to take on direct management of all consumer stores
Posted 19-Jun-2017 10:03

Qrious acquires Ubiquity
Posted 14-Jun-2017 12:21

Spark New Zealand prepares for 5G with Nokia
Posted 14-Jun-2017 12:16

Geekzone Live »

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