Ask Your Question

Calc: Checking whether a range of cells are blank via a formula [closed]

asked 2012-04-01 01:06:18 +0100

Paul451 gravatar image

updated 2015-11-03 21:22:35 +0100

Alex Kemp gravatar image

I'm trying to create a formula that checks whether a range of cells above it are blank.

Ie, =IF(AND(E21>$C$16, $H$21:H22="");"Here!";"")

gives a #value error. Using F9 I found the error is $H$21:H22="". It works for a single cell, so I assume you can't check a range this way. So how do I do it?

Background: Simple physics spreadsheet. Each row increments a calculation, I want a flag to draw my attention to the spot where the calculation swings from negative to positive. Using =IF(E21>$C$16;"Here!";"") works, but then it has "Here!" on every positive row.

I would prefer to only have it appear once, on the first row it flips:

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-03 21:14:25.079893

4 Answers

Sort by » oldest newest most voted

answered 2013-03-13 10:07:13 +0100

JohnSUN gravatar image

If you are interested in attracting attention only, then why not take advantage of the conditional formatting? Small demo - Flags to draw attention.ods

edit flag offensive delete link more

answered 2013-07-10 08:24:21 +0100

p1999 gravatar image

updated 2013-07-10 08:37:07 +0100

Why don't you try countif(<range>,"")=counta(<range>)?

edit flag offensive delete link more

answered 2012-04-01 02:18:50 +0100

m.a.riosv gravatar image

Try this array formula:

=IF(($C$5<e10:e11)*isblank($h$10:h11);"here!";"")< p="">

Introduce with Ctrl+Shift+Enter, the AND() OR() don't work in array formulas.

edit flag offensive delete link more


Doesn't work with the range/array in isblank. =IF(ISBLANK(H10);"True";"False") shows True. =IF(ISBlank(H11);"True";"False") also shows true. But =IF(ISBLANK(H10:H11);"True";"False") shows False.

Paul451 gravatar imagePaul451 ( 2012-04-01 05:46:19 +0100 )edit
moggi gravatar imagemoggi ( 2012-04-01 18:10:58 +0100 )edit

I didn't understand well what Paul451 want.

I hope the next array formula works as wanted.

=IF(($C$16<E21:E41)*($C$16>=E20:E40);"here!";"") (Ctrl+Shift+Enter)

Only shows here!, when the previous cell does not satisfy the condition.

m.a.riosv gravatar imagem.a.riosv ( 2012-04-03 03:23:03 +0100 )edit

Mariosv, you can't test a range against a conditional (like >= ). You can't use a range with ISBLANK(). So H21="" returns TRUE. H20="" returns TRUE. Even (H20="")*(H21="") returns TRUE. But H20:H21="" returns #VALUE error. How do you test a range/array against another value?

Paul451 gravatar imagePaul451 ( 2012-04-09 06:03:16 +0100 )edit

I can't be the first person in spreadsheet history to want to check all the cells in an array/range against a single value? There must be some way to do it.

Paul451 gravatar imagePaul451 ( 2012-04-09 06:13:25 +0100 )edit

answered 2013-03-13 04:57:10 +0100

qubit gravatar image

Hi @Paul451,

Still looking for an answer here? Have you found an answer already?

edit flag offensive delete link more

Question Tools


Asked: 2012-04-01 01:06:18 +0100

Seen: 16,218 times

Last updated: Jul 10 '13