Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

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


2 posts

Wannabe Geek
Topic # 96970 8-Feb-2012 13:04 Send private message

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!

Create new topic
676 posts

Ultimate Geek
  Reply # 578675 8-Feb-2012 13:10 Send private message

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

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

Jon

676 posts

Ultimate Geek
  Reply # 578677 8-Feb-2012 13:11 Send private message

sorry remove the second parentheses like this:

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

215 posts

Master Geek
  Reply # 578681 8-Feb-2012 13:16 Send private message

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

3630 posts

Uber Geek
  Reply # 578689 8-Feb-2012 13:32 Send private message

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)



2 posts

Wannabe Geek
  Reply # 578697 8-Feb-2012 14:00 Send private message

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

Create new topic
Twitter »
Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when new jobs are posted to our jobs board:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:




News »
Geeksphere TV: Episode 19.1
Posted 25-May-2013 18:19

Amazon brings Kindle Fire to 170 countries, expand app store to more than 200 countries
Posted 24-May-2013 14:41

Gen-i and SAP partner in mobile business
Posted 23-May-2013 09:23

Ericsson to close down telecom cable manufacturing
Posted 23-May-2013 08:46

FaceToFace Communications and StarLeaf bring unique cloud-based videoconferencing to Australia and New Zealand
Posted 22-May-2013 09:12

IBM Watson finds a new job: customer services
Posted 22-May-2013 08:37


Trending now »
Hot discussions in our forums right now:

Fecked up religious people strike again :-(
Created by Mark, last reply by freitasm on 25-May-2013 08:44 (85 replies)
Pages... 4 5 6

Cannabis is illegal yet we have really strong 'legal highs' ?
Created by qwerty7, last reply by freitasm on 23-May-2013 23:20 (74 replies)
Pages... 3 4 5

A new project coming to Geekzone
Created by freitasm, last reply by l43a2 on 24-May-2013 23:02 (342 replies)
Pages... 21 22 23

HTC One (2013) owners' discussion
Created by Dingbatt, last reply by Kalaren on 26-May-2013 15:35 (1575 replies)
Pages... 103 104 105

Xbox One
Created by DjShadow, last reply by TwoSeven on 26-May-2013 15:36 (79 replies)
Pages... 4 5 6

Monolithic Cement Sheet cladding mid 80s house - "leaky home" or not?
Created by joker97, last reply by mattwnz on 24-May-2013 23:46 (15 replies)

Orcon, Is this for real or a scam??
Created by old3eyes, last reply by DarthKermit on 22-May-2013 19:12 (29 replies)
Pages... 2

Entire house HTPC concept
Created by InfiniteLoop, last reply by darthmeow on 24-May-2013 12:19 (26 replies)
Pages... 2


Geekzone Jobs »
Most recent NZ jobs in technology:

Systems Consultant Project Manager
Posted 26-May-2013 14:28

Reporting & Payroll Manager
Posted 26-May-2013 13:28

Developer of interactive experiences
Posted 25-May-2013 21:28

Ambitious Project Coordinator
Posted 25-May-2013 19:28

Ambitious Project Coordinator
Posted 25-May-2013 19:28

Exceptional Senior Project Manager
Posted 25-May-2013 19:28

Multitalented Business Analyst
Posted 25-May-2013 18:28


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

Updates »
Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.