Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    CHICAGO METRO
    Posts
    1

    Unanswered: excel if statements

    =IF(I6-E7=0,B3)+D7+I5
    created the above formula where if I6-E7=0 to place the value of B3 plus values in D7 and I5. That works, however, my data also has other possibilities other than 0; 1, 2 , 3 ,4, & 5. For each possibility wanting to associate a different value for the result, say if I6-E7= 1, C3, etc. The D7 and I5 additions remain constant. Getting nothing but errors in trying to add the additional IF statements. Any help?

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    You need to nest the IF statements:

    =IF(I6-E7=0,B3,IF(I6-E7=1,C3,IF(I6-E7=2,D3)+D7+I5)+D7+I5)+D7+I5)

    etc.

    Adjust as necessary. You can have a maximmun of seven nested IF statements (although there are ways to get around that limit).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2005
    Posts
    25
    was wondering if somebody out there would be able to help me. Currently working with a spreadsheet that has date values (ex. 3/2/02), wanted to find out how to use an IF function to get excel to recognize a date as being in a specific quarter....for example

    10/1-12/31 = Q1
    1/1-3/31 = Q2
    4/1-6/30 = Q3
    7/1-9/30 = Q4

    The formula i came up with is below:

    =IF(D105>9/30,"Q1", IF(D105>12/31,"Q2",IF(D105>3/31, "Q3", IF(D105>6/30, "Q4"))))

    D105 is the date (3/15/2002)....using the formula, the end result is "Q1" which is incorrect. (The result for all dates is "Q1" using the current formula).

    What am I doing wrong?? Please help!!

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    rework your formula to include months
    as what you have in your first statement is looking for everything over 9/30 i.e. 0.3
    and all your dates will be over this mark

    try something like
    =IF(MONTH(D105)<=3,"Q2",IF(MONTH(D105)<=6,"Q3",IF( MONTH(D105)<=9,"Q4","Q1")

    i cant remember if there is a quarters function within excel but you could work with that if there is (I use star office at home),

    HTH
    Dave

  5. #5
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Same question was asked in previous thread

    Same question was asked in previous thread.

    We came up with approximately the same answer.

    http://www.dbforums.com/t1119633.html

  6. #6
    Join Date
    Apr 2011
    Posts
    4

    Additional-

    When Excel calculats the dates isnt it using the number format? Like may 1, 2007 is really "3423432" or whatever? Does this make a difference in the computation?

    -C.W.
    Excel Statement

  7. #7
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I worked out the formula below to calculate the quarter. If someone can think of why it might fail, give your advice on this. Attached is proof that the formula works for every day of the year.
    Jerry

    ="Q" & INT(MONTH(A1)/3.1)+1
    Attached Files Attached Files

  8. #8
    Join Date
    May 2011
    Posts
    1

    Thanks

    Hi, I was trying to figuire out this whole If thing and after your post I could at least understand something, thanks a lot you guys!



    You can switch encoding parameters of end files such as example frequencyp
    Last edited by Pat Phelan; 05-17-11 at 15:55. Reason: removed unrelated URL

  9. #9
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Attached is my alternate solution to the nested IF statements question.
    Use of the OFFSET function makes the formula shorter than nested IFs.

    As I understand the question, (I6-E7) will always be a value between 0 and 5, and these 6 categories will be used to reference values in columns B through G. The sum of (+D7+I5) is always included in the result.

    The formula:
    =OFFSET(A15,-12, (I6-E7)+1,1,1)+D7+I5
    Attached Files Attached Files

Posting Permissions

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