×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Help

Excel Help

Excel Help

(OP)
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.

RE: Excel Help

CODE

      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")

CODE

    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)

RE: Excel Help

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","")))
 

RE: Excel Help

(OP)
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
 

RE: Excel Help

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)

RE: Excel Help

(OP)
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)?

RE: Excel Help

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".

RE: Excel Help

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources