Solved

I would like to populate one cell (A1) with '*' if a range of 5 other cells (B1:B5) contain at least one instance of 'yes' and at least one instance of 'no'.

Posted on 2016-09-23
7
25 Views
Last Modified: 2016-10-12
Hello
I would like to populate one cell (A1) with '*' if a range of 5 other cells (B1:B5) contain at least one instance of 'yes' and at least one instance of 'no'.  As an example, if B1 = yes, B2 =yes, B3=no, B4=yes, B5=yes then A1=*
Thanks for your help
0
Comment
Question by:Dave
  • 3
  • 3
7 Comments
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
ID: 41813235
formula in A1:
=IF(AND(COUNTIF(B1:B5,"yes")>=1,COUNTIF(B1:B5,"no")>=1),"*","")

Open in new window

0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
ID: 41813386
@Crystal
I think the condition >0 is more correct as per the logic i.e. to check if any instance occurs like this. :)

=IF(AND(COUNTIF(B1:B5,"yes")>0,COUNTIF(B1:B5,"no")>0),"*","")

Open in new window


And this can be also written as
=IF(AND(COUNTIF(B1:B5,"yes"),COUNTIF(B1:B5,"no")),"*","")

Open in new window

0
 
LVL 18
ID: 41813392
namaste, Subodh - thank you
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41813397
Namaste Crystal! :)
You're welcome.
0
 

Author Comment

by:Dave
ID: 41837317
My apologies for the delay and thank you both for your quick responses
The suggested points distribution is certainly fine with me.
Dave
0
 
LVL 18
ID: 41837384
you're welcome, Dave ~ happy to help
धन्यवाद, Subodh (not sure of your dialect -- Google helped me)
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41839776
The chosen answers resolved the question.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now