+ Reply to Thread
Results 1 to 8 of 8

function to return text when first value found but different text for subsequent numbers

  1. #1
    Forum Contributor
    Join Date
    09-15-2012
    Location
    las vegas
    MS-Off Ver
    excel 2007
    Posts
    147

    function to return text when first value found but different text for subsequent numbers

    I have 1,2,3,4,5 for cells A2,B2,C2,D2, and E2. I'm trying to figure a function wherein the first cell on row 2 that contains a number will result in ABC for A1 and the rest (i.e. B1,C2,D2,and E2) will result in ZZZ.

    See image for clearer explanation.

    Thanks guys.
    Attached Images Attached Images
    Last edited by managingcrap; 11-08-2013 at 08:32 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: If function pls help

    =IF(A1<1,"",IF(COUNTA($A1:A1)=1,"ABC","ZZZ"))

    copy to the rihgt column

  3. #3
    Forum Contributor
    Join Date
    09-15-2012
    Location
    las vegas
    MS-Off Ver
    excel 2007
    Posts
    147

    Re: If function pls help

    I assume when you mean A1, you mean A5.

    The only problem is that when A4 is zero, the B4 is still giving me ZZZ instead of ABC since the number now (other than zero) is on B6.
    Last edited by managingcrap; 11-08-2013 at 07:59 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: If function pls help

    send excel attachment and describe what you desired

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If function pls help

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Forum Contributor
    Join Date
    09-15-2012
    Location
    las vegas
    MS-Off Ver
    excel 2007
    Posts
    147

    Re: If function pls help

    Here's the attachment
    Attached Files Attached Files

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If function pls help

    title change please! try "function to return text when first value found but different text for subsequent numbers"

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: function to return text when first value found but different text for subsequent numbe

    Maybe try this...
    =if(sum(A2:E2)>0,"ABC","ZZZ")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  2. [SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets
    By Touch9713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2013, 10:09 PM
  3. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  4. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  5. Replies: 2
    Last Post: 03-20-2009, 01:29 PM

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