Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Hall-Of-Famer
    Points: 103,289, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 15.0%
    Achievements:
    Veteran50000 Experience PointsSocial
    ruxCYtable's Avatar
    Join Date
    Aug 2007
    Posts
    3,763
    Points
    103,289
    Level
    100
    Thumbs Up
    Received: 22
    Given: 11

    Any Excel formula experts out there?

    Here's what I've got.

    column a = last name
    column b = first name
    column c = spouse's first name

    I'm using concatenate to combine the columns but want to be able to exclude column c ONLY if it is blank.

    Example:

    col a: smith
    col b: john
    col c: karen

    returns result: smith, john and karen

    but if it was

    col a: smith
    col b: john
    col c: (blank)

    currently returns smith, john and

    Would rather if c is blank it left off the and and ignored the blank cell. Is there a way to do this?



  2. #2
    Rookie
    Points: 8,193, Level: 27
    Level completed: 8%, Points required for next Level: 557
    Overall activity: 0%
    Achievements:
    Veteran5000 Experience Points
    conmebol's Avatar
    Join Date
    Apr 2006
    Posts
    417
    Points
    8,193
    Level
    27
    Thumbs Up
    Received: 0
    Given: 1

    Re: Any Excel formula experts out there?

    can't you just use the if statement.



  3. #3
    Starter
    Points: 19,682, Level: 42
    Level completed: 82%, Points required for next Level: 168
    Overall activity: 24.0%
    Achievements:
    Veteran10000 Experience Points

    Join Date
    Apr 2008
    Location
    Pants-free Avenue
    Posts
    806
    Points
    19,682
    Level
    42
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by ruxCYtable View Post
    Here's what I've got.

    column a = last name
    column b = first name
    column c = spouse's first name

    I'm using concatenate to combine the columns but want to be able to exclude column c ONLY if it is blank.

    Example:

    col a: smith
    col b: john
    col c: karen

    returns result: smith, john and karen

    but if it was

    col a: smith
    col b: john
    col c: (blank)

    currently returns smith, john and

    Would rather if c is blank it left off the and and ignored the blank cell. Is there a way to do this?
    You need to and an if statement in there. Something like:

    if(col c = "",concatenate(col a,", ",col b),concatenate(col a,", ",col b," and ",col c))


    ...Straight going donkey...

  4. #4
    Pro
    Points: 24,458, Level: 47
    Level completed: 91%, Points required for next Level: 92
    Overall activity: 0%
    Achievements:
    SocialVeteranCreated Album pictures10000 Experience Points
    MidwestZest's Avatar
    Join Date
    Apr 2006
    Location
    Decatur, IL
    Posts
    2,003
    Points
    24,458
    Level
    47
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    I didn't even know concatenate was a word, let alone an excel file term.....



  5. #5
    Starter
    Points: 19,253, Level: 42
    Level completed: 34%, Points required for next Level: 597
    Overall activity: 0%
    Achievements:
    1 year registered10000 Experience Points
    CyFever's Avatar
    Join Date
    Dec 2009
    Location
    Phoenix, AZ
    Posts
    931
    Points
    19,253
    Level
    42
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Use an if/then statement. First check to see if column c is blank. If it is, then use all three columns. If it is not, then use only the first two columns.

    I'll leave you to puzzle through all the correct syntax yourself.



  6. #6
    Speechless
    Points: 426,205, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 20.0%
    Achievements:
    SocialRecommendation Second ClassVeteranCreated Album pictures50000 Experience Points

    Join Date
    Nov 2007
    Location
    Ames
    Posts
    23,643
    Points
    426,205
    Level
    100
    Thumbs Up
    Received: 37
    Given: 1

    Re: Any Excel formula experts out there?

    Quote Originally Posted by MidwestZest View Post
    I didn't even know concatenate was a word, let alone an excel file term.....
    Combines fields.

    If cell A1 says "Midwest" and B1 says "Zest" and you put, =CONCATENATE(A1,B1) in C1, it will return "MidwestZest".



  7. #7
    Addict
    Points: 112,768, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 18.0%
    Achievements:
    SocialVeteran50000 Experience Points
    ianoconnor's Avatar
    Join Date
    Nov 2007
    Location
    Des Moines
    Posts
    6,868
    Points
    112,768
    Level
    100
    Thumbs Up
    Received: 89
    Given: 221

    Re: Any Excel formula experts out there?

    I love Excel.



  8. #8
    Hall-Of-Famer
    Points: 85,801, Level: 91
    Level completed: 14%, Points required for next Level: 1,649
    Overall activity: 0%
    Achievements:
    SocialVeteran50000 Experience PointsRecommendation First Class
    Haverhill's Avatar
    Join Date
    Jan 2009
    Location
    Lawrence, KS
    Posts
    4,202
    Points
    85,801
    Level
    91
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by ianoconnor View Post
    I love Excel.
    It's very cool once u learn the different formulas you need. However, there are soooo many of them!!



  9. #9
    Pro
    Points: 62,260, Level: 77
    Level completed: 39%, Points required for next Level: 990
    Overall activity: 0%
    Achievements:
    SocialVeteran50000 Experience Points
    azn4cy's Avatar
    Join Date
    Dec 2008
    Location
    DSM
    Posts
    3,089
    Points
    62,260
    Level
    77
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by IcSyU View Post
    Combines fields.

    If cell A1 says "Midwest" and B1 says "Zest" and you put, =CONCATENATE(A1,B1) in C1, it will return "MidwestZest".
    You are such a nerd.



  10. #10
    All-Star
    Points: 23,058, Level: 46
    Level completed: 51%, Points required for next Level: 492
    Overall activity: 0%
    Achievements:
    Veteran10000 Experience Points

    Join Date
    Aug 2007
    Location
    Ames
    Posts
    1,820
    Points
    23,058
    Level
    46
    Thumbs Up
    Received: 2
    Given: 7

    Re: Any Excel formula experts out there?

    =IF(ISBLANK(C1),CONCATENATE(A1,", ",B1),CONCATENATE(A1,", ",B1," and ",C1))


    Last edited by TedKumsher; 05-26-2010 at 12:02 PM.
    May my postings be polite and not misunderstood. (And hopefully funny on occasion.)

  11. #11
    All-Star
    Points: 31,093, Level: 54
    Level completed: 23%, Points required for next Level: 857
    Overall activity: 0%
    Achievements:
    Veteran25000 Experience Points
    CykoAGR's Avatar
    Join Date
    Dec 2008
    Location
    Waukee, IA
    Posts
    1,672
    Points
    31,093
    Level
    54
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by ruxCYtable View Post
    Here's what I've got.

    column a = last name
    column b = first name
    column c = spouse's first name

    I'm using concatenate to combine the columns but want to be able to exclude column c ONLY if it is blank.

    Example:

    col a: smith
    col b: john
    col c: karen

    returns result: smith, john and karen

    but if it was

    col a: smith
    col b: john
    col c: (blank)

    currently returns smith, john and

    Would rather if c is blank it left off the and and ignored the blank cell. Is there a way to do this?

    If you have trouble with the formula(s) to get this to work couldnt you also sort your list by column c? This would group all of the blank/non-blank cells in column c together and then just stop the concant function at the empty cells. Then you would have to create another concant formula to get the first and last name of said friends with no spouses.


    “It’s beyond pride. It’s a restored trust. There’s a confidence again, a passion that teeters on swagger. More than anything, (Iowa State head coach Paul Rhoads) has restored the Iowa State “it” factor, the steadfast belief that it is great to be a Cyclone...the man’s enthusiasm is genuine to the core...”
    – columnist Sean Keeler, Des Moines Register

  12. #12
    Hall-Of-Famer
    Points: 83,379, Level: 89
    Level completed: 80%, Points required for next Level: 371
    Overall activity: 0%
    Achievements:
    Veteran50000 Experience Points

    Join Date
    May 2006
    Location
    Houston, TX
    Posts
    3,580
    Points
    83,379
    Level
    89
    Thumbs Up
    Received: 12
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by TedKumsher View Post
    =IF(ISBLANK(C1),CONCATENATE(A1,", ",B1),CONCATENATE(A1,", ",B1," and ",C1))
    This. For the record that is NOT an expert level question.




  13. #13
    Starter
    Points: 18,045, Level: 40
    Level completed: 99%, Points required for next Level: 5
    Overall activity: 0%
    Achievements:
    Veteran10000 Experience Points
    linkshero's Avatar
    Join Date
    May 2008
    Location
    Ames
    Posts
    919
    Points
    18,045
    Level
    40
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by cyeah View Post
    This. For the record that is NOT an expert level question.

    Yet, everyone is an Excel expert on their Resume........


    Lifetime Achievement- In on page 1 of a THujone MS Paint thread.


  14. #14
    All-Star
    Points: 23,058, Level: 46
    Level completed: 51%, Points required for next Level: 492
    Overall activity: 0%
    Achievements:
    Veteran10000 Experience Points

    Join Date
    Aug 2007
    Location
    Ames
    Posts
    1,820
    Points
    23,058
    Level
    46
    Thumbs Up
    Received: 2
    Given: 7

    Re: Any Excel formula experts out there?

    Quote Originally Posted by cyeah View Post
    This. For the record that is NOT an expert level question.

    But:

    "Any Excel formula half-way-decent people out there?"

    doesn't sound nearly as cool.



    May my postings be polite and not misunderstood. (And hopefully funny on occasion.)

  15. #15
    Addict
    Points: 119,458, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Overall activity: 3.0%
    Achievements:
    SocialVeteran50000 Experience Points
    jsmith86's Avatar
    Join Date
    Dec 2006
    Location
    Tulsa
    Posts
    6,193
    Points
    119,458
    Level
    100
    Thumbs Up
    Received: 0
    Given: 0

    Re: Any Excel formula experts out there?

    Quote Originally Posted by linkshero View Post
    Yet, everyone is an Excel expert on their Resume........
    But I know how to use the equals sign to add two cells together and I know how to add titles to my graphs...


    ____________________________________

    You can't die. That would upset the equilibrium in the Cave.
    Warning: Posts contain 98% post-sarcastic content.
    Hoiball

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • Football
  • Iowa State vs. North Dakota State
  • August 30, 2014
  • 06:00 PM