I need a formula that will tell me what % of the first 5 units sold are new, based on the dates provided. This is an example of the data I am working with. What i am looking for is a cell that will respond with .4 (meaning 40% of the first 5 units are new) ...
View 9 RepliesI have three columns. Lets assume i have One header row and 3 rows of actual data and the 4th row is for totaling column B values.
Column A is a col of "Ingredients", column B is the amount of the ingredient (from col A) used. The value is either in Oz or Lbs. Can be either. Column C is a yes or no column. If the value is Yes then i want to have the value in col-B included in my overall sum which is in the 4th row. If the value is No then i do not want the value in col-b (in that row) included in the overall sum.
Any ideas on how to do this?
Thanks.
david
I have the following problem -I want the cell with "SUM OF SPE HOURS (C1)" to contain the numerical value equal to the SUM of numbers in column C ONLY IF column A of the corresponding row contains "SPE". Basically a SUM function which only picks up the rows corresponding to SPE.
A
B
C
D
E
SUM OF SPE HOURS i.e. 6
SPE
4
LDE
8
SD
10
SPE
2
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.
View 9 Replies View RelatedI have a 7000 row spreadsheet of items sold over the past 6 months. I sorted them by their SKU, but some of them sold in multiple qty in a given day.
What is a formula to calculate the total qty of each SKU sold?
Example:
Column A = SKU 001
Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
Column C, (Row 10) = Total where I would like to come up with a number for columns 1-10 (for example).
next item:
Column A = SKU 002
Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
Column C, (Row 17) = Total where I would like to come up with a number for columns 11-17 (for example).
I'm trying to write a formula to count under certain conditions. I tried the following (and some other variations), but it doesn't work, it comes back #VALUE!:
=COUNTIFS('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100,D7,'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A:$A,A34)
I need to count the number of products a rep sold in one day. He can have several sales in one day, and also sell more than one product per sale. The list I am pulling from has all sales from all reps for the day.
Find attached , expected result in on w2 needs to be 9 that I have punched manually : Team(A)01.xls‎
View 14 Replies View RelatedI have a one year sheet for a customer. Column A has a 6 digit part number, Column B has the quantity of that part number and Column C has the date they of the invoice. There could be several rows with the same part number (say, if they purchase them monthly, or weekly they would have 12 or 52 rows, etc.). If I want to find out how many of part # 123456 have been sold all year, is there a formula I could use in Column D?
View 8 Replies View RelatedI have created a spreadsheet as follows:
Date revenue total tickets sold total tickets sold per day
10-1 166,453 15374
10-2 166,915 15414
How do I formulate so that the numbers automatically update per day
I have a table and I want to find out the what is max number for Apple sold on Monday.
FruitsQtyDaysApple20SaturdayApple50SundayApple100MondayApple500MondayApple20
MondayMango50MondayBanana640FridayMango30FridayGet MAX ForApples on Monday500
In table A i have the purchase date of all the articles bought with their corresponding quantities.
Table A:
Purches DateArticleQuantity12/12/2008Radio2012/12/2008Binocular6012/12/2008Fan3112/12/2008Iron5012/12/2008Sun Glass15012/12/2008Tv612/12/2008Head Phone3712/12/20082G Card12
In table B i have the sold dates of all the articles with their corresponding quantities.
Table B:
Sold On ArticleQuantity15/12/2008Iron115/12/20082G Card115/12/2008Radio216/12/2008Tv116/12/2008Iron217/01/2008Head Phone317/12/2008Iron117/12/20082G Card122/12/2008Radio127/12/2008Fan1
An finally table C is a result chart which shows the days between the purchase and the selling dates of the articles.
Table C:
Article Sale After purches date 12/12/2008 Days ?Sale RepeatRadioBinocularFanIronSun GlassTvHead Phone2G CardAfter Every ? Days3163453After Every ? Days1145After Every ? Days5After Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? Days
A worksheet called "purchases" contains the list of shares purchased during the period 1.4.5 to 31.3.8. It has four columns :
1. Date
2. Name of the share (name is repeated for every new purchase as it is entered in a new row and is totally independent of the earlier entry of purchase)
3. Qty
4. Sale price
Likewise there is a sheet for sales also. I want to do the following:
a) Find out all the records showing purchases of a particular share and add them up (both quantity and value). And thus, make a list of shares showing aggregate purchase of each share during the entire year. Likewise, I want to do this for sales also.
b) For every share that has been sold within one year after its purchase, apply 20% tax on the profit earned on sale of such share.
Additional information: If 100 shares of A Ltd are purchased on 1.4.5 and 200 shares on 1.5.5 then during the sale of 150 shares of A Ltd on 2.4.6, 100 shares purchased on 1.4.5 and 50 shares purchased on 1.5.5 shall be deemed to have been sold.
I have a table with sales teams down the left, and products along the top.
The table shows how many sales of each product a team has made. This is populated from a sales data dump each day.
I need a second table with all the products along the top, and beneath each product, a list of which Teams have sold that product.
do I need some Index / Match style formula here to pickup up each team from the left in the first table that's got a sales value higher than 0 for a particular product?
I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.
Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.
I need to work out how long the batten has to be so the roof sheets fit evenly, the measurement has to start from 1460mm and go up in increments of 80mm eg 1540mm, 1620mm, 1700mm and so on.
But the number has be closest increment of 80mm over the shed width if this makes sense, the size of the battens for 2400 width shed would be 2420mm but i need this to work out for any width shed not just 2400.
We have 5 - six month programs and five - 12 month programs we are going to sell; each have a different price. I Want to insert number of projected sales manually for each month and then have excel distribute/spread revenue evenly over six or twelve months into a separate set of cells. First payment would be received at time of sale.
View 4 Replies View RelatedI have a very long Excel spreadsheet (>2500 rows by approx 20 columns) that consists of >400 customer statements. At the top of each invoice, in colun B, is the text "Service Requestor Contract Number", and 5 rows down is the respective contract number. Further down (and the number of rows differ for each contract as each has its own unique level of detail) , again in column B is the text "Current Month Charges", and on the same row, in columns K and O, the current month number of items sold and $ charges respectfully. The spreadsheet is NOT paginated for each respective statement.
What I need to do on a separate spreadsheet is sum, by contract, both the number of widgets sold, as well as the amounts due.
What I am trying to do is take entered data in a spread sheet, and convert the units within that cells.
I have several entries for data in this sheet. Here is what I want to do:
-I have 104 entered in cell B2
-I want to convert that reading (which is ºF) to ºC in that same cell
-I would like a button or selection from a drop down box to select Metric or Imperial
I want to do this so if the whole spread sheet is entered in metric, I can select a name or click a button to have all the data switch to imperial. I know I can do this with two sheets in the same file, but I am not sure if this is possible to do in a cell I entered the data in. Any help would be great. I am using Office 2003.
I'm wondering how to display the following cost model on a graph (this is a piecewise-linear cost model for some production company):
Cost per unit for 0-10 units: $1
Cost per unit for 11-20 units: $3
Cost per unit for 21-30 units: $5
Cost per unit for 31-40 units: $8
Fixed cost: $100 (this is regardless of the number of units produced)
Basically I'm looking to construct a cost vs units graph.
I have been tasked with tracking the expected income for our Advertising Department. (I work for a newspaper.) The rate I charge an ad depends upon the total number of advertising units a customer purchases during an entire billing period. Essentially: I need to have a total of column G for when column B matches the current row. I need this in column K. The value of K should be the same when the value of B is the same.
My programing flow chart I sketched out checked to see if any row in column B that is above the current row matched the current row's data. If it did then it would use that row's value for the total number of units. If it did not it would add up the units from G when B matched the current column. I attached a document
I have a spreadsheet that keeps attendance. Now I need to calculate the
units (1 unit= 15 minutes) the children are actual here. On a normal
schedule they are here for 16 units but if the are late the units get
subtracted. When entering the data, they would like to use letter instead of
numbers.
Refer to the attached sample workbook,
(Keep in mind that there is an error in the sample workbook; Im not sure how to remove and re-attach the sample workbook as this is my first day on this forum.)
Cell F19 should contain =D19*B19
Column 1- These values will not change and represent price/cost of each UNIT
Column 2- "UNITS" the quantity or number of items at column1 value
Column 3- "$ AMT" the total cost of UNITS at that price/cost, i.e. Column1*Column2
Im looking for a way populate the values in Column2 when, "NUMBER OF UNITS" AND "AVERAGE COST PER UNIT" are changed
Ideally speaking, As few units in one row as possible to allow for a good mix.
For example: What i don't want is $9000*86 for 774k.
If it helps: "AVERAGE COST PER UNIT" will always be a whole number rounded up to the nearest 1000.
I believe a Most Even distribution of units will work fine.
After the distribution/population of fields in column2;
D29 must equal F3
F29 must equal F7
I'm looking for something that will populate the values in D12:D27 while adhering to the two conditions above. Even when the Input at F3 and F4 are changed.
The values in "UNITS" represent the number of a specific cost item in inventory when added together do not exceed the "TOTAL COST OF INVENTORY". Depending on store location, the "AVERAGE COST PER UNIT" and/or the "NUMBER OF UNITS" will change. It will be used to help forecast how many of a specific cost item should be purchased based on the overall average unit cost without exceeding the budget or inventory space.
i have been trying to write an Excel sheet in 2007
that can be given a random amount of (money) and show
me the best way to spend it.
Weapons:
Name - Power - Cost
1 - 10 - 20
2 - 20 - 40
3 - 40 - 80
4 - 80 - 160
5 - 160 - 320
6 - 320 - 640
So i have 40K to spend, i want to eneter 40000 into a box and it
will auto populate the number of units i can buy, always setting the weapon
#6 as the priority.
I have lists of room areas in metres squared, I extract the web based lists to excel: for example.
10.56 m2
14.23 m2
21.34 m2
How do i calculate the total of these amounts?
Is there a formular to remove the "m2"?
I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0.
If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together.
Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered.
In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4.
Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?
I have a column named length and values are in different units ie few values in mm, few in cm, few in m. i have to filter out columns having range from 9mm to 6cm. How to do this task.(10mm = 1cm and 100cm = 1m)
Consider sample data as:
4 m
8 cm
9 m
3 mm
9 mm
6 m
6 cm
3 cm
2 m
I'm trying to set up a spreadsheet that tracks total hours worked and total
units produced. Then I need to have a column that shows how many units per
hour were produced.
Currently, I have something like this:
Column A is in elapsed time [h]:mm
Column B is a Number with two decimal places
Column C divides Column B by Column A
However, I get strange results. For example:
Column A is 6:24:00
Column B is 13
Column C shows 120.00
13 parts in 6:24 hours should be something like 2.1666 parts per hour!
Can split a single cell within excel to two or three units of the single cell?
View 2 Replies View Relatedi need a formula that will return the total value of units which is greater than 20000 in col b between 5 and 30 days
AgeUnits272,472,215.29134,354.83112,714,300.007-112,566.006-239,853.006-105,938.006-36,792.006-608,695.001827,632.007-21.00
I have a formula to add the volume (units) for a customer. The formula is:
=E5+E10+E21+E31+E38+E52+E65+E69
Is there a formula that I can use instead of the one above that will find the customer number and automatically sum the units?