I am looking at a set of data and I want excel to put a 1 if cell U2 says "MD" and if AD2 has a specific 3 number score but if both of those requirements aren't met then return a 0.
Column U shows Medical License type and Column AD shows a credit number called PayNet and I want to see what the hit rate of PayNet scores for each Medical License type. I would change the name of the license type for the different types because I would have 3 different columns with each license type that we have on our portfolio. Please let me know if this made any sense it is the end of the work day and my brain is fried so putting together a cogent sentence is a stretch for me at the moment.
Hi Matt,
You can use a nested IF with OR e.g.
=IF(OR(U2="MD",AD2=123),1,0)
If that's not what you're after, then please upload a sample file as it's difficult to picture all that information.
Mynda
Attached is a picture of as much of the data as I can show that I am using to analyze. Column U shows the medical license type for each customer. Column AD shows their respective credit scores at origination and AW through AY is an IF statement that I made to separate out the number of MD's, Non-MD Medical's and Commercial's. I did make 3 other columns in AZ through BB that had an IF statement reading =IF(AW2=1,IF($AK2=1,1,0),0) which got the results that I wanted the only issue is I'd rather not have this be a never ending table simply because I would have to make a column for FICO hit rate and then make another 3 columns that would have that same IF statement (=IF(AW2=1,IF($AK2=1,1,0),0)) except checking for FICO hits for the medical licenses. The same would go for Business Type and TIB (Time in Business). My thought is having a formula that can do a hit rate check without having to create a million columns.
Hi Matt,
You should be able to use COUNTIF on column U to count the different license types? Or COUNTIFS for multiple criteria which would enable you to include the score range etc.? These are covered in lesson 3.04.
Perhaps I'm missing the point. If so, please upload an Excel example file. I cannot tell much from the screenshot. You just need to include a small sample of the data relevant to the question/formula and you can anoymise it if required.
Mynda
