If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count number of people
We will be hosting a large fund raiser. I need to know the number of people
attending. Here's my problem................ Some people come as couples, and some as singles. For example, I have listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann Johnson. If ALL of these people RSVP with a yes, it represents 4 attendees. I think the key to determining if it is 1 or 2 people attending is the ampersand "&". If I have an ampersand, it should count as two; no ampersand, it is 1. How can I use a function or functions to make this distinction, anc correctly enter the number of attendees in the spredsheet? |
#3
|
|||
|
|||
Count number of people
=ISTEXT(A1)+ISNUMBER(FIND("&",A1))
best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme REMINDER: These newsgroups are about to die. We will all meet again at http://social.answers.microsoft.com/...ry/officeexcel "burtlake" wrote in message news We will be hosting a large fund raiser. I need to know the number of people attending. Here's my problem................ Some people come as couples, and some as singles. For example, I have listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann Johnson. If ALL of these people RSVP with a yes, it represents 4 attendees. I think the key to determining if it is 1 or 2 people attending is the ampersand "&". If I have an ampersand, it should count as two; no ampersand, it is 1. How can I use a function or functions to make this distinction, anc correctly enter the number of attendees in the spredsheet? |
#4
|
|||
|
|||
Count number of people
This is easy and it works. Now I need to understand why! Thanks!!
"Bernard Liengme" wrote: =ISTEXT(A1)+ISNUMBER(FIND("&",A1)) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme REMINDER: These newsgroups are about to die. We will all meet again at http://social.answers.microsoft.com/...ry/officeexcel "burtlake" wrote in message news We will be hosting a large fund raiser. I need to know the number of people attending. Here's my problem................ Some people come as couples, and some as singles. For example, I have listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann Johnson. If ALL of these people RSVP with a yes, it represents 4 attendees. I think the key to determining if it is 1 or 2 people attending is the ampersand "&". If I have an ampersand, it should count as two; no ampersand, it is 1. How can I use a function or functions to make this distinction, anc correctly enter the number of attendees in the spredsheet? |
#5
|
|||
|
|||
Count number of people
=ISTEXT(A1) returns TRUE if A1 has some text in it otherwise FALSE
=FIND("&",A1) returns a number representing the position of & in the A1 string if there is a & and an error value if not =ISNUMBER(...) returns TRUE if FIND returns a number, otherwise FALSE So we have four possible outcomes: TRUE + TRUE TRUE + FALSE FALSE + TRUE (actually this one is unlikely!) FALSE + FALSE But when Excel see an arithmetic operator (here the addition operator +) between two Boolean values (TRUE/FALSE) it treats TRUE as 1 and FALSE as 0. So we will get 1 when there is text but no & 2 when there is text and there is a & best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme REMINDER: These newsgroups are about to die. We will all meet again at http://social.answers.microsoft.com/...ry/officeexcel "burtlake" wrote in message ... This is easy and it works. Now I need to understand why! Thanks!! "Bernard Liengme" wrote: =ISTEXT(A1)+ISNUMBER(FIND("&",A1)) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme REMINDER: These newsgroups are about to die. We will all meet again at http://social.answers.microsoft.com/...ry/officeexcel "burtlake" wrote in message news We will be hosting a large fund raiser. I need to know the number of people attending. Here's my problem................ Some people come as couples, and some as singles. For example, I have listed Jim & Susan Brown. But in the same list I have Bill Smith, and Ann Johnson. If ALL of these people RSVP with a yes, it represents 4 attendees. I think the key to determining if it is 1 or 2 people attending is the ampersand "&". If I have an ampersand, it should count as two; no ampersand, it is 1. How can I use a function or functions to make this distinction, anc correctly enter the number of attendees in the spredsheet? |
Thread Tools | |
Display Modes | |
|
|