• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

ISBLANK Where Value is a Range

V

Vpremakumar

New Member
How to write a formula if range of cells are blank (I mean empty, no values)then it should display "Y" and if not display "N"
 
Hi Prem,


Suppose your data ranges from A1 to A10 as follows(A2,A6,A8 are blank cells):


A


B

C

D


F


T

M


At B1 write the below formula:


=IF(A1="","y","n") press enter and drag it down.


Hopoe this helps.


Kaushik
 
I want formula in different format, formula should look range of cells, if the all range cells are blank then it should display " Y " and if not display " N "
 
@Vpremakumar


Hi


Try This Formula


=IF(A1:A6="","Y","N")


Hope it will solve your problem other wise please give some more details


Thanks


SP
 
@ Prem


Hi


i try that formula if there is a value from A1 to A6 then it will display in b1 as N if there is no value between the given range say A1 to A6 and A3 has no value then it will Display in B1 as Y


so actually your concept is not clear can you give some more details


Thanks


SP
 
Try this:


=IF(A1:A10="","Y","N") enter as an array formula by pressing CTRL+SHIFT+ENTER from your keyboard.


Note: Formula will return "Y"if all the cells in the range are blank, if any one of the cellls conatain a data it will return "N"


So in case of my above data example, this formula will return N; but if you remove all the data from the range, formula will return Y


Let us know if this is fine.


Regards,

Kaushik
 
@ Kaushik


Hi


Yes with the Array formula it will give the result when the given range has no data but in normal Enter it will give Y when there is no value any one of the given range


Thanks


SP
 
@Kaushik,

Pls check your formula shows N only if A1 has data, if you keep A1 blank and you write some numbers in A2, A3, ...it shows Y..this is wrong i think
 
Hi Prem..


Can you please try the below for me.. :)


Code: 
=IF(COUNTA(A1:A10)=0,"Y","N")


Regards,

Deb
 
@Prem...


You are correct..


Can you try this?


=IF(AND(A1="",A1:A10=""),"Y","N") same with CTRL+SHIFT+ENTER


I think Deb's one is more simple, easy to understand and should be working fine.


Kaushik
 
You must log in or register to reply here.
Back
Top