Correlations

[Pezgordo]

I have 8 correlations in a countifs formula. If all 8 correlations are present than the formula gives me the number of O's or U's (whichever letter is selected at the end of the formula). Here is a sample of the formula:

=COUNTIFS($Q$7:$Q$2870,"<18.26",$R$7:$R$2870,"<214.46",$T$7:$T$2870,"<-6.5",$U$7:$U$2870,"<1.41",$AM$7:$AM$2870,"<19.14",$AQ$7:$AQ$2870,"<365.52",$AW$7:$AW$2870,"<5.39",$BC$7:$BC$2870,"<24.82",$JD$7:$JD$2870,"u")

Is it possible to have the formula give me the number of O's or U's if only 5 or 6 of the 8 correlations are present?

 

[Marcelo Branco]

To get the number of U's if only 5 correlations are present, maybe this Array-formula

=SUM(IF(((($Q$7:$Q$2870<18.26)+($R$7:$R$2870<214.46)+($T$7:$T$2870<-6.5)+($U$7:$U$2870<1.41)+($AM$7:$AM$2870<19.14)+($AQ$7:$AQ$2870<365.52)+($AW$7:$AW$2870<5.39)+($BC$7:$BC$2870<24.82))*($JD$7:$JD$2870="u"))=5,1))

Ctrl+Shift+Enter

HTH

M.

 

[Marcelo Branco]

or this regular formula

=SUMPRODUCT(--(((($Q$7:$Q$2870<18.26)+($R$7:$R$2870<214.46)+($T$7:$T$2870<-6.5)+($U$7:$U$2870<1.41)+($AM$7:$AM$2870<19.14)+($AQ$7:$AQ$2870<365.52)+($AW$7:$AW$2870<5.39)+($BC$7:$BC$2870<24.82))*($JD$7:$JD$2870="u"))=5))

M.

 

[Pezgordo]

Both formulas worked. Obrigado.

However, I do have a question. When I run my original formula I get U=302 & O=184 (out of a total of 2863 rows). When I run this modified formula I get U=158 & O=152. Since the new formula(s) are asking for any 5 of the 8 correlations (and not all 8), shouldn't there be more matches than the original? In other words shouldn't the number of U's and O's increase instead of decrease?

 

[Marcelo Branco]

Hi,

The formulas should produce the same result.

I made a test with a very small data-sample (10 rows and 5 columns) and the two formulas worked ok and produced the same result.

I also checked if there is a typo, or something like that, in the formulas i posted, but couldn't find anything...

I dont know why you are getting different results

M.

-EDIT-
I misunderstood your post

Is possible to have less matches with only 5, than with 8 correlations.

If you want 5 or more correlations this is a different question. You have to change = 5 to >=5

M.

 

[Pezgordo]

Marcelo, the 2nd formula worked great w/ the >=5. It produced U=740 & O=563. Thank you again for the help.

Tim

 

[Marcelo Branco]

Marcelo, the 2nd formula worked great w/ the >=5. It produced U=740 & O=563. Thank you again for the help.

Tim

Tim,

You are welcome and tks for the feedback

M.