value error

[reggieneo]

Hi all,
please assist with this:

=IF(A1:A10="",B13*E1,B13*E2) i get a value of 375

=IF(A2:A12="",B13*E1,B13*E2) i get a value error


the is no space and text into any of the cell.

Thanks
reggieneo

[FlameRetired]

reggieneo,
Those are array formulas.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

What is in B13, E1:E2?

If you click in the formula bar and hit the F9 function key you will see an array of the form {0;0;0;0;0;0;0;0;0;0}. Your answers will be in the place of the 0s.

Did this help?

[reggieneo]

Thanks Dave. It is working well.

[joeu2004]

=IF(A1:A10="",B13*E1,B13*E2) i get a value of 375
=IF(A2:A12="",B13*E1,B13*E2) i get a value error

It is unclear what your intent is. But you probably do not want to simply array-enter the formula as-is (press ctrl+shift+Enter instead of just Enter).

As written, if you simply press Enter, the formula is interpreted as IF(An="",...), where n is the row number where the formula is entered. If you enter the formula in row 1, the first formula will work by coincidence, but the second formula returns #VALUE because the row number is not 2 through 12.

Although the #VALUE error is avoided if you array-enter the formula, the interpretation and intent is unclear. Arguably, one of the following might reflect your intention; but I doubt it.

{ =IF(AND(A2:A12=""),B13*E1,B13*E2) }

or
{ =IF(OR(A2:A12=""),B13*E1,B13*E2) }


The curly braces in red indicate that we pressed ctrl+shift+Enter instead of just Enter. We cannot type the curly braces directly.

The first formula returns B13*E1 if all of A2:A12 are empty or the null string.

The second formula returns B13*E1 if at least one of A2:A12 is empty or the null string.

[FlameRetired]

@ joeu2004

Quite correct. My bad. It doesn't have to be array entered. In fact I could not duplicate the error either way after posting.