% YES is hit formula

[John Caines]

Hello All.
I really would love to create a formula that simply tells me how many times a target is hit.
I have a column, (Column N), which can display the words "EVEN" or "YES" or "NO"

The formula runs from N24:N263. These are added usually daily, so at the moment I only have 30 entries out of the possible 240
As a note, the formula in cell N24 for example is;

=IF(LEN($M$24)=0,"",IF($M$24=$K$24,"EVEN",IF($M$24>$K$24,"YES","NO")))

What I'm looking to do is create a formula for cell P21,,, to display a % relating to column N
If the word displays YES or EVEN in a cell in column N,, this is a positive, only negative is the word NO.

Example.
If there were 10 entries in column N,
N24 shows "NO"
N25 shows "YES"
N26 shows "YES"
N27 shows "YES"
N28 shows "NO"
N29 shows "EVEN"
N30 shows "NO"
N31 shows "EVEN"
N32 shows "YES"
N33 shows "YES"

So P21 cell would like to now display 70% (As there were 7 positive events)

Trouble is, I don't know how to write this
I've looked at a few formulas on my other spreadsheets, but I just can't work it out,, maybe it's a COUNTIF formula?,,, I'm not sure

If someone can help me with this I would be very grateful.

Many thanks for all your time.
A very confused.com,,,
John Caines

 

[Michael M]

Try

=COUNTIF(N24:N33,"<>NO")/10)

Change the ranges to suit and format the cell as %

 

[jasonb75]

This will allow for the varied number of entries,

=SUM(COUNTIF(N24:N263,{"YES","EVEN"}))/SUM(COUNTIF(N24:N263,{"YES","NO","EVEN"}))

Format cell as %

 

[John Caines]

Thanks for your replies!!!
I was just trying Michael's formula,, and was writing a reply,, when I also saw your reply Jason,, many thanks.

Yes,, the problem was,, in adding entries etc,,,
Jason,, your formula seems to solve this !!
As a note,,, I noticed the use of {},,,,,,
Does this mean the formula is an array??

Just wanted to clarify.
Many thanks again,,,
I shall use this for 2 other cells also,, great stuff.

Many thanks
A very grateful.
John Caines

 

[Aladin Akyurek]

How do you want formula blanks that you have in column N treated?

1)

=SUM(COUNTIF(N24:N263,{"EVEN","YES"}))/ROWS(N24:N263)

treats formula blanks as no hit.

2)

=SUM(COUNTIF(N24:N263,{"EVEN","YES"}))/SUM(COUNTIF(N24:N263,{"EVEN","YES","NO"}))

disregards the formula blanks.

 

[John Caines]

As a note;
Formula now is;

=IF(LEN($M$24)=0,"",SUM(COUNTIF(N24:N263,{"YES","EVEN"}))/SUM(COUNTIF(N24:N263,{"YES","NO","EVEN"})))

Many thanks Jason!!! Working perfect,,,, great stuff!!!

Just seen your reply also Aladin Akyurek,,, many thanks also,,, great minds think alike!!! :_)
one of your replies is the same as Jasons!

The formula Jason (And now you) have supplied is the one,, works perfectly.
Many thanks for taking the time to answer,,, it is a very useful formula.

Thanks again
A very grateful
John Caines

 

[Aladin Akyurek]

As a note;
Formula now is;

=IF(LEN($M$24)=0,"",SUM(COUNTIF(N24:N263,{"YES","EVEN"}))/SUM(COUNTIF(N24:N263,{"YES","NO","EVEN"})))

Many thanks Jason!!! Working perfect,,,, great stuff!!!

Just seen your reply also Aladin Akyurek,,, many thanks also,,, great minds think alike!!! :_)
one of your replies is the same as Jasons!

The formula Jason (And now you) have supplied is the one,, works perfectly.
Many thanks for taking the time to answer,,, it is a very useful formula.

Thanks again
A very grateful
John Caines

Doesn't just...

$M$24=""

suffice instead of

LEN($M$24)=0 ?

 

[John Caines]

Hi Aladin,,,,
you said about;
LEN($M$24)=0,,,,,,,,

I'm not an excel formula/coder/vba guy to be honest,,, just kind of scape through.

I remember requesting some help a while ago,, and someone kindly replied and mentioned this LEN part in a formula.
I'd never heard of it,, but I used it as in 1 sheet I had several different ways to do it,, IE COUNTBLANK or COUNTIF=5,,, or things like this,,,,, but I managed to replace them all with the LEN formula.

So,,, I'll carry on using it,,,,but,, do you think this is overkill?
I mean,,, is it asking excel to do a lot more in the formula?
So,, is $M$24="" just a more efficient way to do it then I suppose?

If it's ok (Seems to work ok),, I'll stick with the LEN,, as this is throughout my sheet in virtually all cells with formulas,,, just continuity really.

Thanks for pointing this out though.
I can't remember what it exactly does differently than most,,, but it works ok.

Many Thanks
John Caines

 

[jasonb75]

There are a few things I can't make sense of John, for instance,

=IF(LEN($M$24)=0,"",IF($M$24=$K$24,"EVEN",IF($M$24>$K$24,"YES","NO")))

I would guess that in N25 this formula would be changed to

=IF(LEN($M$25)=0,"",IF($M$25=$K$25,"EVEN",IF($M$25>$K$25,"YES","NO")))

So why use absolute references and edit the formula each time when you could use relative reference and copy down?

An equivilant with a little less overkill would be

=IF(M24="","",LOOKUP(SIGN(M24-K24),{-1,"NO";0,"EVEN";1,"YES"}))

LEN($M$24)=0 LEN() counts the number of characters in the cell, so this is checking for a blank / empty cell, the same as Aladin's suggestion of $M$24=""

 

[T. Valko]

Hi Aladin,,,,
you said about;
LEN($M$24)=0,,,,,,,,

So,,, I'll carry on using it,,,,but,, do you think this is overkill?
I mean,,, is it asking excel to do a lot more in the formula?
So,, is $M$24="" just a more efficient way to do it then I suppose?

Consider this...

A1 is an empty cell

=A1="" = TRUE
=LEN(A1)=0 = TRUE

A1 contains a formula that returns a blank ""

=A1="" = TRUE
=LEN(A1)=0 = TRUE

So, is it overkill? I think so.