Is there a formula to find one phrase in a selection?

Hey again,

Is there a formula I can use to figure out if there is one specific phrase 
in a selection?

I'm updating the attendance prgram at the school that I work at. Every 
student has their own attendance sheet in Excel, and each sheet sort of looks 
like this:

             |Monday|
Period 1:|     A    |        (A=Absent; S=Seat time)
Period 2:|     S    |
Period 3:|     A    |

So basically, if they have one or more "S"s, we can count them as being 
present for the day. I need the formula to find out if there is an "S" in 
that column, and if there is, I need it to type an "S" in the attendance 
report, which sort of looks like this:
DATE:             |1|2|3|
Student Name |S|  |  |

Sorry if that got a little complicated. Is there any way to do this?
0
Tiff1618 (10)
9/1/2005 9:22:02 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
893 Views

Similar Articles

[PageSpeed] 50

one way

=IF(COUNTIF(B1:B3,"S")>=1,"S","A")
Modify range as needed
HTH


"Tiff1618" wrote:

> Hey again,
> 
> Is there a formula I can use to figure out if there is one specific phrase 
> in a selection?
> 
> I'm updating the attendance prgram at the school that I work at. Every 
> student has their own attendance sheet in Excel, and each sheet sort of looks 
> like this:
> 
>              |Monday|
> Period 1:|     A    |        (A=Absent; S=Seat time)
> Period 2:|     S    |
> Period 3:|     A    |
> 
> So basically, if they have one or more "S"s, we can count them as being 
> present for the day. I need the formula to find out if there is an "S" in 
> that column, and if there is, I need it to type an "S" in the attendance 
> report, which sort of looks like this:
> DATE:             |1|2|3|
> Student Name |S|  |  |
> 
> Sorry if that got a little complicated. Is there any way to do this?
0
RayA (96)
9/1/2005 9:40:37 PM
Hi!

=IF(COUNTIF(B2:B100,"S"),"S",not_defined)

Not_defined means that you haven't defined what to do if there are no S's.

Do you want an "A" for absent?

=IF(COUNTIF(B2:B100,"S"),"S","A")

Do you want to leave the cell balnk?

=IF(COUNTIF(B2:B100,"S"),"S","")

Biff

"Tiff1618" <Tiff1618@discussions.microsoft.com> wrote in message 
news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> Hey again,
>
> Is there a formula I can use to figure out if there is one specific phrase
> in a selection?
>
> I'm updating the attendance prgram at the school that I work at. Every
> student has their own attendance sheet in Excel, and each sheet sort of 
> looks
> like this:
>
>             |Monday|
> Period 1:|     A    |        (A=Absent; S=Seat time)
> Period 2:|     S    |
> Period 3:|     A    |
>
> So basically, if they have one or more "S"s, we can count them as being
> present for the day. I need the formula to find out if there is an "S" in
> that column, and if there is, I need it to type an "S" in the attendance
> report, which sort of looks like this:
> DATE:             |1|2|3|
> Student Name |S|  |  |
>
> Sorry if that got a little complicated. Is there any way to do this? 


0
biffinpitt (3172)
9/1/2005 9:46:14 PM
Oh my god, you guys are brilliant. Thank you so much!

"Biff" wrote:

> Hi!
> 
> =IF(COUNTIF(B2:B100,"S"),"S",not_defined)
> 
> Not_defined means that you haven't defined what to do if there are no S's.
> 
> Do you want an "A" for absent?
> 
> =IF(COUNTIF(B2:B100,"S"),"S","A")
> 
> Do you want to leave the cell balnk?
> 
> =IF(COUNTIF(B2:B100,"S"),"S","")
> 
> Biff
> 
> "Tiff1618" <Tiff1618@discussions.microsoft.com> wrote in message 
> news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> > Hey again,
> >
> > Is there a formula I can use to figure out if there is one specific phrase
> > in a selection?
> >
> > I'm updating the attendance prgram at the school that I work at. Every
> > student has their own attendance sheet in Excel, and each sheet sort of 
> > looks
> > like this:
> >
> >             |Monday|
> > Period 1:|     A    |        (A=Absent; S=Seat time)
> > Period 2:|     S    |
> > Period 3:|     A    |
> >
> > So basically, if they have one or more "S"s, we can count them as being
> > present for the day. I need the formula to find out if there is an "S" in
> > that column, and if there is, I need it to type an "S" in the attendance
> > report, which sort of looks like this:
> > DATE:             |1|2|3|
> > Student Name |S|  |  |
> >
> > Sorry if that got a little complicated. Is there any way to do this? 
> 
> 
> 
0
Tiff1618 (10)
9/3/2005 12:01:01 AM
Oh, one more question. This is rediculously complicated, I know, but...  when 
you use that formula, can you put another IF(COUNTIF) check in the "value if 
false" spot? Because if there isn't an "S" in the column, it needs to be able 
to check for a "P" or an "N" (independent study or not enrolled) and mark 
that instead. 

I've been trying to figure it out with the =OR formula but I keep getting 
errors. This is what I've been typing that's not working and I don't know why:

=OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:AA10,"P"),"P","A"))))

Any ideas?

"Biff" wrote:

> Hi!
> 
> =IF(COUNTIF(B2:B100,"S"),"S",not_defined)
> 
> Not_defined means that you haven't defined what to do if there are no S's.
> 
> Do you want an "A" for absent?
> 
> =IF(COUNTIF(B2:B100,"S"),"S","A")
> 
> Do you want to leave the cell balnk?
> 
> =IF(COUNTIF(B2:B100,"S"),"S","")
> 
> Biff
> 
> "Tiff1618" <Tiff1618@discussions.microsoft.com> wrote in message 
> news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
> > Hey again,
> >
> > Is there a formula I can use to figure out if there is one specific phrase
> > in a selection?
> >
> > I'm updating the attendance prgram at the school that I work at. Every
> > student has their own attendance sheet in Excel, and each sheet sort of 
> > looks
> > like this:
> >
> >             |Monday|
> > Period 1:|     A    |        (A=Absent; S=Seat time)
> > Period 2:|     S    |
> > Period 3:|     A    |
> >
> > So basically, if they have one or more "S"s, we can count them as being
> > present for the day. I need the formula to find out if there is an "S" in
> > that column, and if there is, I need it to type an "S" in the attendance
> > report, which sort of looks like this:
> > DATE:             |1|2|3|
> > Student Name |S|  |  |
> >
> > Sorry if that got a little complicated. Is there any way to do this? 
> 
> 
> 
0
Tiff1618 (10)
9/3/2005 1:01:02 AM
Reply:

Similar Artilces:

How do I find a template I downloaded to Publisher?
I downloaded a template fom the microsoft site to use in Publisher 2003. I closed the page since I didn't want to use it right away, but now I can't find it. I looked in the "new from a design" pane, but it isn't listed anywhere. Where should I be looking to retrieve the template? If you didn't save it, then it is still at Microsoft. Could be in your temp folder. In Windows Explorer address bar type %temp% , Try, file, new, templates, scroll down to the bottom. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com...

Help with a simple formula #2
I'm new and trying to learn Excel so please excuse the simple questions. I want to create a formula on a spread sheet that will subtract the number that I will put in a new cell every month from a cell that never changes. This cell - with the answer - sits below the spreadsheet. PghPatti If I understand you correctly, you will need 3 cells. One cell, say A1, is the cell that never changes. The second cell, say B1, is the cell into which you type a number. The third cell, say C1, is the cell that has the answer of the second cell subtracted from the first cell. In the third cel...

adding equal/greater than into formula
Hi. This is relatively simple. I have the following existing formula in a spreadsheet: =IF(G9=F9,"",IF(H9>10/24,16,IF(H9>5/24,8,0))) Now - I would like it to calculate 8 if H9 is equal or greater than 5 Any sugestions? -- rvnwdr ------------------------------------------------------------------------ rvnwdr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23903 View this thread: http://www.excelforum.com/showthread.php?threadid=470359 One way: =IF(G9=F9,"",IF(H9>10/24,16,IF(H9>=5/24,8,0))) Another: =IF(G9=F9,&quo...

Outlook 2003 rules on more than one line
Hi, I was wondering if it was possible to use Outlook 2003 to make a rule in which all mails with the following string of text in the body; "something <empty line> <empty line> something else" is handled and mail with the following string isn't handled; "something <empty line> <any string of text that can be 1 or more lines long> something else" If I where able to include a line break in the making of the rule I wouldn't have a problem. A rule on size wouldn't work since the length of the mail varies and the above is just a portion o...

Find & Extract #2
Hi, I have a list of all mail return data where there is would be a mail i "The original message was received at Tue, 22 Jun 2004 19:21:4 +0530from [192.168.57.184] ----- The following addresses ha permanent fatal errors -----<mukundshah@indiatimes.com> (reason User unknown) ----- Transcript of session follows -----No user b that name.550 5.1.1 <mukundshah@indiatimes.com>... User unknown" Is there a formula where in I can find the e-mail id and it wil extract the same to a new sheet ? Luca -- Message posted from http://www.ExcelForum.com Hi Lucas! Here...

formula for a nested subform into other form
Hello!!!,,,I need an urgent help in a formula. I have the tables: [Ship] [Invoice] [Payments] IdShip (keyfield) ----------------> IdShip IdInvoice DateShipped IdInvoice (keyfield) ---------> AmountPaid Port AmountInvoiced DatePaid Customer DateInv I have a form A, which contains a key field &quo...

Message by changing one datafield if other fields are filled
Hello, In a database I have, for example, 15 datafields in a record. The first one is the AutoNr, the second one is what I called "the lead" and all the others are information. When I change "the lead" I want a popup 'only' if at least one of the information fields is filled with data. So (I think) I need a changemacro on the second datafield. The macro should do (I think)..... .) count the filled in datafields of the total datafields in the record. .) if the filled in datafields is more then 2 give a popup "blabla" Can somebody (please) help...

Adding cells that already contain formulas
I am trying to divide 2 cells (M1/L1) and L1 contains a formula that adds minutes together (I have the L1 cell formatted for minutes [m] - if that has anything to do with it). When it divides it comes up with a really wacky #. How do I get the cell to recognize the value, not the formula? Heather ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like this =M1/(L1*1440) -- Regards, Peo Sjoblom "hgraning" <hgraning.wp1hb@excelforum-nosp...

List Control Always Show Selection
Hi I'm using a list control in report mode with the Always Show Selection property set to TRUE so the selection is visible when the control doesn't have focus. The question is whether it is possible to set the control so that the selection has the same appearance regardless of focus (at the moment, on my system, the selection appears blue when the control has focus, and light grey otherwise - I'd like it to always appear blue). Cheers mark-r -- Fiddlers Dram lyric snippets for sale. All for under a pound. [ Lyric snippet contributions welcome ] >The question is whether i...

Find and Replace Text with CarriageReturn
I get a data extract that includes "\n" text. I would like to replace with CarriageReturn using a macro. I cannot seem to make the normal Find and Replace work for this. Rick one way: Cells.Replace _ What:="/n", _ Replacement:=Chr(10), _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False Regards Trevor "RickT" <anonymous@discussions.microsoft.com> wrote in message news:041b01c3d3e3$25637da0$a401280a@phx.gbl... > I get a data extract that includes "\n" text. I would > like to re...

Can't print more than one copy at a time.
I am unable to print more than one copy of a page from Publisher using an HP Photosmart All-in-one printer. I can get multiple copies of a page on my HP Laserjet. The Photosmart printer will produce multiple copies in copy mode, so I don't think it's the printer itself. Any thoughts? It is a printer problem, maybe an updated driver is available from HP. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jim" <Jim@discussions.microsoft.com> wrote in message news:1FD1F39B-6082-4C49-A5E5-5E1E810E9D52@micros...

Formula or Format Error??
Can anyone tell me please why this formula is returning a 4 figure % in my destination cell Q13 even though I have cleared the cell to "No Decimal Places": =IF(OR(V13="", X13=""),"",IF(V13="N","",IF(X13="R","",SUM(V13*0.15)+(X13*0.85)))) Cheers David If the result of this: SUM(V13*0.15)+(X13*0.85) Is an integer and the cell is formatted as PERCENTAGE then that will happen. Try this: =IF(OR(V13="",X13="",V13="N",X13="R"),"",SUM(V13*0.15,X13*0.85)/100) Biff ...

Find directory dialog
Can I restrict CFileDialog to returning only directory names? If not what should I use to get and return a directory name? Similarly for non-dialog applications can FileFind be restricted to directory names or only file names - again if not what to use? Thanks -- Norman Bearon http://www.codeproject.com/dialog/XBrowseForFolder.asp There are a couple of extra "new" options as well. I usually add: #ifndef BIF_NEWDIALOGSTYLE #define BIF_NEWDIALOGSTYLE 0x0040 #endif And if you use this style you'll get the Create Folder button and the dialog will be resizable. Tom "...

2nd request Help with Formula
Good afternoon: This formula works when an address is entered. I need the option to enter an address or a name and have it populate the same. I sent an earlier posting, with an attachment, Thanks John [Address] =IF($D3=400,"",INDEX('Legal (2)'!$A1:$A400,$D3,1)) [Owner] =IF($D3=400,"",INDEX('Legal (2)'!$B$1:$B$400,$D3,1)) [Col-D] {=IF(LEN(TRIM('Data Sheet'!$D$20))>0,SMALL(IF('Legal (2)'!$A$1:$A$400='Data Sheet'!$D$20,ROW($B1:$B400),400),ROW('Data Sheet'!A1)),400)} We've seen your first post. ...

What formula will take a name in one cell (last name,first name) .
I have a name in a cell, last name first then a comma then first name, and I want to seperate them into two cells. I know there is a formula to do that, but I don't know what it is, can anyone help me? Use the menu option Data:Test To Columns to parse the one field into two fields Good Luck Stewart "jobby" wrote: > I have a name in a cell, last name first then a comma then first name, and I > want to seperate them into two cells. I know there is a formula to do that, > but I don't know what it is, can anyone help me? You could use Data > Text to colum...

Formula for returning values if true or false
I have a spreadsheet which has three columns headed code 1 code 2 code 3. If a cell in a code 1 has an X I want that column heading returned, if its not found in that column then go to next column find the X and return that column heading instead eg code 2, and so on Does anyone know how this may be done ? This formula checks only the first 100 rows, but can be adapted to d the entire column by changing the ranges: =IF(ISERROR(MATCH("X",A2:A100)), IF(ISERROR(MATCH("X",B2:B100)) IF(ISERROR(MATCH("X",C2:C100)), "NONE", C1), B1), A1) Even better, I tes...

Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet?
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet at the date you specified? thanks, Charlie Hi Charlie Delete the file is I think not a good idea? Maybe the user have some information in it??? But it is possible, post back if you want to know how to do it There is always a way to use your workbook if anybody wants it. VBA is not save. Put this in the workbook open event of the workbook. After the date the file will be closed when you open it. Private Sub Workbook_Open() If Date > DateSerial(2005, 4, 1) T...

Moving a sheet from one Excel file to another?
I have two Excel files containing several sheets each. How do I move a sheet from one file (workbook) to another? I tried the move or copy function in the file I want to move the sheet from, however, the file I want to move the sheet to does not appear in the "To book" drop down list. You need to open both files. -- Greetings from New Zealand Bill K "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:AF5B4D23-CBB8-4D7D-8C3D-FA2C0DBD7A67@microsoft.com... >I have two Excel files containing several sheets each. How do I move a >sheet > f...

Copy of email is being sent to one local account automatically!
Dear, I am not expert of exchange server, but now a days I am having problem and needed help to resolve the issue. Our Exchange was setup by 3rd party, now I noticed that whenever I sent an email the copy of my email is being sent to an email account (which is local account in our domain) to make the picture more clearer here are the details; 1. Any email sent by me is being sent to account ABC.Ourdomain.local 2. That “ABC” user account is existing in our Active Directory. my question is how can I disable that option so that my emails are not being sent to that address? in the ...

formula in pivot excel 2007
It used to be possible to enter a formula within a pivot table so it would update if the pivot table was changed. E.g. one column divided by another and the formula would be avaiable even for extra lines if another variable was used. Is this still avaiable in new excel, and if so, where? In EXCEL 2007 take the following action:- PivotTable Tools Options Tools group Formulas Calculated Field The Insert Calculated Field pane should launch. In here you are able to set up to divide one column by another. If my comments have helped please hit Yes. Thanks....

In Excel 2000, can I change the direction of the move selection a.
In Excel 2000, can I change the direction of the move selection after Tab? (I know you can change the dirrection after Enter) GTP Not possible I'm afraid -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "GTP" <GTP@discussions.microsoft.com> wrote in message news:7C5F0598-AA83-4554-8999-3AA123784891@microsoft.com... > In Excel 2000, can I change the direction of the move selection after Tab? > (I > know you can change the dirrection after Enter) If you have a specific pattern to follow... Snipped from...

Looking for a specific Excel function/formula
Hello-I am looking for the function/formula that will give me th following data: Cell D4 has a numerical figure-it is a percent I want cell D6 to show the following: If Cell D4 is 30% or less, then D6 shows 0 If Cell D4 is >=31% but <=35%, D6 must show 15% (or .15) If Cell D4 is >=36% but <=41%, D6 must show 25% (or.25) If Cell D4 is =>42%, D6 shows 3% (or .30) Thank You Pete -- Message posted from http://www.ExcelForum.com Peter =IF(D4<=30%,0,IF(D4<=35%,15%,IF(D4<=41%,25%,30%))) Regards Trevor "petern >" <<petern.15hpf1@excelforum-nospam.co...

Finding last non-blank cell in a column
I am trying to create a cell at the top of a worksheet that will give me the last non-blank cell in a column. The column is a list of dates. Any help is much appreciated. Bob Weeden =INDEX(A:A,MAX(IF(ISNUMBER(A1:A65535),ROW(A1:A65535)))) as an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Microsoft.news.com" <rweeden@wesd.k12.az.us> wrote in message news:%23mwRL9O$FHA.1172@TK2MSFTNGP10.phx.gbl... > I am trying to create a cell at the top of a worksheet that will give me the > last non...

Generate a graphical formula from a column of data
If I have a set of data, is there any way I can generate a formula which roughly matches the plot of the data set? I know I can generate a trend line on a chart but is there a way of finding out the formula for this trendline, so that I could reproduce it without the origiinal data perhaps? Hope I'm making myself clear. Many thanks in advance. Insure that the Analysis ToolPak has been loaded. If your data is linear, then use LINEST() to derive the formula -- Gary's Student "rmellison" wrote: > If I have a set of data, is there any way I can generate a formula wh...

Create Multi-Select List box
How on earth do I create a multi-select list box? I created a form, frmEditStudent. Bound to a table, tblStudent. the form is to record modifications that a student should receive according to several meetings that parents/administrators have. The fields MathMods, ElaMods, SciMods, and SsMods will have multiple selections and their field type is number. I created a listbox using the toolbox with a record source from tblMods and bound it to MathMods. I set the multi-select to extended. When I open the form and attempt to select multiple choices, no banana. Only 1 is highlighted an...