Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

calculating from variable cells

I have a table where I am trying to calculate, among various parcels of land, how much each parcel is costing per acre. Each row is for a different parcel of land. In this table I am working with parcels under one acre and sometimes the information I am given is in acre size and sometimes in square footage. So column A is for square footage entries and Column B is for acreage size entries. Column C is for the cost of the parcel. When I enter square footage, right now, it takes that number and divides it by 43560 automatically populating column B cell with the acreage size (=8755/43560) to auto calculate .20 acres. It then takes the dollar cost and divides it by the acreage size to come up with the cost per acre - which is the end purpose. It is a little cumbersome the way it is now because when I have only the acreage size of a parcel I have to delete the formula from the cell first and manually type in the acreage size. I would like a way of doing this so that neither A nor B columns have formulas in them but if I type a square footage in A OR type in acreage in B and then enter the price for the parcel, the D column will take the price and then use whichever A or B cell has an entry and calculate the cost per acre. Of course, it will have to do an additional function where there is a square footage column A entry by converting it to acreage.

MacBook Pro 13", macOS 10.14

Posted on Mar 17, 2019 11:16 PM

Reply
Question marked as Best reply

Posted on Mar 18, 2019 1:18 AM

"It is a little cumbersome the way it is now because when I have only the acreage size of a parcel I have to delete the formula from the cell first and manually type in the acreage size. "


You are taking an additional, unnecessary step.


Put the formula in every cell of the Acreage column. (you may want to wrap it in an IF statement that will keep the cell 'empty' by inserting a null string unil the Sq Ft column has an entry)


If your available information is in sq.ft., enter the value in the sq.ft. column, and the formula will calculate the equivalent acreage value in the adjacent column.


If your information is in acres, click once on the cell in the acreage column, and enter the number of acres.

This will immediately replace the formula in that cell with the value you have entered.


In the sample table below. I have followed the instructions above for rows 2 - 5, and for row 7, using the values shown in your screen shot above.


Row 6 has not yet received an entry, so cells in columns B, C and E remain unfilled.

The formula shown below the table is entered in cell C2, then filled down to the last row in that column.


The only other formula on the table, shown below, is entered in E2 and filled down column E.

E2: IF(LEN(C2)<1,"",ROUND(D2÷C2,0))


Note that this formula contains the same 'switch' as the first one. It prevents calculation of the price per acre until column C has an entry. If column C is filled, by its formula or by direct entry, before the value is entered, the formula will correctly calculate a 0/acre price. If it were permitted to do the calculation before there wa a value in column C, the result would be a 'division by zero' error.


When using this method, it is important to maintain one unused row at the end of the table to allow filling the formulas into rows added to the table.


Regards,

Barry


4 replies
Question marked as Best reply

Mar 18, 2019 1:18 AM in response to Brian Entz

"It is a little cumbersome the way it is now because when I have only the acreage size of a parcel I have to delete the formula from the cell first and manually type in the acreage size. "


You are taking an additional, unnecessary step.


Put the formula in every cell of the Acreage column. (you may want to wrap it in an IF statement that will keep the cell 'empty' by inserting a null string unil the Sq Ft column has an entry)


If your available information is in sq.ft., enter the value in the sq.ft. column, and the formula will calculate the equivalent acreage value in the adjacent column.


If your information is in acres, click once on the cell in the acreage column, and enter the number of acres.

This will immediately replace the formula in that cell with the value you have entered.


In the sample table below. I have followed the instructions above for rows 2 - 5, and for row 7, using the values shown in your screen shot above.


Row 6 has not yet received an entry, so cells in columns B, C and E remain unfilled.

The formula shown below the table is entered in cell C2, then filled down to the last row in that column.


The only other formula on the table, shown below, is entered in E2 and filled down column E.

E2: IF(LEN(C2)<1,"",ROUND(D2÷C2,0))


Note that this formula contains the same 'switch' as the first one. It prevents calculation of the price per acre until column C has an entry. If column C is filled, by its formula or by direct entry, before the value is entered, the formula will correctly calculate a 0/acre price. If it were permitted to do the calculation before there wa a value in column C, the result would be a 'division by zero' error.


When using this method, it is important to maintain one unused row at the end of the table to allow filling the formulas into rows added to the table.


Regards,

Barry


Mar 18, 2019 2:27 AM in response to Brian Entz

if I type a square footage in A OR type in acreage in B and then enter the price for the parcel, the D column will take the price and then use whichever A or B cell has an entry
 and calculate the cost per acre.


If you want the 'Sq feet' or 'acreage' columns to be input only with no formulas then one way would be to do this:




There is one formula in the table, entered in D2 and filled down:


=C2/IF(MATCH(MAX(A2:B2),A2:B2,0)=1,A2/43560,B2)


This takes the value in C2 and divides it by A2/43560 if there is a value in A2. If there is no value in A2 it divides the value C2 by the value in B2.


The MAX looks for which column, A or B, has the larger value. The MATCH then finds which column has that larger value. That will be the first column if there is a value in A and the second column if there isn't a value in A.


SG

calculating from variable cells

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.