replace a negative result with a zero?

I have my formula entered but need it to return a zero if the result is 
negative.  Any help is greatly appreciated.
-- 
Pam 
0
Utf
4/19/2010 4:17:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
2300 Views

Similar Articles

[PageSpeed] 37

Well, what is the formula that you are using?

You could try this:

=3DMAX(your_formula,0)

to replace your_formula.

Hope this helps.

Pete

On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote:
> I have my formula entered but need it to return a zero if the result is
> negative. =A0Any help is greatly appreciated.
> --
> Pam

0
Pete_UK
4/19/2010 4:22:27 PM
Hi,

=if(your formula<0,0,your formula)

"Pam2277" wrote:

> I have my formula entered but need it to return a zero if the result is 
> negative.  Any help is greatly appreciated.
> -- 
> Pam 
0
Utf
4/19/2010 4:33:02 PM
Reply:

Similar Artilces:

Syncronise zero values
I have seven columns of data to chart. Columns A & B have values aroun 500,000 to 600,000 and I wish to show them against the right hand axe as a line chart. The remaining columns have values from -50,000 t +200,000 and I wish to show them against the left hand axes as colum chart. Is it possible to get their zero values on the same level? Is this making sense to anyone? Thanks for any hel -- Brisbane Ro ----------------------------------------------------------------------- Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2509 View this thread...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Hyperlink function presents variable results does not work in macro
I need some assistance if possible using a hyperlink function within a macro The hyperlink function is presenting the current address in excel (address varies) but there is no change within the macro code It appears that the macro once recorded cannot be "updated" to the address that is current. Is there a way around, through or over this. I am using the hyperlink to select a range which can then be "updated" as part of the macro Thanks for any assistance -- Message posted via http://www.officekb.com As ALWAYS, post your coding efforts for comments. --...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Negative Data Labels in a Pie Chart
Has anyone encountered a problem in Microsoft Excel 2003 with pie charts where negative values data labels show up with reverse brackets (i.e. )5( )? I sent this spreadsheet that I am working on to my home computer and when I opened it up in Microsoft Excel 2002 the negative number label appeared correctly (i.e. (5) ). I know the ultimate idea of a pie chart is not to have a negative number, but on my pie chart I am showing total earnings for different areas. I am open to other chart representations to show total earnings for multiple (10 areas). Thanks. I've heard of this...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

How do I check error result occurring by filecopy function in VBA
I writing in VBA for excell. My program calls the filecopy function in a private function. If filecopy fails, the program should analyze it and return with a failure value to the main subrotine that calld the fuction (in a success execution of filecopy, the function returns the workbook object of the new destination file). How can I check the status of the execution? How can I found out if the returnd workbook object has value or it's empty? Thanks Hi I tend to do something like Err.clear 'err is not destroyed when the sub ends On Error Resume Next 'do the co...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Coding for future month results
Below is my current code for October's reporting. It is a project form showing planned progress (OctPlan), actual progress (OctAct) and via the attached code, an indicator and color for October (OctInd). This code works great (thanks to the help on here) However, my form shows all 12 months, so the code currently causes future months to indicate red. Is there a an If then type statement that I can place this current code into that will basically say If OctAct is null then OctInd = N/A? or is null ruined based on the the code? Is there some other procedure I can try? I've ...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

Leading zero with fractions
I have one column which is formatted to show inches and fractions of an inch. 1/16, 1/8, 3/16, 1/4 and so on up to 15/16. It works perfectly except that I want it to show a leading zero when the fraction is smaller than one inch. eg 0 15/16, not 15/16. Is there any way to format the cell to do that?. -- Mike A custom format like: 0 ??/?? Mike wrote: > > I have one column which is formatted to show inches and fractions of an inch. > 1/16, 1/8, 3/16, 1/4 and so on up to 15/16. It works perfectly except that I > want it to show a leading zero when the fraction is smaller tha...

How do I replace just a few characters?
Hello, I have an excel worksheet with a street name column. I need to rename some names from "Main Street" to "Main St". I can do it for one name easily with find and replace, but how do I make it work for an entire column of different names such as "Easy Street" and "Main Street". If I do find "* Street" and replace with "* ST" I get "* ST" for the output instead of "Easy ST" What am I missing? -- Tomsriv ------------------------------------------------------------------------ Tomsriv's Profile: http:/...

VBA to find filename and replace
Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW mont...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

Less of Two...but not ZERO
I'm looking to take the lesser of 2 data values (in different colums) but only when the data is greater than 100. Here's an example: Col A Col B Desired result 664 0 664 803 742 742 665 33 665 0 745 745 42 84 42 I'm familar with using an Iff statement to return the lesser of the two values but I am running into problems because I need it to return the lesser of two values ONLY WHEN either column A or B is greater than 100. If neither column A or B is greater than 100, column 1 is the desired result. Note: I'm not to familar wi...

Return single row, which ahs multiple results
That subject isn't clear. I have been asked to write a SP that displays summary information in one row. A person may have 0 to many court appearances. The person may have 1 to many charges relating to a court appearance. So, table structure would be something like: CREATE TABLE dbo.[Person] ( PersonId INT NOT NULL IDENTITY(1,1), // Primary Key Surname NVARCHAR(50) NOT NULL ) CREATE TABLE dbo.[CourtAppearance] ( CourtAppearanceId INT NOT NULL IDENTITY(1,1), // Primary Key PersonId INT NOT NULL, // Foreign key to person AppearanceDate DATETIME NOT NULL, CourtId INT NOT N...

Replacing A Column Of Numbers
Hello, New user of Excel 2007, and Excel in general. Should be simple, but I just can't figure out how. Have a column of numbers in Column A In Column B (row 1)I have the formula of what's in A (row 1) multiplied by 1000 And I have dragged this formula downwards, so I now have in each row B what is in the same row in Column A multiplied by 1000. So far, all is fine. Now, I want to replace what's in Column A by the newly generated values in Column B (row by row replacement) I can't just Cut out everything in Column A, as B then disappears, as, I'v...

Pivot Table Divide By Zero
I am trying to make a sales report showing customer name, billed revenue, cost, profit and percentage of profit (or loss) on each of the jobs my company handled for the year 2004. I then want to make a pivot table, showing the average profit we made on a per-customer basis. Sadly, in some cases our costs exceeded the revenue billed, and we consequently lost money on them. Life is tough, but that is not my current problem. The problem is those jobs where we were unable to bill the customer any money at all (due to a failure on our part), yet we still incurred costs that have to be paid. Those...

Find / Replace
In a field, I would like to update all instances of "Daily Billing" to "Rental Payment - Daily Billing". The Replace function works the first time, but when I add data to the file, and run the query again, it updates the fields which have already been upated to "Rental Payment - Rental Payment - Daily Billing". I can see why it is doing this, but is there a way for the update query to look at just the first 11 characters of the field and only perform the update if it finds the "Daily Billing" text there? Thanks. Use "Daily Billing" as ...

Replacing an OLE picture's picture property
I've been designing a catalog for my home movies. When the user uses a dropdown down menu, selects the title, it reads a synopsis and image name to display for that movie. Its an OLE picture object , but each time a new movie is selected, the code inserts another image (doesn't replace the existing image). My problem is that I can't figure out how to access the .picture property of the OLE object. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Availab...