# calculation of area data in hectare are and sq.mt.

#### prakash vyas

##### New Member
my data is in hectare are and sq.mt. how make addition or substract it?

for example i want to make addition of following data of land

49.12.55 +12.60.23 + 25.23.62 +?

which function or formula should i use in excel?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi.

You could convert the values to square metres first, using e.g.:

=SUMPRODUCT(10^{4,2,0},0+TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",LEN(A1))),{0,1,2}*LEN(A1)+1,LEN(A1))))

I'll leave it to someone else to provide the solution for converting back to your desired format though!

Regards

Don't know which parts of your numbers are hectares and which ones are square meters, but anyhow 1 hectare is 10,000 (ten Thousand) square meters.

So if you want to add hectares to square meters, first multiply the hectare by 10,000, than add the numbers and - if so required - divide by 10,000 if you want the outcome in hectares. Hint: if you use 4 decimals, these will be the square meters, e.g. 3.7500 is 3 hectares and 75-- square meters.

So the functions to be used are addition, multiplication and optionally division

Alternatively you can use the CONVERT function with "ha" as one unit of meaure and "m2" is the other unit of measure, but for factor 10,000 or 0.0001 seems a bit overkill to me.

=TEXT(SUM(SUBSTITUTE(A1;".";"");SUBSTITUTE(A2;".";"");SUBSTITUTE(A3;".";""));"00 \ha 00 \a 00 \m2")

100 sqmt = 1 are
100 are = 1 hectare
so 10000 sqmt = 1 hectare

the example is just like Hours:minutes:Seconds data calculation the data may be in any unit that not have decimal systems like pound shilling pence

Replies
7
Views
111
Replies
10
Views
199
Replies
1
Views
119
Replies
6
Views
330
Replies
1
Views
130

1,203,694
Messages
6,056,762
Members
444,890
Latest member
war24

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back