Excel 2010 formula help

Hi All,
Excel 2007/2010

Need help with a formula to give me the outcome in 'Column H'
I have put in the 'Desired Outcome' to show what I want as the outcome.

Conditions...
- For every change in 'Student#' if any record 'Location' includes a "CYB" then column H should display 'Hybrid' for that specific record

- For every change in 'Student#' if any record 'Location' does not include a "CYB" then column H should display 'InPerson' for that specific record

- For every change in 'Student#' if any record 'Location' includes only "CYB" then column H should display 'OnLine' for that specific record Test-1.xlsx
LVL 14
BigBadWolf_000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jppintoCommented:
I've used this formula:

=IF(G2="CYB","OnLine",IF(SEARCH("CYB",G2)=1,"Hybrid","InPerson"))

But I don't get the same results like you have on column I. Please take a look at the attached file.
Your rules are not clear!

Let's see if I understood right:

1) if value on cell G2 is "CYB" then the returning result should be OnLine - you mentioned Hybrid on cell I2?!

if any record 'Location' includes only "CYB" then column H should display 'OnLine'

2) if value on cell G2 include "CYB" then the returning result should be Hybrid - in this case there should not be any Hybrid result on column I!

3) if value on cell G2 does not include "CYB" then the returning result should be InPerson

Are this the rules that you want?

jppinto
Test-1-1-.xlsx
0
barry houdiniCommented:
I think you can use this formula in H2 copied down to get those results

=IF(COUNTIFS(A:A,A2,G:G,"CYB")=COUNTIF(A:A,A2),"Online",IF(COUNTIFS(A:A,A2,G:G,"CYB"),"Hybrid","In Person"))

see attached

regards, barry
26922831.xlsx
0
BigBadWolf_000Author Commented:
Ok let me clarify...and I also missed another criteria in my discription...sorry :)

There are three records with the 'Student#' = 100, for the same 'ReportTerm' = 05/FR
Each of those records has a different Location.
- If any one of those Locations equals CYB, then display "Hybrid" (for all three records with the same  'ReportTerm' for that 'Student#')
- If any one of those Locations does nor equal CYB, then display "InPerson" (for all three records with the same  'ReportTerm' for that 'Student#')
- If all of those Locations equal CYB, then display "OnLine" (for all three records with the same  'ReportTerm' for that 'Student#')

Hope this helps clarify
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

barry houdiniCommented:
With that clarification I think the results would be different. Try this revised formula in H2

=IF(COUNTIFS(A:A,A2,D:D,D2,G:G,"CYB")=COUNTIFS(A:A,A2,D:D,D2),"Online",IF(COUNTIFS(A:A,A2,D:D,D2,G:G,"CYB"),"Hybrid","In Person"))

see attached

regards, barry
26922831v2.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EmenizerCommented:
A sample file might be easier
0
BigBadWolf_000Author Commented:
Thank you! barryhoudini
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.