Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Can I Add A Quantity To Every Cell In A Column Using A Formula?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is there a formula to use to add a quantity to every cell in a column?

I need to be able to add a quantity of 3 to every row in a column.

Any help would be appreciated.

Thanks!


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

Similar Topics







Hi,

I have a problem I hope you can help me with. I am working on a pricelist that has different quantity breaks. I am try to get subsequent cells to automatically generate the correct quantity according to what is in the previous quantity cell. So for example, if the first quantity is 50, the next needs to be 100. In most cases, like in the above example, the quantity just doubles.

However, there are a few exceptions and this is where I am stuck. For example, if a number is quantity is 12, the next one needs to be 25, or if a quantity is one, the next needs to be 5.

I have built the following formula, but can't get it to work. Help, please!

=if(X9*2,(IF(X9<=4,5,IF(X9<=24,25,IF(X9<=249,250,IF(X9=300,500,IF(X9=1000,2500,IF(X9=10000,25000)))) )))))

in the formula above, X9 represents the first quantity.

Many thanks and I look forward to hearing from you.

Fionn


I have a sheet that has the following format:

Row 1, Column A = Kit ID 1
Row 1, Column B = Quantity 1
Row 1, Column C = name 1
Row 1, Column D = Quantity 2
Row 1, Column E = Name 2
Row 2, Column A = Kit ID 2
Row 2, Column B = Quantity 1
Row 2, Column C = name 1
etc...

The quantities and name can go up to column BD depending on how many items are associated with each kit id.

What I am trying to do is break the information up and make the data appear as Kit ID, Quantity, Name and then break to the next row where it will again have Kit ID, Quantity, Name:

Row 1, Column A = Kit ID 1
Row 1, Column B = Quantity 1
Row 1, Column C = Name 1
Row 2, Column A = Kit ID 1
Row 2, Column B = Quantity 2
Row 2, Column C = Name 2

etc.. until all info from row 1 is done then

Row x, Column A = Kit ID 2 etc.

Can someone point me in the right direction on how to go about this? Any help is very much appreciated.

Thanks




I need some help with a formula to the attached spreadsheet. Below is a copy of a sample problem. I need to be able to enter in a column quantity (355) and a row quantity (3) and have the answer ($1.10) show up in a 3rd cell. The quantity categories are messing me up because there is a spread for each column category like (1-12 units).



QUANTITY? 355 COLORS? 3 AMOUNT? $1.10



UNIT QUANTITY
1-12 13-36 37-72 73-144 145-300 301-600 601-1000 1001-2400 2401-5000 5001-10000
1 $2.25 $1.85 $1.60 $1.30 $0.95 $0.80 $0.65 $0.55 $0.45 $0.40
2 $2.55 $2.10 $1.80 $1.47 $1.10 $0.95 $0.78 $0.68 $0.55 $0.48
3 $2.85 $2.35 $2.00 $1.64 $1.25 $1.10 $0.91 $0.81 $0.65 $0.56
4 $3.15 $2.60 $2.20 $1.81 $1.40 $1.25 $1.04 $0.94 $0.75 $0.64
5 $3.45 $2.85 $2.40 $1.98 $1.55 $1.40 $1.17 $1.07 $0.85 $0.72
6 $3.75 $3.10 $2.60 $2.15 $1.70 $1.55 $1.30 $1.20 $0.95 $0.80
7 $4.05 $3.35 $2.80 $2.32 $1.85 $1.70 $1.43 $1.33 $1.05 $0.88
8 $4.35 $3.60 $3.00 $2.49 $2.00 $1.85 $1.56 $1.46 $1.15 $0.96
9 $4.65 $3.85 $3.20 $2.66 $2.15 $2.00 $1.69 $1.59 $1.25 $1.04
10 $4.95 $4.10 $3.40 $2.83 $2.30 $2.15 $1.82 $1.72 $1.35 $1.12
11 $5.25 $4.35 $3.60 $3.00 $2.45 $2.30 $1.95 $1.85 $1.45 $1.20
12 $5.55 $4.60 $3.80 $3.17 $2.60 $2.45 $2.08 $1.98 $1.55 $1.28

Thanks for anybody's help on this.

Rick

Hii.

In a worksheet, column "B" includes the "Quantity". There are around 150 Items in Column "A", in front of these items, quantity remaining comes in cells of column "B". I have used formula to get the value of quantity from different files.

A B
Item Quantity
XYZ 15
RST 0
ABC 8

I need that if quantity is 0(zero), the complete row hides automatically.






I am trying to get information I post in one sheet of a workbook to automatically update the relevant information in a different sheet of the same workbook. Hopefully, this will save me a few steps at work.

The first sheet is where I am inputting customer invoices. They get put in as I receive them, so I can't put them in any order. The second sheet has the parts and set prices for the various quantities of those parts.

What I want to happen, is when I input the part number in one field and part quantity in another, a formula will automatically fill in a cell on the second sheet with the total of that part quantity and all other of the same part (including different quantity).

As an example:

Cell A2 has part number 12. Cell D2 has part quantity 5
Cell A3 has part number 12. Cell D3 has part quantity 10
Cell A4 has part number 30. Cell D4 has part quantity 5
Cell A5 has part number 12. Cell D5 has part quantity 5

On sheet 2, I have the part numbers in column A. Column C would be part quantity 5 and Column D would be part quantity of 10. Let's put part 12 in row 12 and part 30 in row 30.

I want column F to take the data and combine everything. In this example, Cell F12 would take the date from sheet 1 to add sheet 2 cell C12 twice and D12 once.

So, if quantity 5 cost 50 and quantity 10 cost 90, then F12 would give me the result of 190.

I hope I explained this right.

when using vlookup, how do you direct the formula to look for a text string in a column, and use the data in the column to the immedate right of that column?

for example sometimes we put product A, quantity of product A in col 1 & 2 respectively, and sometimes product A, quantity of product A in col 3&4 or 9&10.

regardless where I put product A, and it's corresponding quantity, I want the quantity to go to the proper inventory column on the inventory page.

currently we enter the quantity by "tabing" to the correct column. If someone enters the quantity in the wrong column, our inventory gets messed up.

Can someone help me?


Hi
this is very dynamic requirement.
I have attached image as well sample sheet for what i need.

In brief:
I want to transfer(cut or moving) the cells to respective column but on same row from where is has been cut.

For example: (where , (comma) means different column

Account No.1
item33,quantity,amount,
item2,quantity,amount,
item48,quantity,amount,

Account No.2
item9,quantity,amount,

Account No.3
item19,quantity,amount,
item2,quantity,amount,
item6,quantity,amount,
item75,quantity,amount,
item52,quantity,amount,

Now i want put it like this

Account No.1 item33,quantity,amount,
Account No.1 item2,quantity,amount,
Account No.1 item48,quantity,amount,

Account No.2 item9,quantity,amount,

Account No.3 item19,quantity,amount,
Account No.3 item2,quantity,amount,
Account No.3 item6,quantity,amount,
Account No.3 item75,quantity,amount,
Account No.3 item52,quantity,amount,

I have also attached image and sample excel file.

Thanks

I have a table that looks like this:

Column 1: ID
Column 2: January '07 quantity
Column 3: February '07 quantity
Column 4: March '07 quantity
Column 5: April '07 quantity
...
Column 54: June '11 quantity

I want to create a new simplified table that has three columns

Column1: ID
Column2: Month
Column3: Quantity

Is there an easy way to do this without creating 54 separate append queries?
Is there an easy


Hi everyone, I'm in need of some assistance.

I have a spreadsheet. In column A, I have a unique identifier number. In column B, I have a quantity. My problem is there are around ~600 duplicates. Ex:

ID | Quantity
23 | 4
23 | 8
24 | 4
25 | 4

I want to remove the extra ID, but sum the quantity value together, is this possible?
Ex (following from above):

ID | Quantity
23 | 12
24 | 4
25 | 4

Any help is appreciated. Thanks!


Hello!
I need some help please ....I need a formula that will calculate the quantity of each size of an item (XS,S,M,L,XL). For example to calculate the quantity for Mediums, I need the formula to look at column C, if M is entered, then look at the adjacent cell in column A for the quantity and formulate a total number (from rows 4-21) and enter the total quantity in C27.
See attachment.
Thanks for your help! Kim




Hello there,

I found this Inventory and Sales Spreadsheet online and I am wanting to be able to add the option to put Quantity column in each month. Currently it only calculates each row as on item sold (when sales column reads 'yes'), however when I sell large quantities to retailers etc, I don't want to have to list them one at a time.

How might I edit the formula in column H under 'Mater Inventory' to reflect the quantity sold? I want that to lookup and calc the quantity from the 'Quantity' column I added to each month.

Please let me know if you need more information or further description.

Thanks in advance,
Lorne



Hi guys i'm now working on a spreadsheet and was wondering whether this is achievable by using excel basic formula:

I have created a databank for a list of item with their accordance price.

For Instance (Sheet1):
Cell A1=Item A = $8.00..........Quantity = 1
Cell A2=Item B = $13.00.........Quantity = 0
Cell A3=Item C = $18.00.........Quantity = 1
Cell A4=Item D = $23.00.........Quantity = 0

Let say Customer A would like to buy Item A and Item C only is there a way to display the final list as:

For example (Sheet 2):
Cell A1=Item A = $8.00..........Quantity = 1
Cell A2=Item C = $18.00.........Quantity = 1
**Item B and Item D will not be shown

I would like excel to automatic capture those item with quantity>1 only and arrange them in order.

Can anyone provide any idea on this?

in first cell (A1) i have a code: 553282300000
in second cell (B1) quantity: 50
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 160.

Here is the example:

Code........................Quantity

553282300000............... 50........................55
553290240040..............160........................58
553480480020..............420........................7E
553492300040............... 20
583182300060SMA.......100
585281100060SMA........ 80
7E1382300000................15
7E1682300000................38
7E3684000000................73
7E3684000002................57


What i have to do is to sum the quantity of the products that begin with 55 in "C1", quantity of the products that begin with 58 in "C2" and the quantity of products that begin with 7E in "C3".
Can someone help me?

Thank you!


I have a list of data.. I wish to make column "A" numbers random. Is there a way to do it?

ORIGINAL
1...keyboard....quantity 1
2...fan coil...quantity 4
3...mouse...quantity 2
4...staples...quantity 7
5...menu...quantity 3
6...thumbdrive...quantity 1
etc

RESULT
1...staples...quantity 7
2...thumbdrive...quantity 1
3...fan coil...quantity 4
4...mouse...quantity 2
5...keyboard....quantity 1
6...menu...quantity 3
etc


I have a huge amount of data which i would like to keep consistent.

In 1 cell i have a quantity and in another cell in a different column i have the quantity to date value. I know how to make it so the quantity to date cell does not exceed the planned quantity but is there a way of repeating this for hundreds of situations rather that data validate every single cell, just like if you were dragging a formula through a range of cells?

please help




Please help me I have a COLUMN:

Product Quantity in Stock Remaining Quantity in Stock Request Quantity MY FORMULA IS: Quantity in Stock - Request Quantity = Remaining Quantity in Stock



EXAMPLE:
Product Quantity in Stock Remaining Quantity in Stock Request Quantity PO12 450 200 250


All I want to do is to create another entry that will deduct from the same product but it will copy the Remaining Quantity in Stock to Quantity in Stock so that if I deduct another 100 it will deduct on the current Remaining Quantity in stock. Please help me to do it... Thanks

in first cell (A1) i have a code: 553282300000
in second cell (B1) quantity: 50
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 160.

Here is the example:

Code........................Quantity

553282300000............... 50........................55
553290240040..............160........................58
553480480020..............420........................7E
553492300040............... 20
583182300060SMA.......100
585281100060SMA........ 80
7E1382300000................15
7E1682300000................38
7E3684000000................73
7E3684000002................57


What i have to do is to sum the quantity of the products that begin with 55 in "C1", quantity of the products that begin with 58 in "C2" and the quantity of products that begin with 7E in "C3".

NOTE: OP requires VBA solution - WF solution provided previously he related thread: http://www.excelforum.com/excel-work...st-column.html
(D.O)


Hello All,

I have a inventory question that I need help with.

Lets say cell:
A1=Quantity in stock (person enters)
B1=Quantity used per day (person enters)
C1=Quantity used per week (person enters)
D1=Reorder Level (Person enters)
E1=Status (Condition formula)

What I need formulas for are
1. Subtract quantity used per day(b1) or quantity used per week (C1) from quantity in stock (a1). Then I want the total of this (a1-b1 or c1) to replace the quantity in A1 as the new quantity in stock.

After the calculation is completed I am looking for a formula that then clears the cells (B1 or C1) for the next entry (the next day/week).

2. A status formula that will state when A1 (quantity stock) is above the reorder level (d1) the cell (e1) will be green and say In Stock, otherwise if a1 is less than or equal to d1 then state Need to order, and turn cell e1 yellow.

If anyone can help I would really appreciate it!!! I would even pay for your time invested if I need to!!

Thanks!!


Hi,

I'm working on some VBA to re-format some quotations to a more basic layout for data entry. The re-formatted sheet is very simple containing just the product code in column A and the quoted quantity in column B.

A colleague has asked me if the quantities could be ommitted but if the quantity is greater than 1 then it should duplicate the code underneath the original (duplicate once for a quantity of 2 or twice for a quantity of 3 and so on).

I'm not really even sure where to start with this - can anyone help?


Ok, so here's the situation:

I have one static cell, in this case it's "F48" and I need it to equal the sum that I have at the bottom of another range of data. That range of data always changes in the amount of rows, however the columns are static. In column "J" is the part number, column "K" is the description, column "L" is the quantity, and the only purpose of column "M" is to label the total quantity at the very bottom of the data where everythign is summed.

I can't simply just do a sum formula in the F48 cell because of how everything is arranged and how it changes. Basically, I just need code that looks for the term "Polarized" in column "M", and takes the total quantity that is in the cell directly to the left of it, and copy that quantity into F48.

Any ideas?


Help. What I need to do:

I have a spreadsheet with a list of products, quantity, descriptions, discounts, etc., used to prepare estimates and orders.

Each row is an item and information to configure item cost. I have an item quantity in column (A). I have a numerical Pts in column (L).

I have a reference table at the top with y value ranges (1, 2-5, 6-17, 18-47, etc), to select from the quantity in column A. The x values are number ranges (pts) (0-5000, 5001-10000, 10001-20000, over 20000.) to select from column L.

I need a formula for a cell in the same item row (J), determined by table value from quantity and points.

Summary: I have to enter a value used to determine pricing, which is based on quantity and stitch count for embroideries.

Help anyone?


Box Size (units that can be placed inside)
F G H I J K
Quantity 1 - 7
units 8 - 30 units 31 - 40 units 41 - 84 units >84 8 0 1 0 0 0 4 1 0 0 0 0 4 1 0 0 0 0 7 1 0 0 0 0 7 1 0 0 0 0 210 0 0 0 0 3 7 1 0 0 0 0 7 1 0 0 0 0 4 1 0 0 0 0 14 0 1 0 0 0 70 0 0 0 1 0

I have this table with me which follows the quantity and the units (boxes required) that is involved when it is within range

the formula for B=IF(AND(F2>0,F27,F230,F240,F284),F2/84,0)

Lets look at row 6 where the quantity is 210 and the box that is needed is 3.

What i want is that the quantity of the range higher than 84 is 2 and the rest is given to the columns that can take the quantity of the its balance
example= 210/84= 2.5 which equals to 2 box (that can fill 84 units each)
and 1 box that can fill 42 units which is the box @ column J.

How can i do this? what formula to use?
Please help me, Excel Guru




hi to all

there is a serous problem. i have a quantity column in my bank. i want to update it by another quantity column in outcomefactor and manually with hand!
in quantity from outcomefactor, my quantity in bank must be updated with subbing.
in other case i must be able to add (raise) quantity cells in bank.
brief:
1- i have an original quantity in bank and other quantity in outcome factor.
2- auto subbing update in bank by outcomefactor(whit formula or vb code). if i change quantity in outcomefactor the numbers must be computed from first place(i must not loss base numbers until the table in outcomefactor is not completed or excel is not closed)
3- manually adding update in bank by hand(without any code).
4- original table is in bank and other table is in outcomefactor.

please help me
thanks

I am looking for a way to automate the goal seek tool in excell or write a formula to find the "Quantity 3" below.

I have a column of 100 items that are variable and to calculate them one at a time is way to slow

The feilds I am dealing with are

"Quantity 1" = $6760
"Sell Price 1" =$1,882

"Quantity 2" = $27,038
Sell Price 2 = $2,698

Goal Unit price =.10

"Quantity 3"= Goal Quantity that needs to be determined with a Goal seeker formula that will create a unit Price that matches a Goal Unit Price

"Sell Price 3"= Formula that interpolates from "Quantity 1", "Sell Price 1" and "Quantity 2", "Sell Price 2" using Qty 3 as its variable

"Unit price 3" = Formula = Sell Price 3/Quantity 3 (This needs to match the Goal Unit price

This is my 1st post, Thank you for any help

Jim


I have 3 columns:
in first cell (A1) i have a code: 553482300040
in second cell (B1) quantity: 1000
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.

What i have to do is to sum the quantity of the products in third cell, row 1 (A3).
Can someone help me?

Thank you!