Using Numeric Values from Formulas

Hello All
I have created a formula in one column that returns a value of 5 if a given 
line entry is more than 5 years old.

What I want to do is then say " if (and((d3>5, b3>0),"Yes","")
What this does is if the entry is over 5 years old and there is a value 
greater than 0 in cell b3, then give a value of "yes".

However since cell D3 contains a formula it does not work! If I were to 
manually type in the values in Column D then it works ok.

Is there a function in Excel that says "if the result of the formula >5" 
then ....??

thanks
Natalie
0
Tarry (1)
8/22/2005 9:30:43 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
510 Views

Similar Articles

[PageSpeed] 12

On Mon, 22 Aug 2005 02:30:43 -0700, "Natalie Tarry" <Natalie
Tarry@discussions.microsoft.com> wrote:

>Hello All
>I have created a formula in one column that returns a value of 5 if a given 
>line entry is more than 5 years old.
>
>What I want to do is then say " if (and((d3>5, b3>0),"Yes","")
>What this does is if the entry is over 5 years old and there is a value 
>greater than 0 in cell b3, then give a value of "yes".
>
>However since cell D3 contains a formula it does not work! If I were to 
>manually type in the values in Column D then it works ok.
>
>Is there a function in Excel that says "if the result of the formula >5" 
>then ....??
>
>thanks
>Natalie

Your formula SHOULD work.  The problem is likely that the result of the formula
in d3 is not exactly 5. (or that it is not a number).

What, exactly, is in D3?, B3?

What, exactly, do you see if you format D3, B3 as General?


--ron
0
ronrosenfeld (3122)
8/22/2005 10:09:19 AM
Natalie Tarry Wrote: 
> Hello All
> I have created a formula in one column that returns a value of 5 if a
> given
> line entry is more than 5 years old.
> 
> What I want to do is then say " if (and((d3>5, b3>0),"Yes","")
> What this does is if the entry is over 5 years old and there is a
> value
> greater than 0 in cell b3, then give a value of "yes".
> 
> However since cell D3 contains a formula it does not work! If I were
> to
> manually type in the values in Column D then it works ok.
> 
> Is there a function in Excel that says "if the result of the formula
> >5"
> then ....??
> 
> thanks
> Natalie

Natalie

Your formula has too many brackets (, remove one of the brackets after
and so it reads as follows:

=IF(AND(D3>5,B3>0),"Y","")


-- 
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=397694

0
8/22/2005 10:29:38 AM

"Paul Sheppard" wrote:

> 
> Natalie Tarry Wrote: 
> > Hello All
> > I have created a formula in one column that returns a value of 5 if a
> > given
> > line entry is more than 5 years old.
> > 
> > What I want to do is then say " if (and((d3>5, b3>0),"Yes","")
> > What this does is if the entry is over 5 years old and there is a
> > value
> > greater than 0 in cell b3, then give a value of "yes".
> > 
> > However since cell D3 contains a formula it does not work! If I were
> > to
> > manually type in the values in Column D then it works ok.
> > 
> > Is there a function in Excel that says "if the result of the formula
> > >5"
> > then ....??
> > 
> > thanks
> > Natalie
> 
> Natalie
> 
> Your formula has too many brackets (, remove one of the brackets after
> and so it reads as follows:
> 
> =IF(AND(D3>5,B3>0),"Y","")
> 
> 
> -- 
> Paul Sheppard
> 
> 
Hi Paul

Thanks for the information re the brackets, but unfortunately it still does 
not work.
If any of the cells in D3 are blank it still does not work. I need to add 
something into the formula that says 
=if(d3="","",if(and(d3>5,b3>0),"y","").

I have just created a little formula that says, in english, if D3>5, "Yes".
It gives a value of "Yes" to cells with "  " in it ( with a formula behind 
it).
If I delete the formula and delete everything from the cell, this solves the 
problem!

Still confused!
Natalie 



But it still doesn't solve my problem why
> 
0
8/22/2005 11:23:04 AM
Natalie Tarry Wrote:
> "Paul Sheppard" wrote:
> 
> >
> > Natalie Tarry Wrote:
> > > Hello All
> > > I have created a formula in one column that returns a value of 5 i
> a
> > > given
> > > line entry is more than 5 years old.
> > >
> > > What I want to do is then say " if (and((d3>5, b3>0),"Yes","")
> > > What this does is if the entry is over 5 years old and there is a
> > > value
> > > greater than 0 in cell b3, then give a value of "yes".
> > >
> > > However since cell D3 contains a formula it does not work! If 
> were
> > > to
> > > manually type in the values in Column D then it works ok.
> > >
> > > Is there a function in Excel that says "if the result of th
> formula
> > > >5"
> > > then ....??
> > >
> > > thanks
> > > Natalie
> >
> > Natalie
> >
> > Your formula has too many brackets (, remove one of the bracket
> after
> > and so it reads as follows:
> >
> > =IF(AND(D3>5,B3>0),"Y","")
> >
> >
> > --
> > Paul Sheppard
> >
> >
> Hi Paul
> 
> Thanks for the information re the brackets, but unfortunately it stil
> does
> not work.
> If any of the cells in D3 are blank it still does not work. I need t
> add
> something into the formula that says
> =if(d3="","",if(and(d3>5,b3>0),"y","").
> 
> I have just created a little formula that says, in english, if D3>5
> "Yes".
> It gives a value of "Yes" to cells with "  " in it ( with a formul
> behind
> it).
> If I delete the formula and delete everything from the cell, thi
> solves the
> problem!
> 
> Still confused!
> Natalie
> 
> 
> 
> But it still doesn't solve my problem why
> >

Natalie

Post some example data with cell references so I can duplicate what yo
have, eg what is the formula in D3? what is in the cells that drive th
result in D3, it seems to work ok for me

It could be that D3 contains 5 so you are expecting Yes, when th
result in D3 might only be 4.999999999999999, but formatted to show 
so you get blan

--
Paul Sheppar

-----------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2478
View this thread: http://www.excelforum.com/showthread.php?threadid=39769

0
8/22/2005 3:19:50 PM
Reply:

Similar Artilces:

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

SQL query / Formula for Reorder Point?
Is it possible to issue an SQL command(s) that would: 1. Set the Restock Level for all items to be equal to the current on-hand quantity for each item. 2. Set the Reorder point for all items to be equal to 1/3 of that items Restock Level. I'm currently doing this manually, or using a formula in excel when items are initially imported into the DB, but there are still several thousand that need to be retro-fitted like this... Thanx in advance! -=RFM=- UPDATE Item SET RestockLevel=(Quantity - QuantityCommitted) UPDATE Item SET ReorderPoint=ROUND(RestockLevel/3,0) - Evan Culver Ne...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...