Excel Help 1

[renzon]

I have a couple of questions so please bear with me,

First, can I set a value to a cell with words? Such as the word dog=1, and then have a total for dogs? Here is an example just incase...

Dog
Dog
Dog

Total dog=3

My next question is how can I make a formula that will give me the % of good and a % of bad. I thought about an if statement but I have three different conditions determining what is good and what is bad. The information is for anything less than -.630=bad, between -.5 and .5=good, and .629 and greater=bad.

I am stumped...and Google is not cooperating. Plus i feel like a am trying to over complicate things when it should really be easy. I am going to blame it on management making us come in when Fey is over land.

 

[handleman]

  	A
1	Dog
2	Rooster
3	Chicken
4	Dog
5	Pig
6	Dog
7	Cat
8	
9	
10	3

The formula in A10 is:

=COUNTIF(A1:A7,"Dog")

    A	  B
1  2      No Good
2  0.5    Good
3  -3     No Good
4  -0.25  Good
5  0      Good
6  -0.6   No Good
7  Bird   #VALUE!
8  0.129  Good
9  0.2    Good
10 0      Good
11		
12        60.00%

Formula in B1:

=IF(ABS(A1)<=0.5,"Good","No Good")

Formula in B12:

=COUNTIF(B1:B10,"Good")/10


-handleman, CSWP (The new, easy test)

 

[rnordquest]

Put this in another cell like b1

=if(a1="dog",1,"") then do a sum

Now if you just want to count the number of cells with dogs then you could use COUNTIF

=COUNTIF(A1:A5,"dog")

You can nest 7 IF statements so doing your % good and bad can easily be done.

=IF(C1<-0.63,"bad",IF(OR(C1>-0.5,C1<0.5),"good",IF(C1>0.629,"bad","")))

 

[renzon]

Ok, well the countif works perfectly and I thank you all. I knew it wasn’t that hard but I am pretty lame at Excel.

Rnordquest, I am trying to use your formula:
=IF(C1<-0.63,"bad",IF(OR(C1>-0.5,C1<0.5),"good",IF(C1>0.629,"bad","")))

Because it breaks everything down how I want it to be recognize but it is not working properly. Values below- .630=bad -.5 to .5 = good, also works fine. But the other .639 and above is not changing to bad. I have been trying to figure it out but everything I think seems to be the problem doesn’t make and difference.

Thanks for all your help

 

[handleman]

Change "OR" to "AND".

What about the numbers that are between your ranges? You have not defined the areas between ±0.63 and ±0.5


-Inf. to <0.63 = Bad
-0.63 to -0.5 = ???
>-0.5 to <0.5 = Good
0.5 to <0.629= ???
0.629 to Inf. = Bad

-handleman, CSWP (The new, easy test)

 

[renzon]

thanks for looking out! those ranges are the most important since they need to be approved! How would i tie that into the equation just an, if(c1>-.63,c1<-.5)?

 

[davidbeach]

I'm too lazy to type it out into Excel, but I'd throw in an ABS function and then nest two IF functions. If ABS()>.629 then "Bad", else if ABS()<0.5 then "Good" else "Check".

 

[yakpol]

Alternatevely you can try this fancy formula. It based on excel TRUE = 1 and FALSE = 0

=CHOOSE((ABS(A1)<0.5)+(ABS(A1)<0.63)+1,"bad", "so so","good")