+ Reply to Thread
Results 1 to 11 of 11

IF Formula Question

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    IF Formula Question

    I have created a non-subjective job evaluation spreadsheet, which takes data obtained from a work daily of an individual and is implemented in my spreadsheet. The columns of areas to be evaluated will average each individuals performance by averaging the individual to the rest of his shifts performance and a point system has been established depending on their work level. The situation I'm running into is I'm using the formula below to establish my point system. In an effort to be fair in every way possible, should a particular column end up with all zeros "0" this would then become the average, which should provide the individuals in this group with three "3" points. My forumula as it is set up will now only give one "1" point, when it should be three. The point system works great other than this one particular problem, when everyone has a zero. I'd sure appreciate anyone's suggestion how I might be able to add one more IF statement to this existing formula. Note: I29 is the cell for this particular column that represents the average of the column with data in it. I28 is 10% above average, I27 is 20% above average, I30 is 10% below average and I31 is 20% below average.
    point system is 1 point for 20% below average, 2 points for 10% below average, 3 points for average, 4 points for 10% above average and 5 points for 20% above average.

    =IF(ISTEXT(I3),0,IF(I3="",0,IF(I3<=$I$31,1,IF(I3<=$I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27,5,IF(I3<$I$27,4))))))))

    Thanks in advance!

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Could be a problem - you already have 7 IF's. It would be better if you could post a sample of your worksheet

    To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    I've attached a sample

    Using the formula from my first inquiry i've copied those columns/cells involved into this sample sheet; i.e. columns I & J. This spreadsheet was made in excel 2007, but I saved in for earlier versions of excel.. So I did loose some conditional formatting factors, but it should affect what I'm trying to accomplish.. Thanks again for your time & efforts to help me with this issue!
    Attached Files Attached Files

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Does this work? Put this in cell I29

    =IF(SUBTOTAL(1,I3:I24)=0,3,SUBTOTAL(1,I3:I24))

  5. #5
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    Didn't work

    I tried it, but it only changed my average to three and didn't affect anything in column "J", which is where my point system is derived from. I was hopeful though!

  6. #6
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    I figured it out..

    For those that might run into something similar to this...
    I needed to add another If statement like this....IF(AVERAGE(I3:I24)=0,3, it worked when I inserted into my existing formula:

    =IF(ISTEXT(I3),0,IF(I3="",0,IF(AVERAGE(I3:I24)=0,3,IF(I3<=$I$31,1,IF(I3<= $I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27, 5,IF(I3<$I$27,4)))))))))

    I appreciate the other suggestions as it made me think & think.....

  7. #7
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    I am no formula expert

    As the formulas in i27, i28, i30, i31 al depend on i29 could you not add a test against the value of i29

    I have also changed the 1st 2 If staements into 1 Or statement & changed the last if statement to else result
    =IF(OR(ISTEXT(I3),I3=""),0,IF($I$29=0,3,IF(I3<=$I$31,1,IF(I3<=$I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27,5,4)))))))

    Your formulas in i27, i28, i30, i31 all have a sum command which they do not need

    In cell i27 this formula
    =SUM(I29*0.2)+I29
    can be simplified as

    =(I29*0.2)+I29
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  8. #8
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2021
    Posts
    974

    Thanks Mudraker!

    I've learned a great deal from this site, more so than I have from training videos and/or some of the training books. I really appreciate all the advice & assistance!!

  9. #9
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    You are correct in that training videos, courses, books can only teach so much.
    I have found that sites like this one that really opens ones eyes as to the many different ways of acheiving the same result. Some more efficient than others.

  10. #10
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Quote Originally Posted by lilsnoop
    For those that might run into something similar to this...
    I needed to add another If statement like this....IF(AVERAGE(I3:I24)=0,3, it worked when I inserted into my existing formula:

    =IF(ISTEXT(I3),0,IF(I3="",0,IF(AVERAGE(I3:I24)=0,3,IF(I3<=$I$31,1,IF(I3<= $I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27, 5,IF(I3<$I$27,4)))))))))

    I appreciate the other suggestions as it made me think & think.....
    I forgot you said that you were using Excel 2007

    http://office.microsoft.com/en-us/ex...CH100648411033

  11. #11
    Registered User
    Join Date
    05-03-2007
    Posts
    5

    IF formula Question

    I’m trying to formulate an IF function but I am having trouble the conditions are as follows: IF five cells e.g. C1:C5 contain the numbers 1,2 & 3 then condition “C” IF five cells do not contain one of the 3 numbers then condition “B”. I would also like to know if this can be done with 7 cells e.g. D1:D7 the conditions are IF cells contain letters L, M & H then condition “C” IF cells do not contain one of the three letters then condition “B” The source from the five and seven cells are VLOOKUPS, I hope this is clear can anybody help me?

    Thanks in advance.


    New posting here http://www.excelforum.com/showthread.php?t=598768
    Last edited by oldchippy; 05-03-2007 at 05:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1