Divide two cells and if the number is negative display is as zero

Follow
Email
I have an excell spread sheet and i am dividing two cells. Most of the 
numbers are positve but some of the numbers are negative. I want to put a 
formula in to all the cells that will divide the two cells and give me the 
answer to one digit but if the number is negative i want it to display as 0.
0
Reply Utf 1/15/2010 3:07:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1798 Views

Similar Articles

[PageSpeed] 22
Maybe something like this...

=MAX(0,ROUND(A1/A2,1))

-- 
Biff
Microsoft Excel MVP


"mike" <mike@discussions.microsoft.com> wrote in message 
news:74196BD1-586C-47B9-BAFE-C0D895666B9A@microsoft.com...
>I have an excell spread sheet and i am dividing two cells. Most of the
> numbers are positve but some of the numbers are negative. I want to put a
> formula in to all the cells that will divide the two cells and give me the
> answer to one digit but if the number is negative i want it to display as 
> 0. 


0
Reply T 1/15/2010 3:13:58 AM
Hi Mike,

Following assumes that A1 is divided by B1

=IF(A1/B1<0,0,A1/B1)

However, if you have any zeros in column B then you will get #DIV/0! error 
so you might want to expand the formula and put "" in lieu of the error.
=IF(ISERROR(A1/B1),"",IF(A1/B1<0,0,A1/B1))

If you want zero instead of "" if dividing by zero then replace the "" with 
zero.
=IF(ISERROR(A1/B1),0,IF(A1/B1<0,0,A1/B1))


-- 
Regards,

OssieMac


"mike" wrote:

> I have an excell spread sheet and i am dividing two cells. Most of the 
> numbers are positve but some of the numbers are negative. I want to put a 
> formula in to all the cells that will divide the two cells and give me the 
> answer to one digit but if the number is negative i want it to display as 0.
0
Reply Utf 1/15/2010 3:33:01 AM
Follow
Email
Reply:
Similar Artilces:

Automatic divided by 100000
Hi all, On a new worksheet, when I type in a number in the cell, right after I hit "Enter", it automatic divide the number by 100,000, why this happened? Please help. Thanks. Gian Tools>options>edit and uncheck fixed decimal places Regards, Peo Sjoblom "Gian" wrote: > Hi all, > > On a new worksheet, when I type in a number in the cell, right after I hit > "Enter", it automatic divide the number by 100,000, why this happened? > Please help. > > Thanks. > > Gian Thanks. "Peo Sjoblom" wrote: > Tools>...

adding user control to a cell in a DataGridView
Instead of one of the existing column types I would like to have a user control appear in a cell in a gridview. What I have done is created a control CCTest : Control, IDataGridViewEditingControl inherits from Control, and implements IDataGridViewEditingControl I then create a cell class TestCell : DataGridViewCell in here I override public override void InitializeEditingControl(int rowIndex, object initialFormattedValue, DataGridViewCellStyle dataGridViewCellStyle) to set the editing control for the cell. these get used in a column I create and add to t...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

I want to print two copies at the same time of my report
I want to print two (2) copies at the same time of my modified report and it will have different print fields on the bottom of the report which are "Customer's Copy" and "Accountable". ---------------- 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....

How do I print odd numbered pages
I want to print double sided. The even numbers on one side and the odds on the other. Is there a way to do this in Excel Maryjanet, check your printer settings there may be an option there to do it, if not here is a macro from Gord Dibben that will do it Sub PrintDoubleSided() 'Will print odd or even pages 'By Gord Dibben Dim Totalpages As Long Dim pg As Long Dim oddoreven As Integer On Error GoTo enditt Totalpages = ExecuteExcel4Macro("Get.Document(50)") oddoreven = InputBox("Enter 1 for Odd, 2 for Even") For pg = oddoreven To Totalpages Step 2 ActiveWindow.S...

Need checkbox automatically in some cells of a new row
I am trying to create a template for a worksheet that contains columns that contain checkboxes. I need the checkboxes to appear in the cells in certain columns when a new row is added. How can I do it without having 5 million cehckboxes appearing and printing in rows that aren't being used? ...

How to I set the margins for a cell in Excel?
Though I have word wrap on, the words are running over into the next cell. This could have something to do with the fact that I have hard-returns in the cell. I wanted to ensure the margins weren't set to -.5 (or something like that) which would allow this kind of short run-over. Are you sure you've toggled wraptext? Format|cells|alignment tab I've never seen text bleed over to adjacent cells with this toggled correctly. blinko wrote: > > Though I have word wrap on, the words are running over into the next cell. > This could have something to do with the fact tha...

Pictures within a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have created a list of the items in a collection of mine. I have 'drag &amp; dropped' a picture of each of these items to a cell in a single column. <br><br>When viewing the picture properties, the option to &quot;move and size with cell&quot; is selected. However, if I select the sheet and sort by any column header, the pictures stay in their original position while the data in the surrounding columns re-orders according to the sort criteria. <br><br>I would appreciate any advice a...

Adding cells in one column based on a sequence in another
Hello, I have a column of 1000+ rows, each row containing a 1 or 0. I am trying to find a formula that locates the cell range of the largest sequence of ones and use that range to sum the same rows in another column. The largest sequence of 1's may occur a number of times. For example, column C may contain: ...011011110100111100... The largest sequence is four 1's but it occurs twice. I'd like to sum the corresponding rows (for 1111) in column G and present the largest value of the 2 occurances. I hope that makes sense. Thanks in advance. David This is very complicated. ...

cell ranges in formulas
When referencing a cell range in a formula, is there a way to represent the cell range by referencing another cell. What I want to do is this, for example: say cells a1 thru a100 contain a number of values. I want to sum a certain subset of these today, but tomorrow I want to change that subset, so I'd like to enter the starting and ending point for my sum range into another cell, say b1 & b2 So if I enter the text "a6" in cell b1, and "a25" in cell b2, my sum formula should look like =sum(a6:a25). This give me the flexibility to change my sumrange very e...

"link" two or more comboboxes
I'm a wee bit confused... I've read this below: The first thing that needs to be done is name the lists. Select th countries, without the title, and name it COUNTRIES. Next, select al cities (Again without titles) from USA and name it USA. Continue thi process for every country. Now, let's assume that the first list appears in A1. Go to Data Validation, Select List, and in "Source" put =COUNTRIES. Make sure that "In-cell dropdown" is checked. Now, if the second list appears in B1, go to Data, Validation, agai Select List, and in "Source" put =I...

Powerpoint doesnt display text
My powerpoint 2007 does not display text in the main screen.It is only displayed on the preview slides as i type. I have checked all the settings I could but cant find the setting that would cause this. So i reinstalled Office but the same thing happens. Anyone with an idea of how to solve this? Hi Have a look here: How to set graphics hardware acceleration back http://www.pptfaq.com/FAQ00129.htm This may also be worth a look: PowerPoint 2007 text editing slow, text cut off, text display or formatting problems, print, crash problems http://www.pptfaq.com/FAQ00850.h...

Entering + or
Hopefully there is a simple answer to this. I have cells formatted as general, though I have tried it text too, and would like to enter in a + or -. Everytime I do it, it assumes I'm entering in a function and changes it to +D2 (id I'm in the d2 cell.) How can I put in a + or - without this problem? Thanks for the help. Also, this in Excel 2000 and Excel XP. But, I think the - problem doesn't occur in XP. BR Bubba I suspect under tools>options>transition you have 'transition formula entry checked'. The default behaviour should be that a leading minus is sh...

deleting part of a cell
I am using the subtotal function in a worksheet. Is there a way to do this without MS Excel adding the word "TOTAL" at each break? Easiest way to get rid of the word total is go to the subtotal level (level 2) select all rows you want total deleted from, F5, Special, visible cells only, Then Find -- enter total Replace -- leave blank, replace all "CGodet" <anonymous@discussions.microsoft.com> wrote in message news:059167A4-ED8D-4A6D-A9C2-CBEDD5B3D465@microsoft.com... : I am using the subtotal function in a worksheet. Is there a way to do this without MS Excel a...

Adjusting a formula to collect data between two dates?
How can modify the formula below so that its criteria is between two dates? =IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value) Thank you. Something like: =IF(AF16="",NA(),SUMPRODUCT(--(AF16>=Date1),--(AF16<=Date2),Value) Regards, Fred "hoyos" <hoyos@discussions.microsoft.com> wrote in message news:55A330C3-5A1C-4871-8C75-A818F8AE8661@microsoft.com... > How can modify the formula below so that its criteria is between two > dates? > > =IF(AF16="",NA(),SUMPRODUCT(--(Date=AF16),Value) > > Thank you. ...

Actual Duration display
Hi, I'm updating tasks with actual hours spent each week on tasks. How do I change the display on the "Actual Dur" from days to hours? If a developer has worked 4 hour on a task that takes 5 days it displays ..5d under "Actual Dur". I would like to see it as 4 hrs? -jp Why don't you just display the Actual Work column? "jp" wrote: > > Hi, > > I'm updating tasks with actual hours spent each week on tasks. > How do I change the display on the "Actual Dur" from days to hours? > If a developer has w...

Select all entry data from two linked (with a relationship) Tables
Hi, I have two tables that are liked with a relationship: "Orders" and "Reports" with the OrderNumber column. Orders Table (PK -> OrderNumber): OrderNumber HoursForOrder 123456 47 876433 63 Reports Table (PK -> ID): ID OrderNumber HoursWorked 4 123456 5 5 123456 4 6 876433 17 Question: What would be the SQL SELECT command if I have the "ID" of a Report, and I would like to get the "HoursForOrder" at the same time with a single command? Since they are already linked I am wondering if should I really need to execute t...

Printing page numbers in sequence order...
Is there a way to format excel so that when you select the print entire workbook option and you have multiple sheets within your workbook, the pages will print out in sequence order? For example if I have 5 sheets within my workbook and sheet one has 3 pages, sheet two has 5 pages, sheet three has 2 pages, sheet four has 1 page and sheet 5 has 3 pages... is there a way to format excel so that when I select print entire workbook it automatically prints page numbers 1-3 for pages in sheet one, page numbers 4-8 for pages in sheet two, page numbers 9-10 for pages in sheet three, page number 11 f...

PDK in two companies
We are setting up a second company within PDK. Can I be an administrator/delegate for other time sheets if I am not an employee of this company? We are on GP 10 Can anyone please reply with an answer to this question? My payroll person posted this and we need to get this answered ASAP. "ASMAIL" wrote: > We are setting up a second company within PDK. Can I be an > administrator/delegate for other time sheets if I am not an employee of this > company? > > We are on GP 10 Still no replies here? Anyone? "DavidM" wrote: > Can any...

Count Instances of value in two columns
Hi, I am trying to count the occurrences of a word in two columns. For example if column A has the value "YES" and column B also has the value "YES" then I want to count that as one instance of meeting the condition that both columns must be YES in order to count the instance in a third column. So the value of column C would be "1". I can easily do this for one column using the COUNTIF function as follows: =COUNTIF(App_Inventory!A1:A10, "YES") Thanks KN Please refrain from multiposting, you have an answer in you other post -- Regards, Peo ...

Counting cells based on color
I am looking for a "counting" formula that will count cells in a rang based on their color, so that if I color a series of dates (to sho 'vacation) the number of cells (days) will be counted...to keep trac of vacation...thanks -- Message posted from http://www.ExcelForum.com There's no built-in functionality for that - it requires a UDF (user-defined function). You'll find several examples in the google archives: http://www.google.com/advanced_group_search? as_ugroup=*excel*&lr=&num=100&hl=en MVP Chip Pearson also has some code: http://www.cpearson.com...

How to refer to the cell containing the formula?
Hi all! Sometimes, I usually end up in a problem while creating UDFs which refer to the cell actually containing the same. I usually use ActiveCell technique but in a loop in a sub procedure its OK while when used in a UDF the ActiveCell is always different where the cursor is place. Furthermore, sure would oblige if one would kindly provide the code for simple formula of reversing the column values like the following, as a UDF: =OFFSET($D$1,ROWS($D$1:$D$20)-ROWS($A$1:A1),0) -- Thanx in advance, Best Regards, Faraz Hi Faraz, > Sometimes, I usually end up in a ...

Pivot
Hi, I have a pivot with Sales information. The data has multiple columns by various product category. I have added a count column next to each product column representing the dollar amount. Is there a way in Pivot to get a calculated field of Total amount / Count for each product to arrive at Average value per deal. Excel does not allow me to use Calculated Item to get the average number. Any suggestion is welcome. Regards Sandip. If you are unable to create the formula in the pivot table, have yo considered placing it in a row next to the table? Just a thought... -- SHMUNC ---------...

Any way to count cells contining one, or more STRIKETHROUGHS?
Hi, I'm using XL2002 and am looking to count the number of cells in a column that have words struck out. In my case all the words in each cell are either all struck out, or none are struck out. First I can't figure out what character a strikethrough is, anyone know? And I can't get the CODE because excel will only return the code for the first character in a cell, which is nothing when there is nothing in the cell, even if you pre-select strikethrough from format cells. In the end I'm looking to make an array formula that will tell me how many cells have a strikethrough AND ...

Rules Wizard/Rules & Alerts Not Displayed in '07 Pro????
I'm using Office 2007 Pro on W7U 32 and neither the Rules Wizard nor Rules and Alerts is displayed in the Tools menu. I've gone to a couple of different tutorial and they show R&A, but it isn't there on mine and I can't find out how to get it to display. Any help will be appreciated as I need this feature. Thanks If you go to View | Toolbars | Customize you can select the menu and click "Reset" and see if that helps. -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm...