• 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.

Need a third option...

Eloise T

Eloise T

Active Member
The formula below evaluates the row in consecutive cells in Columns P through S as compared with N. If the AND function returns TRUE, put True, if FALSE put False, and if there is no data in the cells I want Column T (where the formula is) to put two dashes. (indicating no data to be evaluated). [Note: cells P through S with either all have data or no data. There will not be a time when less than all cells (P through S) will have data or less than all cells (P through S) will be without data.]

In other words, if the same information is in all cells as compared with Nₓ, i.e. Pₓ, Qₓ, Rₓ, and Sₓ, put True, if not put False, otherwise if there's no data in Pₓ, Qₓ, Rₓ, and Sₓ then cell T should put "--".

The formula below works except for the "3rd option."
=IF(AND(N6=P6,N6=Q6,N6=R6,N6=S6),"True","False")
 
Last edited:
you could try
=IF(ISBLANK(P6),"--",IF(AND(N6=P6,N6=Q6,N6=R6,N6=S6),"True","False"))
which only tests P6 for being blank relying on your "There will not be a time when less than all will have data or less than all will be without data"
 
=IF(ISBLANK(P7),"--",IF(AND(N7=P7,N7=Q7,N7=R7,N7=S7),"True","False"))

...almost worked. My fault: P through S have "--" (two dashes) in them when they have no numerical data so T Column still shows False. Any further suggestions?

WITHOUT DATA
upload_2017-3-22_21-52-53.png

WITH DATA
upload_2017-3-22_21-50-47.png
 
Last edited:
Expanding a bit on @Khalid NGO's approach:

=CHOOSE((COUNTIF(P6:S6,N6)>=COLUMNS(P6:S6))+(COUNT(P6:S6)=COLUMNS(P6:S6))+1,"--",FALSE,TRUE)

Hope that helps.

Regards,
Ken
 
You must log in or register to reply here.
Back
Top