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 |

1/15/2010 3:07:01 AM

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 |

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 |

1/15/2010 3:33:01 AM

What I am doing is in regards to money. I am dividing an amount between two people and sometimes the amount can't be divided equally so I want to be able to have the formula automatically adjust. So for instance: $5000.23 divided by 2 does not divide equally. So person A should get $2500.12 and person be should get $2500.13 The same thing goes for $523.00 Thanks. > So for instance: $5000.23 divided by 2 does not divide equally. > So person A should get $2500.12 > and person be should get $2500.13 If the total amount is in A1 and you want Person A and Pe...

Can anybody help me to do lookup function with two values. For eg., Sheet one having: Part no OrderNo Status (in column A,B,C consecutively) Sheet two having Part No OrderNo in column A, B Consecutively). I want to add Status from Sheet one to Sheet2 in columnC, looking into both partno and orderNo as lookup value. DGET is not working. Thanks in advance, itty. --- Message posted from http://www.ExcelForum.com/ Hi One way would be to use an extra column with a concatenation of columns A and B. Insert a new column C on both sheets In C2 enter = A2&"|"&B...

Hi, I am currently experiencing some difficulties with my number formatting within Excel 2007. I have a fairly complex series of worksheets, which now seem to be defaulting to the Accounting format. I think that I may have done this through a replace all, somehow setting the cells to all be Accounting format. Peculiarly when I add a new worksheet, the cells also default to Accounting format Is there a simple way in which I can use a combination of Replace all and formatting to reset all the blank cells to be to be in General format? Many thanks, Ben ...

i would like to know how to compare two excel files. is there any tool for that? in addtion is there any way to maintain several versions of the same file with easy way to have diff between them? thanks, -- Miri Tz. Hi Miri, There are a number of programs out in the market that provide more complex comparision. Otherwise you could simply make a new sheet and copy the following formula into A1: =if('[book1.xls]sheet1'!a1='[book2.xls]sheet1'!a1,"","<<<") anotherway to do it is to run the following you need to set up the workbook and workshee...

Is there anyway to drag and drop cells using keyboard shortcuts For example in Word you can highlight text, press F2 and then move the cursor to the desired locatiion and press enter I know you can copy/cut and paste but dragging and dropping cells with formulas maintains cell references without having to input '$' holding signs. This would be particularly helpful for large ranges Thank you for your time Tom You can cut (Ctrl+X), then use the arrow keys to another cell, and paste (Ctrl+V). The original references are maintained. Or, select a cell, and press the F2 key to enter Edi...

Hello, Is it possible and easy to have two versions of Excel on the same PC? If so, how? Thanks. Bill Hi Bill This is no problem. See this KB http://support.microsoft.com/default.aspx?scid=kb;en-us;214388 -- Regards Ron de Bruin http://www.rondebruin.nl "Bill" <whmc2@ix.netcom.com> wrote in message news:eGSkrYy1EHA.1264@TK2MSFTNGP12.phx.gbl... > Hello, > Is it possible and easy to have two versions of Excel on the same PC? If so, how? > > Thanks. > > Bill > Hi Bill Sure. This one has six Excel versions running, sometimes simoultanously. ...

I am trying to write a formula that results in the earliest date but a blank cell results in a 0. =MIN('1Jan-9Jan'!R196,'10Jan-16Jan'!R196,'17Jan-23Jan'!R196) What do I need to do to this formula to ignore blank cells This is the idea. Modify this array formula to suit your needs. An array formula must be entered/edited using control+shift+enter vs just enter =MIN(IF(A1:A10>0,A1:A10)) -- Don Guillett SalesAid Software donaldb@281.com "Kerry" <Kerry@discussions.microsoft.com> wrote in message news:D3F62EBB-275C-4B77-BBB3-93EBB968DF19@microsoft.com...

Hi all, I have a lot tedious data input that I need to process. Currently I am doing these whole things manually, which is extremely tiring. Please help me out: The workflow: (1) Browse webpages, the webpages have a lot of numerical tables. (2) I copy the table into clipboard, at this stage, the table is an image in the clipboard. BMP image. And it has numerical and non- numerical cell values. Because the table has headings and decorations, such as shaded headings, shaded numbers. In fact, these tables were taken out from Excel themselves, and it is just now they are displayed on webpages a...

I want to shade in between two data series on an xy scatter chart when they cross over each other. Is this possible in Excel or are there any add on graphics packages that would allow more flexibility with charts? Hi, Have a look at this example. http://www.andypope.info/ngs/ng21.htm You can shade areas using additional data plotted as area or stacked area charts. Cheers Andy spike5150 wrote: > I want to shade in between two data series on an xy scatter chart when they > cross over each other. Is this possible in Excel or are there any add on > graphics packages that would...

I have a cell AD7 with a formatted date "dd/mmm/yyyy" to count orders and i need to count how many orders for each month. how do i count MMM as 1 to another column? There are a few ways, like: =month(ad7) =text(ad7,"mmm") Regards, Fred "Alcadia" <Alcadia@discussions.microsoft.com> wrote in message news:A46C3F39-3AA9-463A-8A17-47D050E85552@microsoft.com... >I have a cell AD7 with a formatted date "dd/mmm/yyyy" to count orders and i > need to count how many orders for each month. how do i count MMM as 1 to > another colum...

I know how to use the & symbol to combine cell references with text, but i am having trouble getting this to work when the 2 cells i am referencing are dates. For instance, lets say cell A1 is 11/15/05, and cell A2 is 3/21/06 and i would like cell A3 to say November - 05 to March - 06. I have tried =A1&" to "&A2 But i keep getting a #value error, any ideas? -- mattflow ------------------------------------------------------------------------ mattflow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25129 View this thread: http://www.exc...

How do I search for a telephone number in a sub-folder of Contacts? When I try a search for a number, that I know is there, it comes up blank. Regards, Peter Ledden ...

while writing equations in MS ....?? Ctrl+Shift+= keyboard shortcut will get you the superscript. If you have already typed the number, select it and then press Ctrl+Shift+= to convert the selection to superscript. - Chirag PowerShow - View multiple PowerPoint slide shows simultaneously http://officeone.mvps.org/powershow/powershow.html "klutz" <klutz@discussions.microsoft.com> wrote in message news:25B7024A-8ED1-4928-94B7-DC8A1615AFDA@microsoft.com... > while writing equations in MS ....?? ...

Working within Excel, I copy one cell and move down a few cells to paste. Every time that i press the arrow down key on the keyboard to move down, the program immediately copies the screen! Does anyone know how to disable this since what is pasted is the screen rather than the originally copied cell? Thanks! Try hitting the scroll lock button. RuiP wrote: > > Working within Excel, I copy one cell and move down a few cells to paste. > Every time that i press the arrow down key on the keyboard to move down, the > program immediately copies the screen! Does anyone know how to ...

Hay!! I have spent quite some time looking for a solution and I doubt there is one. Is it possible to have two units on primary Y axis such us for example EUR /USD.. For example primary Y would have values such us : 1000 EUR / 1300 USD, 2000 EUR/2600 USD etc.. If there was a way that custom format would include possibility of calculation for unit.. tnx Rado Hi, Perhaps you can create a fake value axis using a dummy data series plotted as xy-scatter. Display data labels which are linked to cells that have the required text. Data for series would be like, X Y Label 0 ...

I have come across a few spreadsheets where a comment will pop up when a cell is selected. The unique thing is there is not a comment holder in the cell. Is there a setting or do you have to insert some VBA code to do this? Thanks you in advance for any help. Jacob I've found out my own answer but FYI for anyone else wondering how to do this see instructions below. Select cell that you want to insert comment in Left Click Data -> Validation -> Input Message Type Message you would like to have Click OK That it your off to the races. Jacob ...

I am using MSm 2004. When I download from Vanguard a stock comes in as BRK B. But, moneycentral.msn calls the stock BRK.B. How do I get it to recognize that these are one and the same instead of showing up twice in my portfolio? I have a similar problem with Nasdaq 100 Tr Unit Ser 1. The Vanguard download does not give it a symbol, but the symbol on moneycentral.msn is QQQQ. So it shows up twice in my portfolio. ...

My CFO has a 24" widescreen monitor. Most other Users have 17" or 19" normal monitor. When the CFO opens our Finacials Workbook, makes changes and Saves, all ok. However, after doing so, when someone else opens it, the workbook and some of its contents like the graphs and charts are now enlarged. So they typical users have to use the "fit to window" function in Excel to get the sheets within the workbook to view normally. CFO has Excel 2007 Everyone else Excel 2003 Is it possible that this is the issue? Any help would be greatful More information: Found that wh...

After entering some misc. bank charges that were from the summer a dividing line appeared below my last check and all the new information I try to enter falls below this line. No matter how I try to get it to the correct posting date it stays below the line. Any ideas? What if you enter a transaction with yesterday's date? Does it move back above the line once entered??? "diamonds2" <diamonds2@discussions.microsoft.com> wrote in message news:67EFE9F3-668A-49B1-ACCE-19A6FBF7098A@microsoft.com... > After entering some misc. bank charges that were from the summer a d...

I have a column of cells that contain an id and description in one column. I need a formula that will give me only the id. The id can be either 4 or 5 numbers. Some can be followed by a letter and some can be followed by a dash and then a number. What they all have in common is that the id is followed by a space and then the description. So, the =LEFT formula will not work. I need on that can give me all characters before the space regardless of whether that's after the first 5 characters or after 7 or 8 or however many there are before the space. Anyone know of such...

Does anyone know a way to determine the #of Inbound/Outbound messages per user on Exchange 2000? Thanks in advance. -Jim Crystal Reports can be used to generate Exchange reports. >-----Original Message----- >Does anyone know a way to determine the #of Inbound/Outbound messages per >user on Exchange 2000? > >Thanks in advance. >-Jim > > >. > Crystal? How would one do that? ...

Hi, I have pasted data from a webpage into a worksheet. In order to calculate with it, I need to reformat. I reformat it to "Number" and try to make a formula, but it does not calculate it. I get a zero as result (not an error message). I also tried other formats such as general. Any idea what is wrong? Thorbjorn Sometimes extra characters (like spaces and HTML non-breaking spaces) will get pasted into those cells, too. David McRitchie has a routine that will clean up this kind of stuff at: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall(...

I want to create a scatter plot of the following cells: A1 thru A10801 every twelfth row that is A1,A13,A25,A37 ... A10801 as the x axis and C7 thru C10807 every twelfth row that is C7,C19,C31,C43 ... C10807 as the y axis. How do I do it? Thanks in advance. Hi struggling_with_excel, An easy way to chart non-adjacient cells is to create a range that contains formulas that reference the cells you wish to chart, then chart that range. In the situation you describe, the trick is to write formulas that point to every twelfth row in columns A and C. Here is one way to do it. I will a...

Hello, In excel 2003, is there a limit to the number of named cells or named ranges you can create? thx there is no real limit that I know of, but things can get a bit slow if you get above 5-10000 names. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "greg" <greg@nospam.com> wrote in message news:%23W9XGH$QHHA.496@TK2MSFTNGP06.phx.gbl... > Hello, > In excel 2003, > is there a limit to the number of named cells or named ranges you can > create? > > thx > > this ...

I extract some data from a POS and create a .csv file. One field is an item number which can have 1 or multiple leading zeros (from the barcode). The ..csv file shows the leading zeros correctly when I load it into a text editor but when I load it into Excel (2003) the leading zeros are not displayed. However, because I need to load the Excel into Quickbooks I need to retain the leading zeros. I checked the options and also the help file but did not find anything I could do to show the leading zeros in Excel. Any comment/help is greatly appreciated. Hi Gerd, I suppose the number o...