Formula to Figure Out Square Footage
Greetins everyone, hoping I can get some insight. Not sure if this is even possible.
I have a field in an excel sheet that shows the dimensions in a room. 12'5"x10'6"
Considering both the width and height are all in one field, is there anyway to get excel to calculate the above in square feet? Basically multiply all the numbers in that 1 field. I understand that if the numbers were in there own field this would be a piece of cake. Unfortunate this is how I got the doc.
Well, if you're going to get weird with the data like that, I'll go for the VBA answer so the formula is at least readable. Here's a new function called SQFT() you can add to your workbook to make this easy:
==========
Please Login or Register to view this content.
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
===========
How to use the new function.
If the text strings are in column A, put this formula in an adjacent cell as shown:
You didn't show any spaces in the sample, but adding them won't hurt anything.Please Login or Register to view this content.
Last edited by JBeaucaire; 02-09-2010 at 02:15 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JBeaucaire thank for this complex formula. Didn't think it was going to require VB. I am going to insert this into my sheet. Thanks again for the help!!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
(Note, it's been a few weeks, so maybe the Prefix box is not longer available. If not, use the Thread Tools menu to mark the this thread as [SOLVED].
Thanks...
I was bored, so I decided to see if I could do this with just a formula. Turns out, I can.Please Login or Register to view this content.
Last edited by darkyam; 03-13-2010 at 01:23 AM.
Hehe...Absolutely. I knew it could, but oh my!
...thanks for proving my point so clearly.Originally Posted by JBeaucaire
On the other hand, if that works, now the OP has two good options.
Re: Formula to Figure Out Square Footage
Wow thanks guys for both options, I appreciate the effort!
Darkyam I don't know how you figured out that formula but it works. What is even better is I can use it on my google documents!
Thanks to both of you guys for your help!!!!
JBeaucaire I am not seeing an option to edit my thread and mark it SOLVED. The option is not available in the Thread Tools either. Somewhat odd.
No, the prefix box is only available a for a certain period of time after a thread is started. If it's gone and you don't see the Mark as Solved option in your Thread Tools, then just GO ADVANCED on the original post and add [SOLVED] to the start of the title.
darkyam just trying out your formula and its not working after running a couple of different numbers.
That's a vague response. It seemed to work for me. What numbers are you trying and what are the results? Please post the workbook with them if possible.
Re: Formula to Figure Out Square Footage
Workbook is attached. I put the measurements into a1 and the formula into b1.
The UDF still does it with just:
=SQFT(A1)
Sorry. TryPlease Login or Register to view this content.
darkyam I think that did it!
JBeaucaire you mean using the formula you made (UDF). I am on google documents but when I get home I will give it a whirl.
Re: Formula to Figure Out Square Footage
As though it wasn't ugly enough already...Please Login or Register to view this content.
I would suggest you simply enter your strings as 12'0" x 12'6" so the formula and the UDF given so far don't have to get any worse than they already are.
thanks buddies you have done a great job but i wanted to know as i m new here does it work if we have three values to multiply like 10'3" x 5'2" x 2'3"
and does it will work if we do not use inch in feet like 10' x 5' x 3' ....
please advice or did i done something wrong?
The UDF and the formulas given in this thread work as is. For you to use them you would need to stick to the layout/use shown.
To leave inches out, list them as 0
10'0" x 5'0"
If you need to do VOLUME, which is what your question implies, you run the formula or UDF on each "pair" and multiply the result.
10'x5'0"= 50
5'0"x3'0" = 15
50x15= 750 cubic feet.
If you cannot adapt the solution given in this thread to your different scenario and needs, you will need to start a thread of your own (as per Forum Rules) and present your situation. You could include a link to this thread in your thread.
Personally, I think you can get what you need from this thread by just using twice as shown.
Last edited by JBeaucaire; 12-09-2012 at 07:25 PM.
This is great information. I quote alot of mirror and glass. I will generally enter the size in the cell like this: 42x66, with no inches symbol ("). I know I can enter the sizes in two different cells then in another cell multiply them & divide by 144, but I was hoping to eliminate that with a formula.
Okay, new here but I am struggling at something I am hoping someone can help me with! I am trying to create a document that we can utilize in the office for Roof Pitches. So what I need is basically a spreadsheet that I enter the roof pitch in and it calculates the square footage, we have 6 different square footage but here's the catch.... they'd like to have triangles (roof pitches) in the diagram so they just input their numbers and at the bottom it calculates the footage. I am at a loss. Is there anyone that can help me? (I am feeling defeated).
You are assuming we will know or can figure out how your company sets things up... we can't... you have to tell us. Best would be if you post a copy of a workbook (see yellow banner near the top of the forum window) with the same structure as you actual worksheet and with a few examples. Show us your manually calculated answer and tell us which value is that calculated value so we know what to give you in formula or code. Import to fully describe how your company represents "pitch"... is it an angle in degrees, an angel in radians, two numbers (rise over run in one or two cells), or some other way?
Last edited by Rick Rothstein; 06-23-2020 at 03:27 PM.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks