Excel spreadsheet Widths across columns in centimetres

Lengths down rows in centimetres

Price per metre in separate cellWant to fill in sheet with price per sq metre for the dimensions

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

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)*C2MIKE

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

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

message edited by mmcconaghy

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

playing around with it and AbsolutesWhat 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

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

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

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

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

Want to fill in sheet with price per sq metre for the dimensionsSo 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

I might need to if they add additional sizes - any tips? Ann

message edited by annj

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History