If statement help

[bknight]

I have an if statement with a either not is null or is null within.

Code: Select all

IF(E2319 is null, null,VALUE(LEFT(E2319,1))))

What is the correct syntax?

[HansV]

Null is a VBA concept. In Excel, use "" to check if a cell is blank:

=IF(E2319="", "", VALUE(LEFT(E2319)))

(You can omit ,1 from the LEFT function; Excel uses 1 as default)

[bknight]

Null is a VBA concept. In Excel, use "" to check if a cell is blank:

=IF(E2319="", "", VALUE(LEFT(E2319)))

(You can omit ,1 from the LEFT function; Excel uses 1 as default)

,1 omit??, where is it in the formula?

It was supposed to be LEFT(E2319,3) not what I published.
=IF(E2319="","",VALUE(LEFT(E2319,3)))

But this is wrong, E2319 is blank but the above formula returns 124?

[bknight]

Here is image

[HansV]

I omitted ,1 from YOUR version of the formula. But since you need ,3 instead of ,1 it is not relevant.

Are you sure that the formula returning 124 is in F2319?

[bknight]

=IF(E2319="","",VALUE(LEFT(E2319,3)))
is in F2319
and you can see E2319 is blank.

[HansV]

There is something strange here. Could you attach a copy of the workbook with most stuff removed except those cells?

[bknight]

Full spreadsheet is 520K, so.
Some of the formula are not valid,but the BP are.

[HansV]

The formula in what is now F11 must have referred to another cell than what is now E11, for it is

=IF(#REF!="","",VALUE(LEFT(#REF!,3)))

If I change this to

=IF(E11="","",VALUE(LEFT(E11,3)))

the formula returns a blank. I changed the formula in G11 accordingly, and filled up and down.

Book1.xlsx

[bknight]

You have mail.

[bknight]

The formulas in column F & G were not "automatically" being entered as new data was entered, so I put those formulas in this morning.

[HansV]

The formulas are in row 2913 but they refer to row 2319.
Do you see the mistake?

[bknight]

I did make a mistake, thanks.