IF Statement

Question

Hello,

I'm trying to figure out a formula that will display the data in the E29:E40 range as square footage, not as the present combination of square footage and acres.

The one digit number (1.70) is acres while the five digit (38071) is square foorage. The square footage number could be two, three, four or five digits. One acre equal 43,560 square foot.

The formula would be for cell E29 that I can copy down range to E40.

But when displaying in square footage drop of the number below .000. Example D30 would diplay as 38071, but keeping the two digit for number that are displaying as 1.70.

Any help would be appreciated.

Answer 1

In E29:

=IF(D29<10, 43560*D29, D29)

Fill down.

To display numbers below 10 with 2 decimal places and larger numbers without decimal places, apply the custom number format

[<10]0.00;0

Answer 2

Sorry, I hadn't seen your reply.

=LET(v, --SUBSTITUTE(D43, " ac", ""), IF(v<10, 43560*v, v))

or in older versions of Excel

=IF(--SUBSTITUTE(D43, " ac", "")<10, 43560*SUBSTITUTE(D43, " ac", ""), --SUBSTITUTE(D43, " ac", ""))

Answer 3

HansV,

Your formula worked perfectle as you can see below, everything converted as illustrated in the E column.. But I ran into this component issue. The possibility of have ac as part of the definition.

The criteria is if the site size is less then 1 acres it will be listed in square footage. If the site size is over one acres it will be listed with three digit (1.70) or with sc in the wording (1.19 ac). Could you help me create a formula for ce;; E43, to still convert carry forward the number if it's less then 1 acrea in a (five, four, three) digit format, covert acres to Square Footage. plus acres that has an ac in the definition to just square footage.

cell E43 should be 51,836 Sq.Ft.

1 acres = 43560 Sq.Ft.