confusing question -- counting 1s in one column, and multiple IFs from another

[Jennifre]

Hi, I have a confusing [to me] problem -- I need to use multiple IFs in a creative way, I think.

I need to find the 1s from column J, then get the percentage of those 1s "scoring" a 1, 2, or 3 in AN, by using something like this:

=IF(AN17<1.8,1,IF(AN17<2.7,2,IF(AN17>2.7,3)))

I don't see how I can use that here though, given I need to get the percentage of 1s, 2s, and 3s given these "judging" criteria (above).

If I can get how to do this for even one of these things (how to get AN's 1s)

Sample Data:
J
1
1
2
1
2
2

AN
1.25
3.12
2.35
1.37
1.00
2.10

Ideal result (down 3 rows (just need the percentages)):
24% of 1s in J = 1 (or <1.8)
72% of 1s in J = 1 (or <2.7)
4% of 1s in J = 1 (or >2.7)

 

[repairman615]

Hello,

Using 2007 or later.

I may be misunderstanding, to clarify, you would like to count the 1's in col. J that also have a score greater than (or equal to???) 1.8, and then divide be the count of numbers in column J.

If so then maybe like:

=COUNTIFS(J1:J10,"=1",AN1:AN10,">1.8")/COUNT(J1:J10)

This example is for 10 rows.

-Jeff

 

[barry houdini]

I think the denominator needs to be the count of 1s in column J, i.e. for the first

=COUNTIFS(J$2:J$100,1,AN$2:AN$100,"<1.8")/COUNTIF(J$2:J$100,1)

and for the second....

=COUNTIFS(J$2:J$100,1,AN$2:AN$100,"<2.7",AN$2:AN$100,">=1.8")/COUNTIF(J$2:J$100,1)

 

[Jennifre]

Awesome, thank you so much for helping me to clarify what I will need here!!

I used your formula basically, converting it a smidge to:

=COUNTIFS(L$1:L$1364,"=1",$AN$1:$AN$1364,"<1.8")/COUNT(L$1:L$1364,"=1")

However, for the middle one (to get 2s), I'll need the "<1.8" to indicate, really, <1.8 AND <2.7.

*Is there a way I can use more than "<0.0" to get a middle number. Something more like "<0.0>0.0" to use both a > and a < ??

I used this to get this for other data, and now I'm getting a smidge paranoid that it didn't work, but I guess that's another post! (embarrassed!)

=IF(AN17<1.8,1,IF(AN17<2.7,2,IF(AN17>2.7,3)))

 

[barry houdini]

Try my second formula to get the % of 1s in column J that have >= 1.8 and < 2.7 in column AN

 

[Jennifre]

Oh my goodness, that looks like it worked!!

I'm checking, but that looks like it may have done the trick -- thank you SO much!