text in formula

Help!!!
Is there a way to have a formula where a cell with text is 
in it, but it is not included in the formula.  Like I have 
a random cell that appears sometimes within the range but 
because it is text and I do not want to include it in the 
formula.  Is there a certain "symbol" that could be used?  
HELP!! 
0
anonymous (74722)
1/16/2004 3:05:50 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
373 Views

Similar Articles

[PageSpeed] 55

Hi maureen,

It depends on what the actual formula is, but *some* 
functions ignore text entries. You could use the ISNUMBER 
function to include only those entries that are numbers. 
Post your formula for a more detailed reply.

Biff

>-----Original Message-----
>Help!!!
>Is there a way to have a formula where a cell with text 
is 
>in it, but it is not included in the formula.  Like I 
have 
>a random cell that appears sometimes within the range but 
>because it is text and I do not want to include it in the 
>formula.  Is there a certain "symbol" that could be 
used?  
>HELP!! 
>.
>
0
anonymous (74722)
1/16/2004 3:25:50 AM
Ok, say the original formula was =sum(B24:B32) but then 
someone uses cell B28 for text, like "missing" for 
example.  It is a fairly large spreadsheet, so I wouldn't 
want to re-invent the wheel, so how could I set it up to 
ignore text if it is entered?  Thanks for your help, and 
can you show me the formula.  I am VERY NEW IN EXCEL.
>-----Original Message-----
>Hi maureen,
>
>It depends on what the actual formula is, but *some* 
>functions ignore text entries. You could use the ISNUMBER 
>function to include only those entries that are numbers. 
>Post your formula for a more detailed reply.
>
>Biff
>
>>-----Original Message-----
>>Help!!!
>>Is there a way to have a formula where a cell with text 
>is 
>>in it, but it is not included in the formula.  Like I 
>have 
>>a random cell that appears sometimes within the range 
but 
>>because it is text and I do not want to include it in 
the 
>>formula.  Is there a certain "symbol" that could be 
>used?  
>>HELP!! 
>>.
>>
>.
>
0
anonymous (74722)
1/16/2004 3:39:10 AM
Sum ignores text entries within it's range.

=SUM(B24:B32) will still return a valid total with "missing" being entered
in B28.

Have you tried it ?
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Maureen" <anonymous@discussions.microsoft.com> wrote in message
news:04e001c3dbe2$4d7d3950$a601280a@phx.gbl...
Ok, say the original formula was =sum(B24:B32) but then
someone uses cell B28 for text, like "missing" for
example.  It is a fairly large spreadsheet, so I wouldn't
want to re-invent the wheel, so how could I set it up to
ignore text if it is entered?  Thanks for your help, and
can you show me the formula.  I am VERY NEW IN EXCEL.
>-----Original Message-----
>Hi maureen,
>
>It depends on what the actual formula is, but *some*
>functions ignore text entries. You could use the ISNUMBER
>function to include only those entries that are numbers.
>Post your formula for a more detailed reply.
>
>Biff
>
>>-----Original Message-----
>>Help!!!
>>Is there a way to have a formula where a cell with text
>is
>>in it, but it is not included in the formula.  Like I
>have
>>a random cell that appears sometimes within the range
but
>>because it is text and I do not want to include it in
the
>>formula.  Is there a certain "symbol" that could be
>used?
>>HELP!!
>>.
>>
>.
>


0
ragdyer1 (4060)
1/16/2004 3:58:03 AM
Ok, it must have been a more involved formula.  Please, is 
there a way to isolate a cell from a formula if it has 
text rather than a number value?
>-----Original Message-----
>Sum ignores text entries within it's range.
>
>=SUM(B24:B32) will still return a valid total 
with "missing" being entered
>in B28.
>
>Have you tried it ?
>-- 
>
>HTH,
>
>RD
>==============================================
>Please keep all correspondence within the Group, so all 
may benefit!
>==============================================
>
>"Maureen" <anonymous@discussions.microsoft.com> wrote in 
message
>news:04e001c3dbe2$4d7d3950$a601280a@phx.gbl...
>Ok, say the original formula was =sum(B24:B32) but then
>someone uses cell B28 for text, like "missing" for
>example.  It is a fairly large spreadsheet, so I wouldn't
>want to re-invent the wheel, so how could I set it up to
>ignore text if it is entered?  Thanks for your help, and
>can you show me the formula.  I am VERY NEW IN EXCEL.
>>-----Original Message-----
>>Hi maureen,
>>
>>It depends on what the actual formula is, but *some*
>>functions ignore text entries. You could use the ISNUMBER
>>function to include only those entries that are numbers.
>>Post your formula for a more detailed reply.
>>
>>Biff
>>
>>>-----Original Message-----
>>>Help!!!
>>>Is there a way to have a formula where a cell with text
>>is
>>>in it, but it is not included in the formula.  Like I
>>have
>>>a random cell that appears sometimes within the range
>but
>>>because it is text and I do not want to include it in
>the
>>>formula.  Is there a certain "symbol" that could be
>>used?
>>>HELP!!
>>>.
>>>
>>.
>>
>
>
>.
>
0
anonymous (74722)
1/16/2004 4:46:44 AM
There are different procedures for different types of functions.

Post back with a specific formula.
-- 


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"maureen" <anonymous@discussions.microsoft.com> wrote in message
news:054001c3dbeb$be202100$a601280a@phx.gbl...
Ok, it must have been a more involved formula.  Please, is
there a way to isolate a cell from a formula if it has
text rather than a number value?
>-----Original Message-----
>Sum ignores text entries within it's range.
>
>=SUM(B24:B32) will still return a valid total
with "missing" being entered
>in B28.
>
>Have you tried it ?
>-- 
>
>HTH,
>
>RD
>==============================================
>Please keep all correspondence within the Group, so all
may benefit!
>==============================================
>
>"Maureen" <anonymous@discussions.microsoft.com> wrote in
message
>news:04e001c3dbe2$4d7d3950$a601280a@phx.gbl...
>Ok, say the original formula was =sum(B24:B32) but then
>someone uses cell B28 for text, like "missing" for
>example.  It is a fairly large spreadsheet, so I wouldn't
>want to re-invent the wheel, so how could I set it up to
>ignore text if it is entered?  Thanks for your help, and
>can you show me the formula.  I am VERY NEW IN EXCEL.
>>-----Original Message-----
>>Hi maureen,
>>
>>It depends on what the actual formula is, but *some*
>>functions ignore text entries. You could use the ISNUMBER
>>function to include only those entries that are numbers.
>>Post your formula for a more detailed reply.
>>
>>Biff
>>
>>>-----Original Message-----
>>>Help!!!
>>>Is there a way to have a formula where a cell with text
>>is
>>>in it, but it is not included in the formula.  Like I
>>have
>>>a random cell that appears sometimes within the range
>but
>>>because it is text and I do not want to include it in
>the
>>>formula.  Is there a certain "symbol" that could be
>>used?
>>>HELP!!
>>>.
>>>
>>.
>>
>
>
>.
>


0
ragdyer1 (4060)
1/16/2004 8:57:12 PM
Reply:

Similar Artilces:

Formula para sacar promedio pondeera
Hola Amigos: Tengo un problema al querer sacar promedio ponterado con numero de cursos distintos y para lo cual te adjunto el modelo. Pero quiero con formulas REDONDEDAR - SUMA - DIVIDIR - CONTAR U OTRAS MAS FORMULAS CURSOS JUNIO LENGUAJE Y LITERATURA 13 RAZ. VERBAL IDIOMA 12 GEOGRAFIA DEL PER� Y DEL MUNDO 12 EDUCACI�N RELIGIOSA 13 EDUCACI�N CIVICA 12 ECONOMIA FILOSOF�A Y LOGICA 12 MATEMATICA 13 RAZ. MATEMATICA 13 EDUCACI�N ARTISTICA 10 EDUCACI�N F�SICA 12 HISTORIA DEL PERU - PAM 12 F...

autocalculate doesn't work even with formula .
I have been using the same worksheet for past 1 year. Last week, when I edit the worksheet by input new data into it, I realise that : 1. The "Total" does not add up the sums of 2 cells automatically, even though the "A1 + B1" formulation is still in the cell. 2. I have auto-link sheet 1 cells into sheet 2 in different layout to suit my final output design. But even I paste the cells in the designated area in sheet 1, my final output does not automatically show the new data I have input into sheet 1. Only way to "show in the final output" correctl...

Find feature does not work with HTML messages (Content-type: text/html)
I have Outlook 2000 SP-3 running under Windows ME. When using the Find feature to locate all e-mails with a given word in the message, no items are found if the e-mails are of "text/html" content-type. The Find feature appears to work for any messages that have URLs embedded and are "multipart/alternative" or "text/plain" content-type. I noticed a similar post several months ago (see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%23qSBYIssDHA.2224%40TK2MSFTNGP09.phx.gbl). Is there a work-around for this problem, or should this be reported t...

Excel should default to autoformat for chart text boxes
Excel in (Office 2003 and as far as I can tell all previous versions of Excel) should default to autoformat for chart text boxes - it currently selects "none" for outline and background options ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

Formula
For each cell in column B I want to test if the adjacent cell in column A is > 10 and if it is set the B cell the same as value in the A cell (this bit is easy) and also set a cell in column C with the value "100". However the reference of the column C cell is dependent upon the value (between 1 - 4) in the adjacent cell in column D. For example (I know this isn't possible but it may help to explain the idea), applying the formula in B1: if(A1>10,"20", C(D1)="100"). If A1 was 8 and D1 = 4 then C4 is set to 100 and if D1 was 2 then C2...

Formulas showing in cell???
I keep getting a formula showing in the cell after I edit i (occasionally). Show formulas is turned off and edit in cell is on. How can I make the formula go awaw and simply show the results whic when edited the results shows correctly? -- Message posted from http://www.ExcelForum.com Hi probably the cell is formated as 'Text' change the cell format to 'General' and re-enter your formula -- Regards Frank Kabel Frankfurt, Germany > I keep getting a formula showing in the cell after I edit it > (occasionally). Show formulas is turned off and edit in cell is on. >...

How do I set up formulas to update graphs dynamically
Hello, I have the following information I need to graph date Feb-08 Mar-08 Apr-08 May-08 Jun-08 Jul-08 burn 116 23 254 inventory 3035 3011 2757 The "date" is the x-axis label and the "burn" and "inventory" are the values on the y-axis. If the values for burn and inventory are blank for a particular date, I don't want the information plotted. As I add values for each date, I would like the graph to be updated automatically. I understand I need to define a named range and use it in the graph, however, I have not been successful in doing this. Also, even t...

Need help extracting text from EDLs
I really am trying to learn this stuff, but I haven't programmed anything since my TRS-80 Basic days and I'm a bit overwhelmed. I want to extract text from Edit Decision Lists so that I have a list of shots used in a film. Here is an example of one event of an EDL: 002 TAPE004 V C 04:45:22:06 04:45:24:14 02:00:08:00 02:00:10:08 FROM CLIP NAME: 7C-4_B_ DLEDL: PATH: /raids/luc_1/bun/reel_02/oscans/1222/bun_7c_4_b_02/2048x1556/ The text that follows "FROM CLIP NAME:" is the name of the clip that I want to extract so that I have a document on onl...

Excell Formula
I have a list of over 1000 Product in column A and in column B over 30 Supplies and in column C the price of each product from different Supplies. Each Supplier may offer same product but with different prices ( e.g. Table below ). All the information is in sheet 1. A B C 1 Product 1 Supplier 2 £10.00 2 Product 2 Supplier 1 £8.00 3 Product 3 Supplier 2 £8.00 4 Product 2 Supplier 2 £6.00 5 Product 1 Supplier 3 £11.00 6 Product 3 Supplier 1 £7.00 I have created sheet 2 ( e.g. table below ) I need formula which updates u...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Wildcards in Excel Formulas
I am working on an Excel spreadsheet and I am tyring to write a formula to read the 4th character of previously entered data For instance, codes entered in column A could be eadSvf1 and eadNvf1. I need a formula to read that 4th character and put a 1 or 2 in column B depending on if the 4th character is an S or an N Hope that made sense and thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ got it, thanks anyway, I ended up using the MID function ...

Code to send SMS text message?
Hi, I would like to send an SMS text message to a cel phone via code within Excel 2007. Has anyone done this? What code / services / products do I need to accomplish this? Does anyone have an example? Thanks in advance. --Dan What gateway are you using? What you would need to do is to snd a internet message to a gateway provider which would then send the messae out on the wireless land. Most likely you are using a webpage interface from your PC to interface with the gateway. So opening up an internet explorer and goiong to an URL is probably the method to use. ...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

Formula automation
Hi all, I want to create a formula automation so when a user enter a data through the form that I've created, the data will go straight to my Excel database and Excel should automatically calculate them using the formula from the previous row. I have tried to go to "Tools-options-edit-extend formula" and ticked the box but it won't do anything. Does anyone has any idea on how to do this automation thing? Thanks a lot! Rgds, Marcella ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software!http://www.ozgrid.com/Services/excel-software-categori...

Access 2007 Rich Text Question 04-28-07
Hi All In the plan text version of a Memo field you can easily add a date by pressing Ctrl+Colon which makes life easy for the end user. However, if the Memo field is set to the Rich Text Property this no longer works. Or am I missing something? Tom gave me a part answer last week but I cannot see a way of inserting a date on a Rich Text Memo field by using keystrokes Thanks ...

how do i copy a formula when cell references r not together
in cell reference H5 i have a formula H4*H3, I have copied this formula through to DG5. In cell reference H7 I have a formula H6*H3 which i have also copied through to DG7.I have formulas right down to cell reference H299 (H298*H3) Is there a quick way to repeat the copy bearing in mind the cell references are not together ie H5, H7, H9 and so on. Jon, Copy cells H6:H7, then select H6:H299 and pastespecial formulas. Just make sure your formula is =H6*$H$3 HTH, Bernie MS Excel MVP "jon104" <jon104@discussions.microsoft.com> wrote in message news:DDAB488A-5CDA-47A2-AD...

Text box and formatting?
I am working on class schedule and have one text box which spans a few pages. Pages are double columns. I have a header for each class and paragraph given for class description. On the last page, last column, one description appears at the top with empty space for the rest of the column. I would like to change the formatting of the text in the whole text-box so that most of the last column is used. I can select the text in the text box which expands over the few pages and play with the font size, but that would mean that all the text would be the same size. Is there a way that text...

Overwriting a cell with a formula without deleting the formula
Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense...

How do I use text in a cell as a range name in a formula
If cell A1 had the text TEST in it and TEST is the name I have given to a group of cells using the name box what formula can I use to give me the sum of TEST, thats is the sum of the cells in the group called TEST. I understand that I can simply have =SUM(TEST), but I want the formula to refer to Cell A1 to get the name ie =SUM(A1) doesn't work obviously Any help appreciated Thank you In this case, you want to use the INDIRECT function. E.g., =SUM(INDIRECT(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kiwi" &...

Time formula
I'm trying to find a fomula that inserts the time on column b when I enter a text on column a.... ...

How to create a formula?
What do I do to have a result appear automatically in F68 which is the result of F67+C68+E68-D68. Currently I Copy and Paste on F67 & F68. In the cell type =F67+C68+E68-D68 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Zygy" <zygy@bt.connect.com> wrote in message news:eDXC$INMHHA.5064@TK2MSFTNGP04.phx.gbl... > What do I do to have a result appear automatically in F68 which is the > result of F67+C68+E68-D68. Currently I Copy and Paste on F67 & F68. > As I said I copy and paste the formula already, but how do I...

Formula "Change" Problem
Situation: In cell A1, I have a simple formula that sums the data in the following 6 horizontal cells. The formula is =SUM(B1:G1) I want this formula to never change under any circumstance. The problem is if I insert a column before in front of column B the formula changes to the following: =SUM(C1:H1) The spreadsheet is setup so that it is supposed to calculate a rolling 6 week total. Each new week a new column will be inserted and the data for the new week entered. How can i keep the formula from changing as the worksheet changes? Thank you in adnvance to anyone that may be able to h...

Formula or not?
Hi, How can one determine if a cells contais a value or a formula? I'm trying to use Conditional Formatting to color code the cells based on their content (formula or value). Does anyone know? Thanks -- J. Avelar Here's a nifty macro that some kind folks in the group gave me some time ago........it works super. (watch out for email word-wrap) Vaya con Dios, Chuck, CABGx3 Public Sub IDFormulae() Dim response As Variant response = Application.InputBox("Identify Cells containing formulas with:" & _ vbNewLine & "1 - Red Border" & vbTab &am...

Month Formula #2
Is there a formula that would read the previous cell and insert the following month? Ex. Prev Cell is "June" the next cell would read "July"? Hi Todd, That really depends if what you have in the cell is a date or text. When you select a cell in question, what does it say in the Formula Bar? Is it "June"? Or is a number/date *formatted* to show June (mmmm)? If it's a date, check out the EOMONTH function from the Analysis Toolpak. HTH -- Regards, Zack Barresse, aka firefytr "Todd Nelson" <ToddNelson@discussions.microsoft.com> wrote...