How do I round up numbers when dividing?
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
> 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...Lookup with two values
Can anybody help me to do lookup function with two values.
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,
Message posted from http://www.ExcelForum.com/
One way would be to use an extra column with a concatenation of columns A
Insert a new column C on both sheets
In C2 enter = A2&"|"&B...Default number formatting
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
...how to compare two excel files
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?
There are a number of programs out in the market that provide more complex
Otherwise you could simply make a new sheet and copy the following formula
anotherway to do it is to run the following you need to set up the workbook
and workshee...Dragging and dropping cells with the keyboard
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
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...Two Versions
Is it possible and easy to have two versions of Excel on the same PC? If so,
This is no problem.
See this KB
Regards Ron de Bruin
"Bill" <email@example.com> wrote in message news:eGSkrYy1EHA.1264@TK2MSFTNGP12.phx.gbl...
> Is it possible and easy to have two versions of Excel on the same PC? If so, how?
Sure. This one has six Excel versions running, sometimes simoultanously.
...how do you ignore blank cells
I am trying to write a formula that results in the earliest date but a blank
cell results in a 0.
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
"Kerry" <Kerry@discussions.microsoft.com> wrote in message
news:D3F62EBB-275C-4B77-BBB3-93EBB968DF19@microsoft.com...seeking the fastest workflow for converting bmp image into Excel or ASCII table with numbers
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:
(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...How do I shade the space between two data series in Excel?
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?
Have a look at this example.
You can shade areas using additional data plotted as area or stacked
> 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...Extract the Month from a cell with a formatted date
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
There are a few ways, like:
"Alcadia" <Alcadia@discussions.microsoft.com> wrote in message
>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...Combining cell reference with text
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'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 Contacts
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.
...How can you convert a number to superscript?
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.
PowerShow - View multiple PowerPoint slide shows simultaneously
"klutz" <firstname.lastname@example.org> wrote in message
> while writing equations in MS ....??
...Moving between cells copies the screen!
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?
Try hitting the scroll lock button.
> 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 ...two units on primary Y axis
I have spent quite some time looking for a solution and I doubt there is
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..
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 ...Comments in Cells #2
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.
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
That it your off to the races.
...Stock showing up under two symbols
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.
...Views on two different size monitors
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
Found that wh...Dividing Line
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" <email@example.com> wrote in message
> After entering some misc. bank charges that were from the summer a
d...Need function to return numbers at beginning of text
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...How to Determine Number Inbound/Outbound messages per user?
Does anyone know a way to determine the #of Inbound/Outbound messages per
user on Exchange 2000?
Thanks in advance.
Crystal Reports can be used to generate Exchange reports.
>Does anyone know a way to determine the #of
Inbound/Outbound messages per
>user on Exchange 2000?
>Thanks in advance.
Crystal? How would one do that?
...Formatting pasted cells
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
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:
(look for "Sub Trimall(...Graphing non adjacent cells
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
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.
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...How many named cells
In excel 2003,
is there a limit to the number of named cells or named ranges you can
there is no real limit that I know of, but things can get a bit slow if you
get above 5-10000 names.
FastExcel 2.3 now available
Name Manager 4.0 now available
"greg" <firstname.lastname@example.org> wrote in message
> In excel 2003,
> is there a limit to the number of named cells or named ranges you can
this ...Display leading zeros
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.
I suppose the number o...