Formulas or not ??

Good morning guys,

I woant a formula which when referring to another cell 
verifies whether this cell contains a numerical value or 
otherwise a normal numerical number

Is this possible ?

Thanks in dvance,
Brian
0
5/10/2004 7:14:38 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
593 Views

Similar Articles

[PageSpeed] 32

Hi Brian,

> I woant a formula which when referring to another cell 
> verifies whether this cell contains a numerical value or 
> otherwise a normal numerical number
>

=IF(ISNUMBER(A1),"A1 contains a number","A1 Does not contain a number")

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

0
jkpieterse (271)
5/10/2004 8:02:54 AM
Hi Brian!

I'm not sure whether I'm interpreting you correctly.

If a number is entered as text (eg by preceeding with an apostrophe) then

=ISNUMBER(A1)
will return FALSE

But:
=ISNUMBER(--A1)
will return TRUE

However, if A1 is an empty cell, then ISNUMBER(--A1) will return TRUE which
may not be what you want.

So for testing, you might use something like:

=IF(ISNUMBER(A1),"A number",IF(ISBLANK(A1),"Blank",IF(ISNUMBER(--A1),"Text
Number","Text")))

-- 
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"Brian Ferris" <brian.ferris@go.com.mt> wrote in message
news:a99d01c4365e$74c9e270$a601280a@phx.gbl...
> Good morning guys,
>
> I woant a formula which when referring to another cell
> verifies whether this cell contains a numerical value or
> otherwise a normal numerical number
>
> Is this possible ?
>
> Thanks in dvance,
> Brian


0
njharker (1646)
5/10/2004 9:18:43 AM
Hi Brian!

If you are wanting to test whether a cell contains a formula you can use the
User Defined Function:

Function ISFORMULA(MyCell As Range) As Boolean
    ISFORMULA = MyCell.HasFormula
End Function

-- 
-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

"Brian Ferris" <brian.ferris@go.com.mt> wrote in message
news:a99d01c4365e$74c9e270$a601280a@phx.gbl...
> Good morning guys,
>
> I woant a formula which when referring to another cell
> verifies whether this cell contains a numerical value or
> otherwise a normal numerical number
>
> Is this possible ?
>
> Thanks in dvance,
> Brian


0
njharker (1646)
5/10/2004 9:22:45 AM
But what i mean is ...

if in a particular cell, there is a valid number but which 
is a result of another formula, I want it to return false 
and not true... I want tru to show ONLY for manually input 
variables and not for formulas returning a number..

Hope I made myself clear

Thanks again,
Brian

>-----Original Message-----
>Hi Brian!
>
>I'm not sure whether I'm interpreting you correctly.
>
>If a number is entered as text (eg by preceeding with an 
apostrophe) then
>
>=ISNUMBER(A1)
>will return FALSE
>
>But:
>=ISNUMBER(--A1)
>will return TRUE
>
>However, if A1 is an empty cell, then ISNUMBER(--A1) will 
return TRUE which
>may not be what you want.
>
>So for testing, you might use something like:
>
>=IF(ISNUMBER(A1),"A number",IF(ISBLANK(A1),"Blank",IF
(ISNUMBER(--A1),"Text
>Number","Text")))
>
>-- 
>-- 
>Regards
>Norman Harker MVP (Excel)
>Sydney, Australia
>njharker@optusnet.com.au
>Excel and Word Function Lists (Classifications, Syntax 
and Arguments)
>available free to good homes.
>
>"Brian Ferris" <brian.ferris@go.com.mt> wrote in message
>news:a99d01c4365e$74c9e270$a601280a@phx.gbl...
>> Good morning guys,
>>
>> I woant a formula which when referring to another cell
>> verifies whether this cell contains a numerical value or
>> otherwise a normal numerical number
>>
>> Is this possible ?
>>
>> Thanks in dvance,
>> Brian
>
>
>.
>
0
5/10/2004 9:36:51 AM
Thanks for the suggestion...

Please guide me as to where I should place this 
function... I am green to Visual Basic 

Your help will be appreiated.

Thanks,
Brian

>-----Original Message-----
>Hi Brian!
>
>If you are wanting to test whether a cell contains a 
formula you can use the
>User Defined Function:
>
>Function ISFORMULA(MyCell As Range) As Boolean
>    ISFORMULA = MyCell.HasFormula
>End Function
>
>-- 
>-- 
>Regards
>Norman Harker MVP (Excel)
>Sydney, Australia
>njharker@optusnet.com.au
>Excel and Word Function Lists (Classifications, Syntax 
and Arguments)
>available free to good homes.
>
>"Brian Ferris" <brian.ferris@go.com.mt> wrote in message
>news:a99d01c4365e$74c9e270$a601280a@phx.gbl...
>> Good morning guys,
>>
>> I woant a formula which when referring to another cell
>> verifies whether this cell contains a numerical value or
>> otherwise a normal numerical number
>>
>> Is this possible ?
>>
>> Thanks in dvance,
>> Brian
>
>
>.
>
0
5/10/2004 11:08:06 AM
Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like:   VBAProject (yourfilename.xls)  

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.  

Now go back to excel and test it out.


Brioan Ferris wrote:
> 
> Thanks for the suggestion...
> 
> Please guide me as to where I should place this
> function... I am green to Visual Basic
> 
> Your help will be appreiated.
> 
> Thanks,
> Brian
> 
> >-----Original Message-----
> >Hi Brian!
> >
> >If you are wanting to test whether a cell contains a
> formula you can use the
> >User Defined Function:
> >
> >Function ISFORMULA(MyCell As Range) As Boolean
> >    ISFORMULA = MyCell.HasFormula
> >End Function
> >
> >--
> >--
> >Regards
> >Norman Harker MVP (Excel)
> >Sydney, Australia
> >njharker@optusnet.com.au
> >Excel and Word Function Lists (Classifications, Syntax
> and Arguments)
> >available free to good homes.
> >
> >"Brian Ferris" <brian.ferris@go.com.mt> wrote in message
> >news:a99d01c4365e$74c9e270$a601280a@phx.gbl...
> >> Good morning guys,
> >>
> >> I woant a formula which when referring to another cell
> >> verifies whether this cell contains a numerical value or
> >> otherwise a normal numerical number
> >>
> >> Is this possible ?
> >>
> >> Thanks in dvance,
> >> Brian
> >
> >
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/10/2004 11:28:09 PM
Reply:

Similar Artilces:

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

Formula answer red when should be black
I have a worksheet error testing formula that subtracts one number from another in a cell thats formated to show red when the answer is less than zero. If I have not made an entry error the two numbers are the same so the answer is zero and the zero showes up in red. If I reverse the formula the answer is still zero but shows up in black. My first thought was that sometimes in Excel during computations a number might be added to one of the numbers well to the right of the decimal. I checked for this, to 15 digits past the decimal and it's still all zeros. In another simular sprea...

possible countif formula?
Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in ...

Creating a Formula to Format Column automatically? #3
Can I record it as a macro or anything like that? I have to run it o five sheets. I've tried to conditional formatting, and it works only o one cell, unless I'm doing it wrong -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26434 not sure what you are trying to do but conditional formating can be coped and pasted. you have also conditional format an entire column/row by selected the en...

Formula for date field
1.I have simple dates in one column (say column A) . 2.In the next column(Column B) I would like the date five months after Column A to be displayed.Eg if Column A has an entry of 9th June 2007,Column B should display 8th November,2007. 3.A simple formula does not do the job as this does not take into account the different number of days in different months! regards S.Sanatani Your post is a bit ambiguous since you don't really say how the different number of days in months should be handled. One way: A1: <date> B1: =DATE(YEAR(A1),MONTH(A1)+5,DAY(A1)-1) In articl...

One More Formula Question
I need to get a specific total from the following criteria. I have data in 2 columns and need to get a total based upon specific entries in the cells in thoses columns. For example: Column A has 4 choices from a picklist. (N,P,C,R) Column B has a different set of choices. (ENG,OP,T&E,RS) What I need to do is be able to get an answer for how many N's are also ENG's Thanks -- doc ------------------------------------------------------------------------ doc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1434 View this thread: http://www.excelforu...

Date Time Stamp Formula
Hello, I am trying to create a formula that will insert the date and time into a new cell only when another cell is "0". In other words, if the specific cell hits 0, then the current date and time when the cell hits 0 is recorded in the other cell. If the cell is anything except 0, then the other cell is blank or says "not finished". I tried the if and now formulas to no avail and my macro is returning a VALUE error, so I'm completely lost. Any help would be much appreciated. Thanks, Katie Katie, It is a lot simpler with VBA This tests A1 and puts the date and t...

Array Formula Confusion
Does anyone understand this array function? {=TABLE(,H2)} Here's what I do know: 1) It doesn't seem to refer to any named ranges. 2) It doesn't seem to be a user-defined function. HELP! Thanks, Jim Look up "one variable data table" in help You can also look up "two variable data table", but yours is one. Regards Ken................. "Jim" <clovisjim@gmail.com> wrote in message news:d62b8b12-89a9-4cb9-acbc-b7f1e54244ed@15g2000yqi.googlegroups.com... > Does anyone understand this array f...

Help with a formula #4
This may seem academic, but I'm having difficulty finding the right formula to use for a task. In simple terms, I'm trying to cross-reference 2 columns (say A and B) to find instances where a value exists in column A but not B, and vice versa. For example, I have 2 columns of Vehicle Id numbers (VIN#), A and B, that should each have the same count. Assuming that these columns are duplicates, I'd like to find the exceptions where a VIN# exists in column A and not, and vice versa. Thanks for any help!!!! Hi in C1 enter =IF(A1<>B1,"no match","") and ...

I don't know which formula to use?
I need help understanding which formula I should use. . . I have built a table similar to the one below on a sheet of my workbook. On a separate sheet, I need to be able to input ONLY two variables (state and fruit) and return information (the cost) defined in the table below. As an example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to change Texas to Florida and the formula should immediately update to the value to $6.00. Another example: Inputting the variables of Texas and Bananas should return $4.00. I need to be able to ...

How do I add a date formula to a cell but hide the contents with .
I'm got a spreadsheet with many different sheets in it. The first sheet is the main page, and it holds a master table with data like customer name, salesman, registration number, date of registration etc. The other sheets are the individual salesman sheets, with just their relevant information which is copied from the main page. I'm experiementing with two ways to do this, VLOOKUP or just a formula like =sheet1!A12 etc. In the date column I want to put the date of registration, so I can copy that from the main page. However, sometimes a date is not always needed, so when I ...

Formula to produced a Cumulative Delivered/Ordered figure
I have a spreadsheet that I collate orders during the month from a customer, the spreadsheet contains the volume they order in one column and the volume actually delivered in another. I need to keep track during the month of how much they have ordered/received to ensure they remain within their product allocation for the month. eg: Site Ordered Delivered A 36,000 35,999 A 18,000 18,500 B 36,000 36,000 A 36,000 36,001 What I want to do is create a formula th...

MS Excel 2003 cannot auto calculate formula, need to press F9 each time
hi, I don't know why my excel 2003 new worksheet cannot auto calulate formula (eg. summation), i need to press F9 and it will refresh and show the new figure. there is "calculate" word at the left hand bottom of the screen. what is the likely reason ? it was running fine 2 weeks ago. any advise is greatly appreciated. rgds. Tools>Options>Calculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel <sg_s123@yahoo.com.sg> wrote in message news:d5393a73-eb7d-4e08-8fab-5f4ab895f77a@e23g2000prf.googlegroups.com... | hi, | | I don't know w...

sorting or grouping on formula based User-defined fields
Hi, I have a formula based user defined field containing the value: Str(Mid([Subject],2,6)) By default it's not possible to sort or group by formula UDF's. Sue Mosher wrote quite a while ago: "Quite right, and thanks for pointing that out. Turns out that I'd had formula and combination fields on the brain lately and hadn't been working with any other kind of UDF for a while. And, of course, there's a workaround. If you need to sort or group by a calculated field, put the formula in code behind the form rather than in the definition of the field." Does any...

conditional formulas #2
I've done all the searches online and off for help. Now time's running out for my project. Here's the situation... My data is in two columns with thousands of rows, columns are Parcel Size in Acres (number) and Use Code (numeric text). There are probably a couple hundred different use codes. I would rather group the more detailed use codes into broader categories, then get those total acreages. For example, there are several use codes that specify exactly what type of single-family house but all I really need to know is that it is a single-family house. Ultimately, I...

formula error #3
have any body tried mod(12,2.4) it should return zero but instead it returns a number any clue here http://www.cpearson.com/excel/rounding.htm -- Jim <alaomair@gmail.com> wrote in message news:7ffaf97a-ad59-4e89-a174-6ffb1e1b1daa@l62g2000hse.googlegroups.com... > have any body tried mod(12,2.4) it should return zero but instead it > returns a number > any clue here Hi, Try not to do multiple posts. -- Thanks, Shane Devenshire "alaomair@gmail.com" wrote: > have any body tried mod(12,2.4) it should return zero but instead it > returns a number > an...

Need help w very difficult formula!
Hello all! I need help with an "if then" formula. What I have is a list of about 8000 people in column a, their hours worked in column b, and a number of 8,9,10,11,12,13,20,21,22,23 in column c. I need several things to happen. If someone's scheduled hours worked are greater than or equal to 32, and they are 8,9,10 I need a result to say 150. If someone's scheduled hours are less than 32 and they are 8,9,10, I need the result to say 75. AND If their hours are greater than or equal to 32, and they are 12 or 13, the result = 250, if hours less than 32 and 12 or 13...

How does Money calculate formulas
Hi - is there a reference doc or an FAQ somewhere that details how MS Money calculates its various investment fomulas. I have investments that show different returns in Money vs what the various investment companies are reporting. I've looked around and haven't been able to find anything. Much obliged. Ian In microsoft.public.money, Ian wrote: >Hi - is there a reference doc or an FAQ somewhere that details how MS Money >calculates its various investment fomulas. I have investments that show >different returns in Money vs what the various investment companies are &g...

How could I forget this formula? Help please
Good afternoon, Alright, here's a simple one. How I have forgotten it (when I use it almost every day) is beyond me. All I know is that my mind is mush right now. I am preparing a blank work sheet for others to use, so here it is: I have multiple columns that all sum different cells. I need the "Answer" cell to not show a 0 if there is nothing to "sum". ex. Cell A1 is the sum total of A2-A10. If A2-A10 are empty, I do NOT want A1 to show 0. I have no idea how I am forgetting this today. I need a drink. Thanks for your help. Really. -- Scuba Wino -------...

Help Modifying this Formula ....
hi, I am trying to modify the formula below to use the LEFT function. any help Appreciated.. =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!H6:H35"),"=W")) I tried this below, but I get a reference error: =SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!(LEFT(H6:H35,1"),"=W")) Thanks In Advance. -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3598 View this thread: http://www...

formula help.. text
I am trying to get a series of cells to fill with a color if the word "insert" is inserted into the first cell. I.E. cells A2-A5 and cell A2 has "insert" typed in so up to A5 will change color. How do I go about doing this? Try Format | Conditional Formatting - after selecting cells A2:a5 Then use "Formula is" and type in =$A2 = "insert" Then click on the Format button and select the color you want from the Patterns tab "Murph" wrote: > I am trying to get a series of cells to fill with a color if the word > "insert&qu...

Can I use AutoFill or a formula to fill a series of letters?
I am looking to fill a series of increasing letters for example: aaa aab aac ... ... aaz aba abb Hi there, You can do this with some formulas. I'll assume you have this in A1 "aaa". Enter this formula in A2 and copy down as needed ... =IF(LEFT(A1,1)=122,CHAR(B1+1),CHAR(B1))&IF(MID(A1,2,1)=97,CHAR(C1+1),CHAR(C1))&IF(RIGHT(A1,1)=122,CHAR(97),CHAR(D1+1)) -- Regards, Zack Barresse, aka firefytr "tadpgk835" <tadpgk835@discussions.microsoft.com> wrote in message news:6EC42423-AA5B-40D0-ABB7-2A156AFEC187@microsoft.com... >I am looking to fill a ser...

Letters in IF formulas
If the value of a cell is a letter, can this be used in an IF formula? I am making a simple chore spreadsheet for my kids. In this sheet, each chore is represented by a letter. (i.e. B=clean bedroom) Also, each chore has a certain value amount with it (i.e. B=$1). I would like to be able to have the kids enter in the chore letter when it's completed and have the computer do all the calculations of amounts and totals. Can this be done? For example: =IF(c7=B,1,IF(c7=K,2)) Hi Stop re-posting, your messages 6 of them are posted plus you have an answer on your first post. Ignore...

Chart ignoring formulas but only plotting formula results
I am creating a line chart where the source data is the result of an =IF formula. How do I get the chart to only plot the cells where there is a result? Currently the chart is plotting a zero value for all the cells where the formula does not yet show a result. If( something, formula, NA() ) The otherwise blank cells now have #N/A in then (you can hide these with Conditional formatting if they bother you) Charts ignore these values best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "JD" <JD@discussions.microsoft.com> wrote in message new...

What's the correct formula?
Hi all, I'm looking for the correct formula for this expression. =COUNTIF((Your_Range,"hat and coat") or COUNTIF(Your_Range,"hat")) and (range,"New York") The first two criteria are possible selections and the third is a must in order to count the record as 1 My question is what would be the logical expression(formula) to show this. -- -- Cheers try =SUMPRODUCT((K7:K8={"hat","coat"})*1) -- Don Guillett SalesAid Software donaldb@281.com "zubee" <zubee@discussions.microsoft.com> wrote in message news:D711CA23-32A6-4C...