Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Adding Numbers without Carry

Do you know the formula for the VTRACS in excel????
VTRACS IS JUST A NAME---

Example:
A-row----B-row-----C-row
MD------EVE--------TOTAL
456-----223--------679
337-----127--------454
896-----589--------375

Looking for the formula to go in
C-ROW to get 679--454--375???
I am adding A row and B row to get
the results in C-row---
Adding with no carry over----
What is the formula to use in C-ROW???


Post your answer or comment

comments powered by Disqus
How can I show a negative number without the minus sign?

Instead of the minus sign I want the number in red.

eg 48000 - 60000 = -12000

How can i delete the minus sign and turn the number red?

Your help is greatly appreciated!

Thank you.

Is it possible to move an excel file into another folder on a shared drive
without carrying over the permissions set in the source folder?

Does anyone have any suggestions on how to sort out a list of numbers without
duplication?
For example,
There is a list of numbers under column A
36,6,6,81,9,9,15,17,18,77,55,55
then
sort out those numbers in ascending order
return 6,9,15,17,18,36,55,77 under column B.
Does anyone have any suggestions?
Thank you in advance
Eric

How do I add the % sign to a existing number without changing the value?
Example: 1.68 should show 1.68% not 168?

I need to have one cell in a sheet generate a random number without changing
after calculations are entered in other cells.

What I've tried so far:
=randbetween(111111,999999)

But this number keeps changing every time any other data is entered into
other cells in the sheet, or a calculation processes.

So, I disabled calculations, but that won't work b/c I need to have the
calculations available.

Oh, and I can't use macros b/c the sheet is going to be posted on the net.

Any suggestions for generating one random number and keeping it? Thanks

Hello,

I'm still learning Excel and I would like help on how to automatically generate four digit numbers without repeating the same number in any four number combination.

Eg. The list would include: 1234, 4321, but not include 1123, 2244.

There must be an easy way to do this in Excel.

Could anyone help me please?

Thank you very much!

Adding Numbers with asteriks
I am studying data for cricket.
In cricket a player can stay Not out by scoring any number of runs, now If he is not out then its displayed with ana asterik.

If i have to compute the total no of runs scored in the entire season / tournament then I come across proper nos and also numbers prefixed or suffixd with asterik.

Like
Match 1 98*
Match 2 109
Match 3 45*
Match 4 125
Match 5 31*
Total 408
Average 204

Now the total would be a sum of all the five score and the average should exclude the number of instances with asterik..

The range can be of any length.
I would appreciate if I could add numbers with asterik (*) and also display the largest and the average...

Hello all!
I'm trying to add cells from a text file that contains numbers with dashes after them. I can't delete the dashes because there are numbers that don't have dashes. Numbers without dashes must be subtracted.

Every time I try to make a formula with one of the ones with dashes, it gives me zero.

Example:
20-
2
These two numbers must add up to 18.

Any way I can do that?

Thanks a lot!

Boyaco.

How do i remove the hyphens between numbers without having to edit without
having to use edit manually. I have a column of 3000 numbers similar to this

0-7643-2300-5, which neds to read 0764323005

Any ideas
Regards Ian

Please I've been trying to discover a way to insert a dash automatically in
many phone numbers without dashes already input in a single column in Excel.
The phone numbers are all unique so I can not use Find and Replace method
with the asterisk wildcard. The only consistent factor is the dash would be
inserted after the first 3 digits of each phone number in the column. Thanks
for any help or tips!

I have a column that has Numbers in the EVEN rows or cells(the number can range from 1000 to 64000), in the ODD rows or cells I have a fromula (if(c1=$d$1,"MAX",if(c1=$D$2,"Min","") This formula is copied down the entire column in each odd row or cell, at the bottom of C1 column (350 rows) I have a sum function. The "Max" & "Min" work great but the sum does not, it comes up blank? I know it is the the formula causing the problem but without adding columns (Spread sheet is Busy enough) or VBA (which I know noting, but willing to learn) How do I fix this?

I dont have enough hair left to pull out so any help or suggestion would be appreciated.

Hello,

I need your help! Please, help me...

In the MS Excel Worksheet Sheet1, Column A there are numbers 1, 2 & 3
which I change manualy and I need to add manualy another new numbers in
combination with old numbers in Sheet1, Column A.

I need a MS Excel Worksheet formula (Please, no VBA) wich automaticall
add numbers from the Sheet1, Column A to the Sheet2, Column A but not
by duplicates in the Sheet2, Column A and automatically recognize a new
added number 4 in the Sheet1, Column A as I type it and add
automatically number 4 as the next in the Sheet2, Column A.

Sheet1 Sheet2
Column A Column A
1 1
2 2
3 3
1
2
3
3
2
2
1
3

Thank you in advance for your help and collaboration.
All the best of the best.
Regards,
Master

--
Master
------------------------------------------------------------------------
Master's Profile: http://www.excelforum.com/member.php...o&userid=25123
View this thread: http://www.excelforum.com/showthread...hreadid=386259

Hello,

I need your help! Please, help me...

In the MS Excel Worksheet Sheet1, Column A there are numbers 1, 2 & 3 which I change manualy and I need to add manualy another new numbers in combination with old numbers in Sheet1, Column A.

I need a MS Excel Worksheet formula (Please, no VBA) wich automaticall add numbers from the Sheet1, Column A to the Sheet2, Column A but not by duplicates in the Sheet2, Column A and automatically recognize a new added number 4 in the Sheet1, Column A as I type it and add automatically number 4 as the next in the Sheet2, Column A.

Sheet1 Sheet2
Column A Column A
1 1
2 2
3 3
1
2
3
3
2
2
1
3

Thank you in advance for your help and collaboration.
All the best of the best.
Regards,
Master

Hi! I'm working on a macro, where some of the action has to do with adding zeroes in front of numbers, so that every number gets the same amount of digits. 4 to be exact. This means that the number 1 is transformed to 0001. 12 -> 0012, and of course 123 will be 0123. You get the point. Every number gets 4 digits, and 0 is the filler.

I am a total newbie when it comes to these things, so my script consists of shameless cutting and pasting from other peoples works. This also means that I don't have the slightest clue of what to do when things aren't going my way.

Everything is going perfectly, except for one important thing: The macro treats numbers with decimals in a way I absolutely don't want it to. I want for example 12,3 to become 0012,3 -- but the macro insists on not changing it at all. I figured this happens because the comma (and the numbers behind it) are counted too. Therefore, a number like 12,3 already has four digits. But I want it to be 0012,3! This is making my head hurt.

My question is: Are there any way of making the program ignore the comma and all the other digits behind it? So that they are not counted. Or is there another way of dealing with this problem? Most of my numbers are without decimals, but the ones who do have them are causing severe damage to my nerves.

All help is tremendously appreciated!

(I don't know if I have explained myself good enough, but I hope I have. I'm from Norway, and my English isn't top notch.)

If you want to take a look at the code, here it is:

Code:
 Sub AddZeros()
	Dim Cl
 
	Selection.NumberFormat = "@"
	For Each Cl In Selection
		 'Change the 10 below to the total desired Length of the cell
		 '0's will be added to reach this length.
		Cl.Value = PadVal(Cl.Value, 4, "0", False)
	Next Cl
 
 
 
End Sub
 
Function PadVal(ByVal StartVal As String, EndLen As Integer, PadWith As String, Optional AtEnd As Boolean = False) As String
	 'Arguments
	 'StartVal = Value to be "Padded"
	 'EndLen = The desired total charecter count of value
	 'PadWith = The charecter to add to the StartVal to reach the desired EndLen
	 'AtEnd = Pad at end of the StartVal string... True pads at end, False pads at beginning.
 
	Do While Len(StartVal) < EndLen
		Select Case AtEnd
		Case False
			StartVal = PadWith & StartVal
		Case True
			StartVal = StartVal & PadWith
		End Select
	 Loop
	PadVal = StartVal
End Function


I have an inventory sheet where I occassionally have to create more rows with the same information. So I will create rows and then copy and paste info into them. What I would like to do is be able to drag the information by clicking on the lower left corner and populate the cells that way. The problem is that if there is a number in the cell then it will automatically add to it. For instance, if I try to drag the contents of cell labeled klaraid 1112 then it will add incrementally to this in the cells below.....1113, 1114 and so on. If there are no numbers in the thing i'm dragging then it works fine. How do I drag cells with numbers without it adding to the contents incrementally?

Hi, I am new here and would like to get help on adding/minus numbers lotto style, no carrying over.

I want to input pick 3 (3 numbers) into a cell and with the assigned SUM to that cell to add +123 to the 3 numbers I input, but how do I get the answer not to carry over?

For pick 3 games, the numbers are; 0,1,2,3,4,5,6,7,8,9 (ten total).

When I add +123 to 987 I get 1110. I don't want that. I would like it to show 010 instead, lotto numbers no carry over. How can I achieve this? Thank you in advance.

Hi All,

I cannot figure out how to add just the number of filled cells in an array (in most cases I'm not interested in whats actually in the cells, I just want to add up how many actually do have a number and are not empty). All in all, though, I need the macro to do this:

1. Subtract 10 from cells C7 down to C45
2. Subtract 5 from cells B6 down to B45
3. Add the number of filled cells (again, I don't care what is in them just the number of cells with something in it - could range from 0 to 1,000) for rows 6 through 45 starting in column C (so rows C6 down to C45) and place the number of filled cells in each row in column ZZZ for that row (i.e. the number of filled cells for rows C6 through C45 and put that number in the corresponding ZZZ6 through ZZZ45 cells)
4. Add ZZZ6 through ZZZ45 (now add the actual contents of the cells) and place in cell E2
5. Divide "E2" by the number of filled cells (again, contents don't matter here) from B6 to B45 (can range from 0 to 40). Place the results of that equation in cell E1.

The most important part of this is adding the filled cells in rows C6 through C45 and placing them in ZZZ6 through ZZZ45, and then after the other math, dividing "E2" by the number of filled cells in column B from cell B6 to B45.

Any help is much appreciated, thank you in advance...

Allright I am using the code below to add all numbersv in colums 11 and 13 of a workbook, that is straight forward the catsh is these colums include things other than numbers, I used the resume next to deal with this.

	VB:
	
 
ActiveWorkbook.Sheets("2004").Activate 
Dim ii As Integer 
 
On Error Resume Next 
i = 8 
ii = 11 ' for sales
Do Until Cells(i, ii - 10) = "" 
    Sales_Overall_Total = Sales_Overall_Total + Cells(i, ii).Value 
    i = i + 1 
Loop 
i = 8 
ii = 13 
Do Until Cells(i, ii - 12) = "" 
    Cups_Overall_Total = Cups_Overall_Total + Cells(i, ii).Value 
    i = i + 1 
Loop 
On Error Goto 0 
 
Cups_Overall_Total = Cups_Overall_Total / 2 ' because the colum includes totals
Sales_Overall_Total = Sales_Overall_Total / 2 ' because the column includes totals
MsgBox Sales_Overall_Total 
MsgBox Cups_Overall_Total 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a better way of doing this without the use of resume next?
Any comments or pointers are appreciated.

Layth

Hello all,

I am making a spreadsheet to keep track of local rac car points for each night of racing. There are about 75 cars and 26 weeks of racing. Last year is was pretty simple as each racer recieved x number of point per week. This year we are addinf "Bonus points".

Instead of creating another sheet for "Bonus Points" or adding another group of cells for each week. I would like to put the points and to bonus in the same cell. THe problem I have is people like to see the points as well as the bonus

Example,
I know I can put =10+5 and the cell wouls shouw 15. I want it to diplay 10,5 and have it still use 15 for all the calculation.
FYI - the seperator does not need to be a comma. it can be and character or even a space.

Thanks,

Ross

I need to add the same number to a column of 100 number (A1:A100) Is there a fast way i can do this without using vba?

I HAVE A SHEET THAT WITH ALL THE FORMULA's ATTACHED WORK OUT HOW MUCH IS REQUIRED SO THAT THE TEAM CAN CHECK/TICK OFF IN GROUPS OF 10.

THE QUESTION I HAVE, I HAVE IN (A & C) GROUPS OF 10 THAT ARE AUTO COLOURED WHEN THE QUANTITY FROM AB34 & AB35 HAVE QUANTITIES ATTACHED.
I WILL ATTACH A SHEET FOR YOU ALL TO SEE.

WHAT IM AFTER IS, I HAVE WORKED OUT HOW TO COLOUR THE TOTAL NUMBER OF CELLS (THAT REPRESENT STACKS) FROM AB34 USING CONDITIONAL FORMATTING.

HOW CAN I ATTACH AB35 NUMBER TO THE NEXT CELL (THAT IS COLOURED) SO IT TELLS ME I REQUIRE 7 STACKS & 70 UNITS.
AS SAID I HAVE GOT THE FORMULA TO WORK SO IT COLOURS THE 7 STACKS BUT DO NOT KNOW HOW TO WORK OUT HOW TO GET THE 70 (UNITS) ATTACHED TO THE NEXT CELL. I HAVE TO MANUALLY DO THAT EVERYTIME.

HERE IS THE SHEET PLUS THE FORMULA FOR THE COLOUR CELLS REQUIRED:

******** ******************** ************************************************************************>Microsoft Excel - Mon-Tue%20Schedule%20v1(1).xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA8A9A10A11A12A13A14A15=
ABCD71 35 82 36 93 37 104 38 115 39 126 40 137 41 1487042 159 43 1610 44 800g Seeded 2nd Run 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************>Microsoft Excel - Mon-Tue%20Schedule%20v1(1).xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAB34AB35=
ABACADAE347Open Stock Stacks  3570Open Stock Units  800g Seeded 2nd Run 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

This is my Excel dilemma: creating roundrobin pairs.

For a given list of numbers, I need to generate pairs where each number is paired with all the other numbers without creating a duplicate pair.

For example: 1,3,4,6... (dynamic)

Pairs: 1-3, 1-4, 1-6, 3-4, 3-6, 4-6

Results should be generated in two columns. Column A would be ID1, Column B would be ID2 (the combination of both would be the pair created)

ID1ID2131416343646

I have basic ideas for a macro but the loop within a loop and the OFFSET for the next row is something I really can't put together.

I would really appreciate any help at this point. THANKS!

Is there a way to generate random numbers between 1 and 63 without any duplicates?

Hi - can someone please advise how I can add filtered data in a column without adding numbers that have been filtered out?

eg SUM(A5:A121) but exclude the values that have been filtered out.

Thx


No luck finding an answer? You could always try Google.