Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Creating Formula

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My table

    A B C D
    1 Model Price Value Profit
    2 SN1 153 763 333
    3 SN2 876 788 987
    4 SN3 769 331 768

    If I enter the Model number SN3 in cell A5 and Value in cell A6, I need a formual that I can put in cell A7 that will return "331".

    I'm sorry I didn't understand the previous attempts to help me.

    Thanks!

  2. #2
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    {=sum(if((a5=a1:a4)*(a6=c1:c4), b1:b4,0))}

    enter this as an array (control-shift-enter)

    [ This Message was edited by: zacemmel on 2002-05-09 15:15 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

    =INDIRECT(A5) INDIRECT(A6)

    ... into cell A7.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-09 15:10, Bunderwood75 wrote:
    My table

    A B C D
    1 Model Price Value Profit
    2 SN1 153 763 333
    3 SN2 876 788 987
    4 SN3 769 331 768

    If I enter the Model number SN3 in cell A5 and Value in cell A6, I need a formual that I can put in cell A7 that will return "331".

    I'm sorry I didn't understand the previous attempts to help me.

    Thanks!
    I'm sorry I didn't understand the previous attempts to help me.

    If you like, just ask a copy of the WB that shows how the OFFSET formula that I suggested works at:

    aladin_akyurek@yahoo.com

    Anycase, there is no need for using an array formula for this retrieval task.


  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 15:17, Mark W. wrote:
    Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

    =INDIRECT(A5) INDIRECT(A6)

    ... into cell A7.
    Hi Bunderwood75:
    If i may elaborate on what Mark W has provided ...
    the space between the two INDIRECTs is the interesection operator,
    so a caution here, after you enter the formula with the INTERSECT operator, Excel may offer to provide a needed correction by offering to substitute the space with the * character ... and of course that offer has to be rejected.

    I hope I didn't go overboard on this one.

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 15:17, Mark W. wrote:
    Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

    =INDIRECT(A5) INDIRECT(A6)

    ... into cell A7.
    Hi Mark:
    Since you have facilitated Range Names as Labels already, I can also write my formula diectly without having to make entries in cells A5 and A6 (I do reconize that the OP had stated putting the values in cell A5 and A6)--

    =SN3 Value

    giving me a result of 331

    Of course being able to write the formula with Label Names can be so much more meaningful as it provides for direct interpretation.

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 18:45, Yogi Anand wrote:
    On 2002-05-09 15:17, Mark W. wrote:
    Select cells A1:D4, choose the Insert | Name | Create... menu command and check both "Top row" and "Left column". Then enter...

    =INDIRECT(A5) INDIRECT(A6)

    ... into cell A7.
    Hi Mark:
    Since you have facilitated Range Names as Labels already, I can also write my formula diectly without having to make entries in cells A5 and A6 (I do reconize that the OP had stated putting the values in cell A5 and A6)--

    =SN3 Value

    giving me a result of 331

    Of course being able to write the formula with Label Names can be so much more meaningful as it provides for direct interpretation.

    Regards!

    Yes, I know... but, as you stated that wouldn't satisfy the original problem statement.

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mark: T H A N K S.

    Regards!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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