How to work out price per square metre - in Excel

August 4, 2014 at 06:22:43
Specs: Windows 7
Excel spreadsheet

Widths across columns in centimetres
Lengths down rows in centimetres
Price per metre in separate cell

Want to fill in sheet with price per sq metre for the dimensions

I know it will be easy when I know how !?


See More: How to work out price per square metre - in Excel

Report •


#1
August 4, 2014 at 07:17:51
Is there a reason you want a Table as
opposed to using four cells for ( L x W = Area ) x Price
which would be like:

     A        B      C          D
1) Length  Width   Price      Total
2)   2       4     $1.13    =(A2*B2)*C2

MIKE

http://www.skeptic.com/


Report •

#2
August 4, 2014 at 07:39:55
Hi Mike

That's the specific format I've been asked to do it in - its for flooring so different widths and lengths

Thanks, Ann


Report •

#3
August 4, 2014 at 08:04:22
Not to be nosy, but is this a Homework Assignment?

Because using a Table may not be the best option for you.

First, how big do you want the table,
Excel 2010 has 1,048,576 rows by 16,384 columns,
but I doubt you want a table that large. :-)

Second, your formula will probably be the same as I posted,
because with a Table you will need to Input both the Length & Width
to do a Lookup into the table to get your Area.
But why do a Lookup, if you already have your L & W?

Now all you need is Price and your done.

It may look pretty, but is it really needed?
See what I mean?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
August 4, 2014 at 08:36:56
It's for a website form, fill in Width and Length and it comes back with a price - I've just been playing around with it and Absolutes - come up with the following which works but not sure if strictly correct. I've got the price per metre on another sheet

=(B$1/100)*($A2/100)*Sheet1!$A$1

The form is in cms so had to get back to metres.

Ann


Report •

#5
August 4, 2014 at 17:51:25
playing around with it and Absolutes

What do you mean by Absolutes?

It's the input in CM that may give you grief
I can envision some one doing 1.20 or some such
to represent One meter, 20 cm
and not being conversant with Metric Measurements
not sure if your formula will account for something like that.

MIKE

http://www.skeptic.com/


Report •

#6
August 4, 2014 at 18:18:40
Also, so you know, Excel has a CONVERT function,
so something like:

=CONVERT((A2*B1),"cm","m") * Sheet1!$A$1

Will first multiply A2 * B1
then Convert the answer from CM to Meters.

MIKE

http://www.skeptic.com/


Report •

#7
August 5, 2014 at 07:28:14
Hi Mike

I put in absolutes so the formula can be copied with changing either column/row whichever appropriate.

Luckily the website is coded to only accept whole numbers.

Thank you very much for tip on converting.

Ann


Report •

#8
August 5, 2014 at 07:55:46
I put in absolutes so the formula can be copied with changing either column/row whichever appropriate.

Is that because you are using a Table
or are you allowing multiple entries?
Just curious.

MIKE

http://www.skeptic.com/


Report •

#9
August 6, 2014 at 03:50:18
Sorry Mike not sure I quite understand the question.

Have approx 10 widths going across top and 20 lengths going down which is why using table and using absolutes in formula.

Hope that makes sense !

Ann


Report •

#10
August 6, 2014 at 07:05:49
Want to fill in sheet with price per sq metre for the dimensions

So I take it that you no longer need assistance with your original problem,
since you have a table of 10 x 20 or do you wish to extend that size?

MIKE

http://www.skeptic.com/


Report •

#11
August 7, 2014 at 00:18:10
I might need to if they add additional sizes - any tips?

Ann

message edited by annj


Report •

#12
August 7, 2014 at 06:40:59
Using the Fill Handle should make life easier:

http://spreadsheets.about.com/od/ex...

MIKE

http://www.skeptic.com/


Report •


Ask Question