Results 1 to 6 of 6
  1. #1
    djbluesbar
    Guest

    Problemas con funcion

    Tengo un problema con una funcion "SI" anidada. Necesito que dando un valor a una celda me busque, dependiendo del valor en 12 rangos (mensuales) distintos, y mi problema es que no me deja anidar mas de 7 "SI".
    Hay alguna manera de solventar esto??
    Si alguien lo sabe hacer con codigo, y es posible que me lo postee aqui o me lo envie.

    Muchas Gracias.

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problemas con funcion

    Perdone mi espa
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    djbluesbar
    Guest

    Re: Problemas con funcion

    Yes, I post you my file and a little explain inside it.
    I think you can understand it..
    If no, just post me a message ...

    Thanks.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problemas con funcion

    Hello,

    I inserted 2 columns (A & [img]/forums/images/smilies/cool.gif[/img] and entered numbers from 1 to 12 in A, and the composed a VLOOKUP, using the fact that columns of values are spaced 4 columns apart.

    DataTable =Hoja1!$A$3:$AW$14
    G3 = VLOOKUP(D38,DataTable,D38*4,FALSE)

    Does that help ?

    Andrew C
    Attached Files Attached Files

  5. #5
    djbluesbar
    Guest

    Re: Problemas con funcion

    I think that it would work....
    I will try to complete all the sheet and i will tell you...
    Thanks a lot..

    I was trying to do it more difficult that it was... i think... Thanks again for all..!!!!

    I

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problemas con funcion

    It is possible to overcome the limit of 7 nested IFs by using named formulas. The last CONDITION_FALSE in the first formula is the name of the second formula. For example for your problem define FirstIF as

    =IF(Sheet1!A1=1,"Jan",IF(Sheet1!A1=2,"Feb",IF(Shee t1!A1=3,"Mar",IF(Sheet1!A1=4,"Apr",IF(Sheet1!A1=5, "May",IF(Sheet1!A1=6,"Jun",SecondIF)))))).

    Then define SecondIF as

    =IF(Sheet1!A1=7,"Jul",IF(Sheet1!A1=8,"Aug","ERROR" ))

    I have only taken the example up to August, but obviously it can be extended. This way there does not seem to be a limit on the number of nested IFs.

    Beware that as written the references are relative and odd things happen if you have the selected cell in a different location from where it was when you wrote the formula. You may think it better to use absolute references.

    To use these nested IFs go to the cell where you want the result then use Insert/Name/Paste and paste FirstIF in the cell.

Posting Permissions

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