+ Reply to Thread
Results 1 to 6 of 6

#VALUE! error nesting IF function

  1. #1
    Registered User
    Join Date
    04-30-2006
    Posts
    61

    #VALUE! error nesting IF function

    In A1 I have a formula that can have 7 results: a,b,c,d,e,f,g

    In range A65:A95 I have the results I want if "a", B65:B95 results if "b" and so on.

    What I want is if A1=a, then A2:A32 will fill up w/ the range from A65:A95

    What I came up w/ for cell A2 was

    =IF(A1="a",A65),IF(A1="b",B65),IF(A1="c",C65),IF(A1="d",D65),IF(A1="e",E65),IF(A1="f",F65),IF(A1="g",A65)

    I'm getting a #VALUE! error. What am I doing wrong or is there a better way to do this?

    Thanks,

  2. #2
    Registered User
    Join Date
    04-30-2006
    Posts
    61
    ok it's been a long day, I realized what I was doing wrong, but I still want to know if this is the best option?

    Here's my formula

    =IF(A1="a",A65,IF(A1="b",B65,IF(A1="c",C65,IF(A1="d",D65,IF(A1="e",E65,IF(A1="f",F65,IF(A1="g",G65)))))))

  3. #3
    Biff
    Guest

    Re: #VALUE! error nesting IF function

    Hi!

    This will work as a replcement for your formula:

    =INDEX(A65:G65,MATCH(A1,{"a","b","c","d","e","f","g"},0))

    But I have no idea what your tying to do with this:

    > In range A65:A95 I have the results I want if "a", B65:B95 results if
    > "b" and so on.
    >
    > What I want is if A1=a, then A2:A32 will fill up w/ the range from
    > A65:A95


    Biff

    "fastballfreddy"
    <fastballfreddy.27iqny_1147141801.1655@excelforum-nospam.com> wrote in
    message news:fastballfreddy.27iqny_1147141801.1655@excelforum-nospam.com...
    >
    > In A1 I have a formula that can have 7 results: a,b,c,d,e,f,g
    >
    > In range A65:A95 I have the results I want if "a", B65:B95 results if
    > "b" and so on.
    >
    > What I want is if A1=a, then A2:A32 will fill up w/ the range from
    > A65:A95
    >
    > What I came up w/ for cell A2 was
    >
    > =IF(A1="a",A65),IF(A1="b",B65),IF(A1="c",C65),IF(A1="d",D65),IF(A1="e",E65),IF(A1="f",F65),IF(A1="g",A65)
    >
    > I'm getting a #VALUE! error. What am I doing wrong or is there a
    > better way to do this?
    >
    > Thanks,
    >
    >
    > --
    > fastballfreddy
    > ------------------------------------------------------------------------
    > fastballfreddy's Profile:
    > http://www.excelforum.com/member.php...o&userid=33986
    > View this thread: http://www.excelforum.com/showthread...hreadid=540116
    >




  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Perhaps

    =IF($A$1={"a";"b";"c"},INDIRECT(A1&"65"))

    would be a better clue

    --

    Quote Originally Posted by fastballfreddy
    ok it's been a long day, I realized what I was doing wrong, but I still want to know if this is the best option?

    Here's my formula

    =IF(A1="a",A65,IF(A1="b",B65,IF(A1="c",C65,IF(A1="d",D65,IF(A1="e",E65,IF(A1="f",F65,IF(A1="g",G65)))))))

  5. #5
    Registered User
    Join Date
    04-30-2006
    Posts
    61
    thanks for everybody's replies. My formula did work, but Biff's formula is what I was looking for. Thanks again.

  6. #6
    Andre Croteau
    Guest

    Re: #VALUE! error nesting IF function

    Hi,

    Create a range name "A" for your range A65:A95
    Create a range name "B" for your range B65:B95 etc

    in cells A2:A32 (as a group), enter this array formula =indirect(A1)
    (while pressing Shift+ Ctrl+Enter)

    So now, when you enter "a" in cell A1, this wll populate your cells A2:A32
    with values found in cells A65:A95

    Cheers!

    André





    "fastballfreddy"
    <fastballfreddy.27iqny_1147141801.1655@excelforum-nospam.com> wrote in
    message news:fastballfreddy.27iqny_1147141801.1655@excelforum-nospam.com...
    >
    > In A1 I have a formula that can have 7 results: a,b,c,d,e,f,g
    >
    > In range A65:A95 I have the results I want if "a", B65:B95 results if
    > "b" and so on.
    >
    > What I want is if A1=a, then A2:A32 will fill up w/ the range from
    > A65:A95
    >
    > What I came up w/ for cell A2 was
    >
    > =IF(A1="a",A65),IF(A1="b",B65),IF(A1="c",C65),IF(A1="d",D65),IF(A1="e",E65),IF(A1="f",F65),IF(A1="g",A65)
    >
    > I'm getting a #VALUE! error. What am I doing wrong or is there a
    > better way to do this?
    >
    > Thanks,
    >
    >
    > --
    > fastballfreddy
    > ------------------------------------------------------------------------
    > fastballfreddy's Profile:
    > http://www.excelforum.com/member.php...o&userid=33986
    > View this thread: http://www.excelforum.com/showthread...hreadid=540116
    >




+ 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