+ Reply to Thread
Results 1 to 29 of 29

Function IF

  1. #1
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Function IF

    Hi to all, I am italian and I write from Spain

    =IF(1<=I6<=6;"1"),IF(7<=I6<=12;"2");"3")

    I would:
    if I6 is > = 1 and < = 6 write 1 in I7
    If I6 > = 7 and > = 12 write 2 in I7
    If none write 3 in I7

    Why doesn't work??

    If I have in A1 a date, for example monday 21 april 2011, I want that in A2 it write 1 (first day of week), if it is tuesday write 2, and so on... how make it?
    It has to work with excel of other language where for example in spanish monday is lunes and so on...
    Thanks

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Function IF

    why not use excel function weekday?

    A2 =Weekday(A1)
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    10-25-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    71

    Re: Function IF

    Here is one example, it does require refering to another sheet or range of cells though.

    weekday-sample.xlsx

    It is either this method, chaning if statements together, or writing VBA code to do this I believe.

  5. #5
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    oeldere, it works, but if I don't write a date it results 6 , why !??! I want an empty cell if I don't put a date

    and in the other function it have 2 IF:
    if I6 is > = 1 and < = 6 write 1 in I7
    If I6 > = 7 and > = 12 write 2 in I7
    If none write 3 in I7

    not only < 7

    Thanks!

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    oeldere,
    OK

    but I speak about 2 different things:
    1) FUNCTION DAY OF THE WEEK:
    =DAY.WEEK(A1;2)
    it works perfectly !!!, but if I don't write a date it results 6 , why !??! I want an empty cell if I don't put a date

    2) FUNCTION IF:
    if I6 is between 1 and 6, excel has to write 1 in I7
    If I6 is between 7 and 12, excel has to write 2 in I7
    If more then 12, excel has to write 3 in I7

    Thanks!

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    You can use the same solution for that.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    It give me error

  10. #10
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    OK now it works!!!

    Now the second problem is:

    FUNCTION IF:
    if I6 is between 1 and 6, excel has to write 1 in I7
    If I6 is between 7 and 12, excel has to write 2 in I7
    If more then 12, excel has to write 3 in I7

    Thanks!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    I reply on your #7 (point 1))

    maybe in spanish weekday is called differant.

    or more likely, change the collon (,) in semi collon (;)

  12. #12
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    yes the problem was to change the collon (,) in semi collon (;)

    Now the second problem is:

    FUNCTION IF:
    if I6 is between 1 and 6, excel has to write 1 in I7
    If I6 is between 7 and 12, excel has to write 2 in I7
    If more then 12, excel has to write 3 in I7

    Thanks!

  13. #13
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    ...and the third and last problem is:
    If A1=1 or A2=1 or A3=1 or A4=1 or A5=1, then write 1 in A6

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    Use Vlookup for that.

    See the attached file.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    In addition to your 3 question.

    Please Login or Register  to view this content.
    I don't understand the question => in your example is A1 a date (and will therefore never be 1)

  16. #16
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    Quote Originally Posted by oeldere View Post
    Use Vlookup for that.

    See the attached file.
    This works perfectly!!
    But I can't copy it for my 300 cells because it changes the table number
    For example (in my language) this works
    =CERCA.VERT(I3;formule!A1:B4;2;1)
    but if I copy it in the cell below it goes out so:
    =CERCA.VERT(I4;formule!A2:B5;2;1)

    but it is wrong because the table is in A1:B4 !!!
    It should be coorect write:
    =CERCA.VERT(I4;formule!A1:B4;2;1)
    but I Should have all by hand?!?
    how to copy all with always the same table?
    Thanks

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    Please Login or Register  to view this content.
    See the red text ($)

  18. #18
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    Quote Originally Posted by oeldere View Post
    In addition to your 3 question.

    Please Login or Register  to view this content.
    I don't understand the question => in your example is A1 a date (and will therefore never be 1)
    Perfect!
    Now it works all!
    Only this has the same problem:
    Please Login or Register  to view this content.
    If no numbers in a1 or a2 or a3 or a4 or 15 then No number in A6
    If there is number, then
    Please Login or Register  to view this content.
    because my formula is:
    Please Login or Register  to view this content.
    I need 1 if it is true, 2 if it is false, no number if no number in 1 or more of 5 cells
    Last edited by gexxy; 01-21-2013 at 08:01 PM.

  19. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    Please Login or Register  to view this content.
    Please test it, it should work.

    It's an OR statement, so if everything is empty, a6 is empty also.

  20. #20
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    I need 1 if it is true, 2 if it is false, no number if no number in 1 or more of 5 cells

    Please Login or Register  to view this content.

  21. #21
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Function IF

    another approach I think is

    another approach is

    =IF(COUNTIF(A1:A5,1)>0,1,2)
    or
    =if(count(A1:A5)>0,1,2)

    don't know how to write it in your language.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  22. #22
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    Quote Originally Posted by vlady View Post
    another approach I think is

    another approach is

    =IF(COUNTIF(A1:A5,1)>0,1,2)
    or
    =if(count(A1:A5)>0,1,2)

    don't know how to write it in your language.
    It's not correct...it leaves 2 if there isn't numbers in 5 cells
    I want empty space if no numbers

  23. #23
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Function IF

    change the 2 to ""

  24. #24
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Function IF

    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    I solved so:
    =SE(O(AI3="";AJ3="";AK3="";AL3="";AM3="");"";SE(O(AI3=1;AJ3=1;AK3=1;AL3=1;AM3=1);1;"2"))

    Is it correct?
    THANKS!!!

  26. #26
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Function IF

    =if(count(A1:A5)=0,"",if(countif(A1:A5,1)=5,1,2))

  27. #27
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    other question please:

    I have 3 column:
    In column A for example I have these numbers: 1,2,5,6
    In column B I have : 3,4,7,9
    In column C I have: 8,10,11,12

    If I write in D1 one of the numbers in column A, then in E1 excel has to write "1"
    If I write in D1 one of the numbers in column B, then in E1 excel has to write "2"
    If I write in D1 one of the numbers in column C, then in E1 excel has to write "3"

    The columns have to stay in another page, not in the same page of D1 and E1
    Thanks!

  28. #28
    Registered User
    Join Date
    01-21-2013
    Location
    barcelona, spain
    MS-Off Ver
    Excel 2010 but 2007 and 2003 too
    Posts
    52

    Re: Function IF

    someone can help me please?

  29. #29
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Function IF

    try

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    -array formula
    you have to hit ctrl+shift+enter not just enter, you'll notice the formula will be enclosed with curly braces.

+ 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