Returning 1 or (blank) from separate columns

[nogstai]

Hi, looking to get help for this issue I have that I can't figure out. I'm a newbie on Excel formulas

So I have columns that need to yield 1 or blank based on separate columns that are in-between texts.

The requirements are;

IF Column's A, C, E, G, L, N (number columns) has one entry that is less than 7, LR = 1

IF Column A, C, E, G (Number columns) has ALL 10's, HR = 1

• IF data is available in Column L and N and are ALL 10's, HR = 1
• IF data is available in Column L and N and has <7, HR = "" and LR = 1

Added Note:
Column I and J needs to be excluded from the formula.
They are not part of the computation and have numerical values so formula or range needs to "jump" them.

Column P and Q are the desired results

So right now, I was testing these:

On Column R and S I am using these formulas to get the results (to test it out):

Low Return (Column R) =IF(COUNTIF(A2:H2, "<7")=1,"1","")
High Return (Column S) =IF(COUNTIF(A2:H2, "<10")=0,"1","")

What I can't figure out on this is on Row 10 and 14, there are less than 7 values, but it's blank in Low Return column R (bordered).

Now I'm testing to add the other columns after Column I, J, K.

Major issue I'm encountering is when I'm trying to include column's L and N, it's saying "Too many arguments"

I'm testing formulas one by one to get the results, but now I'm stuck in getting in between those pesky columns! I'm not sure if I have the correct logic, or I'm thinking about it the right way.

I hope I was able to explain that well. I'm kinda new to Excel, and looking anywhere I can for guide. Thank you!

 

[Jamie McMillan]

Hello,

Here are two simpler formulae that will work:

Column P - =IF(A1<7,1,IF(C1<7,1,IF(E1<7,1,IF(G1<7,1,IF(L1<7,1,IF(N1<7,1,""))))))

Column Q - =IF(L1=10,1,IF(N1=10,1,""))

Jamie

 

[nogstai]

Hello,

Here are two simpler formulae that will work:

Column P - =IF(A1<7,1,IF(C1<7,1,IF(E1<7,1,IF(G1<7,1,IF(L1<7,1,IF(N1<7,1,""))))))

Column Q - =IF(L1=10,1,IF(N1=10,1,""))

Jamie

Hi Jamie,

Thanks for the reply! Actually, it spans for more columns. This is just a sample I created.

My apologies, I should have clarified it in the post.

 

[Jamie McMillan]

Hello,

Actually the second formula is wrong: I just read the post again. It should be:-

Column Q - =IF(L1+N1=20,1,"")



How many columns are there altogether? As the formulae could simply be added to - maybe.

Jamie

 

[Jamie McMillan]

Hello,

From your formulae
column P - =IF(COUNTIF(A1:H1,"<7")>0,1,IF(COUNTIF(K1:O1,"<7")>0,1,""))

column Q - =IF(COUNTIF(A1:H1,"<10")=0,"1",IF(COUNTIF(K1:O1,"<10")=0,"1",""))

I think that is what you want to do.

Jamie

 

[nogstai]

Hello,

From your formulae
column P - =IF(COUNTIF(A1:H1,"<7")>0,1,IF(COUNTIF(K1:O1,"<7")>0,1,""))

column Q - =IF(COUNTIF(A1:H1,"<10")=0,"1",IF(COUNTIF(K1:O1,"<10")=0,"1",""))

I think that is what you want to do.

Jamie

I believe it's a more AND IF K:O is available. if not, it will only score the A:H.

This is mostly survey data, let's say A:H are mandatory questions, then K:O are optional questions (depending on designation).
High Return will give a 1 if A:H are all 10's, and if K:O were answered and all 10's, HR will be 1.

Same with Low Return, but it is easier, if there are any <7 between A:H and K:O

Thank you for replying on this! I really appreciate the assistance, I am testing a few of the formulas you did

Also to answer, I probably have 60ish columns I have to sift through, and all the columns I need are in between each other.

 

[nogstai]

Hello,

From your formulae
column P - =IF(COUNTIF(A1:H1,"<7")>0,1,IF(COUNTIF(K1:O1,"<7")>0,1,""))

column Q - =IF(COUNTIF(A1:H1,"<10")=0,"1",IF(COUNTIF(K1:O1,"<10")=0,"1",""))

I think that is what you want to do.

Jamie

I tried this out, but it's also detecting the blank optional columns:

So the highlighted one only has Q1 - Q4 responses, but because the false statement on the first logical test is false (9, 10, 9, 9), it went to the next IF statement which is because it's blanks and it doesn't have less than 10, it's giving an HR = 1.

For HR, if Q1 - Q4 was answered and all 10's, HR is 1
if Q1 - Q4 was answered and there was a <10 response, and Q7 - Q8 was not answered, it should be blank.
if Q1 - Q4 was answered and all 10's, and Q7 - Q8 was answered but it has a <10, it should be blank

I tried using AND to bind together the two separate ranges, but it looks like I am wrong in that formula

=IF(COUNTIF(AND(A2:H2,L2:O2),"<7")>=1,"1","") <- wrong but it's the idea behind it
Getting two separate ranges in a formula.

Thanks!