SUM Spread / Distribute Formula?
I've enclosed a sample summary page to see how I'm going to use the formula.
View Complete Thread with Replies
Related Forum Messages:
Formula To Distribute A Value Along A Bell Curve.
I'm trying to find a formula that would spread out a value amongst multiple cells along a bell curve. Say I have $x,xxx. I want to spread that amount out in a certain proportion, regardless of how many cells I'm spreading it throughout.
Say I have a $10,000 project. I want to be able to spread it out like a bell curve in the cells I choose. For instance, if it was a five month project it would be distributed as
January - $1,000
February - $2,500
March - $3,000
April - $2,500
May - $1,000
If I added another month it would change to
January - $833.33
February - $1,666.66
March - $2,500.00
April - $2,500.00
May - $1,666.66
June - $833.33
I already thought of manually adding percentages to each cell, but the formula needs to be dynamic.
Distribute Macro To Others
I have created a Macro and would like to make it available to other members of my team. This Macro is to be used in various different workbooks - so it cannot be tied to a single specific workbook. I tried creating an Add-In, but I've learned that, even if I instruct my colleagues how to load the Add-In, they will not be able to see the Macro that is inside it. So what methods are recommended for distributing Macro's to other users?
Conditional Formula: If Text, Repeat As Number To Sum, Else Sum Numbers.
I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))
How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).
Evenly Distribute Hours Over Month
I am currently tracking our company's service tasks in Excel and would like a formula or method that will take the total hours a task has been approved for and equally distribute the hours into columns under each month.
Column A = Resource
Column B = Task Owner
Column C = Task Name
Column D = Task Description
Column E = Approved Hours
Column F = Start Date
Column G = End Date
Column H = Jan 07
Column I = Feb 07
Column J = March 07 etc. etc.
I basically want to take the approved hours (Column E) and have those hours automatically calculated and filled into the month columns (Column H forward), as per the start and end date fields (Column F & G) so the hours are evenly distributed over each month.
Distribute Data Columns To Rows
I have searched and have not been able to find exactly what I need. I currently do this manually and it is taking too much time.
I start with a file (sheet1) that has data in column A for a location.
Column B has a unique 10 number (each location can have between 1 and 10000). Example:
I need to get this data into the following format for a mail merge:
I was thinking I would set up a sheet2 with all the unique location codes, and then do a match between the location code on sheet 2 and sheet1 and use an offset function?
But I am way over my head when I work with vba.
Distribute Across Columns Based On Number
I have a list of parts with quantities that i would like to distribute among several columns that represent cities, based on the number of parts in stock. For Example, if i have 5 parts and ten cities, i want it to put one part in the first five. In the case where theres more parts than cities i want it to loop back to the first city and add one and so on. I attached a sample spreadsheet.
Distribute Workbook With Calendar Control
I would like to distribute a workbook that makes use of the calendar control in a userform.
Trouble is.... Not all the end users have the control installed or even a copy of the mscal.osc file.
This workbook is going to be rolled out to a large number of users with varying levels of computer literacy. For this reason I would like to try to 'attach' the calendar control to the workbook OR somehow distribute the mscal.osc fil and automate the installation.
Distribute Quantities From Known Average Price
I'm having some problems to find a way to distribute quantities from a known average price. Here's the Example:
My Client bought 100.000 Shares from a Stock, in various prices.
Then the client wants to distribute this trades for 7 different funds at the same average price.
Qtty Fund 1: 294
Qtty Fund 2: 541
Qtty Fund 3: 1.213
Qtty Fund 4: 13.370
Qtty Fund 5: 16.582
Qtty Fund 6: 28.002
Qtty Fund 7: 39.998
I'm sending an example attached.
Password Protection & Distribute
Is there a tool that can make an Excel application behave like a “real” software? Meaning, the user will still be able to do what I planned for him to do with the file, BUT it will be very difficult for the more-than-average user to “crack” any password protections I have applied to the file? (Something like an MDE file in Access, perhaps?) The reason I am asking is because I have invested a lot of time and efforts into creating an application that I wish to sell to some of my clients, and don’t want them to easily find their way into the formulas and macros I have placed in the file.
The protection tool does not have to be an Excel tool, necessarily, but it should not require any programming skills (because I don’t have them. If there isn’t such a tool? My application contains User Forms (for input), a Database (with Auto Filters and formulas) and some Pivot Table reports. BTW, my application was created in Excel 2003 but should also be used by users with Excel 2007.
Distribute Data From One Main Worksheet To Several Other Worksheets
I am in need of an Excel Macro to help me create an address list by market. For simplicity sake I’ll set up a mini-example: I have an Excel document with a MASTER tab that lists the Market (Column A), a person’s name (Column B) and their Phone number (Column C). What I need to macro to do is read column A and make a new tab for each market in that column, then populate the name and phone fields accordingly.
A found one macro that was close, but it does not automatically make a new tab (I have to do that manually) and it does not refresh the sheet each time I open it, so when I run the macro again I get duplicate entries.
Distribute Data In Single Sheet To Multiple Sheets
The worksheet is formatted as follows: It contains data in columns A-L. I want to breakdown the data further into sheets according to the data that is found in column G. As an example, say I have 1,000 rows of data and in column G there are a number of different branch numbers in each row. So there may be 250 rows with branch 450, another 250 rows with branch 360, another 200 rows with branch 777, and finally 200 rows with branch 888. In this case I want to create 4 new sheets (450, 360, 777, and 888) and copy only the data in the main sheet that corresponds to same branch.
Actions would be as follows:
copy 250 rows to tab "450"
copy 250 rows to tab "360"
copy 200 rows to tab "777"
copy 300 rows to tab "888"
Any comments are much appreciate. I have attached a data sample along with desired output, if needed. One note: the data comes in each day as one single sheet of data and the number of branches varies day-to-day, as such, code would need to create new sheets for each branch found.
Sum Formula: Sum Of The Numbers In Column
I would like a formula for cells B1, B2, & B3 that will give me the sum of the numbers in Column B which are next to their corresponding plan in Column A. The attachment is a sample of a sheet that will be having approx 500 rows to work with. I have tried using the "Sumif" funtion, but I can't seem to get it to work correctly.
Spread Of Consumption
writing a formula or creating a macro
The first step will be to get the formula or macro to fill in the cells under the date columns that will show how the consumption of the 555 is assessed to be spread out over the period. The formula should portray an equal distribution of the consumption over the period.
Revenue Spread Over Months
I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.
I have attached a snapshot of what I am trying to do.
Spread Sheets Vlookup
i have to make it to where when i put a number in it also puts in the description and
unit price just by the number i put in to a cell. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong
Automate A Spread Sheet
I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.
Sheet 1, has a list of stocks, indexed in Column A.
Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.
I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size
Summarize Data Spread Across Workheets
Is it possible to consolidate data from various worksheets into a single worksheet using multi reference points or lookup_values?
For example, I have 2 worksheets named Section A and Section B in which I have detailed monthly expenditure details (e.g. overtime, allowance, uniform, etc) for each sections, respectively.
Then in the 3rd worksheet, called Combined, I have 2 columns - first is 'MONTH' and second, 'SECTION'.
Now, suppose I have a 3rd column in which I want to pull over the overtime expenses for both sections. That means, my VLOOKUP must first look for the SECTION worksheet, then find the MONTH before returning the value found in the overtime column.
How can I do this?
Spread Values When Criteria Met
I have worksheet where I would like to apply a number value proportionately throughout a column bases on user-defined conditions (i.e. >=, <=, Between) from other colunmns. See example below and attached worksheet with further detail.
Col A Col B Col G Col L Col M
01380Employee 15.0 5.5 (0.8)
00304Employee 24.0 - (0.8)
01901Employee 33.8 (1.0) (0.8)
01729Employee 43.0 - (0.8)
01482Employee 53.0 (1.1) (0.8)
00299Employee 62.5 - -
00069Employee 72.5 0.5 -
01783Employee 81.5 - -
02076Employee 91.0 - -
01934Employee 100.5 - -
TOTAL 4.0 -4.0
In the above example, I would like to have the TOTAL value of Column L (4.0) be inversely spread throughout column M based on criteria selected by a user in a section of the spreadsheet.
Column G >= 3 AND all values in Column L = 0; Spread -4.0 (Inverse of cell L12 = 4) in column M for records that match criteria.
I would like to have a command button apply the logic of the conditional criteria set for Columns G & L and spread proportionately ithe value from n Column M.
I added examples of the different criteria and the layout in the file attached.
Report Data Spread Over Many Worksheets
im creating a spreadsheet at work that gathers project and FTE figures.
I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.
Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.
Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".
can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.
Each Spread Sheet Is A Seperate File
I have a few hundred work orders done in Excel. Each spread sheet is a seperate file but all are identical in structure (basic order form). I need to change the value in the same cell on all the forms (manager's name needs to be changed on all our forms).
Question: How do I do this without having to open and edit all five thousand forms?
Sort Data Spread Over Two Sheets
I have two sheets within one workbook, mean I have 300 columns, as u know excel sheet is limited to 256 columns, so I divided the columns in 2nd sheet, now I want to sort the data, but when I sort the first sheet data, the second sheet data not sorted,
Large Spread Sheet Reduction/creation
I am currently working on a very large spreadsheet with a current size of 94mb. So obviously I have problems running and using it.
My question is a general one, I need to understand better why a spreadsheet gets so large. It has 55 tabs with the largest having 1000 rows. It also links in a number of places between sheets but also to external locations. I have moved all files that it is linked to in to the same file.
I am at the point where I am thinking about starting again! And I was wanting some tips or even a recommendation of a book that could help me optimize my spreadsheet going forward so to increase the speed but also reduce the size.
Returning New File Names To Spread Sheet
Public Sub ListMyFiles()
Dim fso As New FileSystemObject
Dim fso_Folder As Folder
Dim fso_File As File
Dim file_count As Long
Set fso_Folder = fso.GetFolder("S:SHARED All of TransportationTraffic AssessmentMultiway Warrants")
file_count = 1
For Each fso_File In fso_Folder.Files
file_count = file_count + 1
Cells(file_count, 1).Value = fso_File.Name
Set fso = Nothing
This works great, but is there a way for the macro to recognize the file names that have already been added to the spread sheet and only return new ones that have been added to the folder?
Averaging Times In Google Spread Sheet
I would like to calculate the average number of hours worked per day in a particular week from my time sheet (in "Google spread sheets"). Daily hours are calculated in hr:min:sec format. Days not worked display 0:00:00. The formula I use to calculate total hours worked that weeK is for row 9 as follows: =INT( SUM(G9,K9,O9,S9,W9) )*24+LEFT(TEXT( SUM(G9,K9,O9,S9,W9) ,"HH:mm"),2)&TEXT( SUM(G9,K9,O9,S9,W9) , ":mm:ss")
How do I calculate the average number of hrs and minutes worked per day (excluding days not worked, cells containing 0:00:00)
Importing Data From A Website To Spread Sheet
Im trying to import data from a plumbing supply website to an excel spread sheet.
On the supply house web site, for each part catgory there are corresponding pages containing a price list data table.
there are from 1 to 3 pages (price list data tables) for each part category.
i can import data from page 1 and page 3 fine. When i try to import data form page 2, it returns no data, or imports page 3 (instead of 2)
I spoke to the person who designed the website and he said that it was a problem within excel and not a problem with his program. (said it was a problem with excel not being able to recognize the page)
How To Spread Existing Data Evenly Throughout The Same Row
I don't know how else to ask this so I will just tell you what I'm trying to do.
-I have a list of name on one column say "A"
and this list of names goes through "A1" through "A145"
-I am taking those names and pasting them via "right click" "paste special"
"Values" onto another spreadsheet.
-BUT I have to take each name one by one and paste them evenly
in every 8th row for example ( "A1" A8" "A16" "A24" ...)
-Is there a way to take the existing data on that row and just spread it evenly throughout the same row? So I don't have to take names one by one by one...
Calculate Hours Worked When Times Are Spread
I got a problem with a time calculation, if it's even possible.
I want to calculate the amount of hours that have been woork in a slot.
First slot is from 08:00am till 17:00pm, time cost 100%
Second slot is from 1700am till 24:00, time cost 110%
Third slot is from 24:00 till 08:00 am, time cost 120%
I'm working from 08:00am till next day 01:00am
So my hours are:
9 in slot 1,
7 in slot 2,
1 in slot 3.
Is this possible to calculate in Excel?
I got a file attached with a sort of layout.
Pivot Table: Data Spread Across Different Worksheets
I have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?
Unmerge And Spread Merge Data To All Cells
In the attached document, there are some cells which have been merged. For instance the cells A4 thru A7 were merged as one field. if you examine B4 which is actually made up of B4 thru B7 the data contained therein is 123. Is there a way to unmerge these merged cells and have the value (in this case 123) appear in each line of the unmerged cells? If you right click and click unmerge, it unmerges okay but does not populate each line with the data which was on the original field.
Projection Spread Sheet For Cash Flow Purposes
I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
Spread Annual Number By Month With Varying Inputs
I'm almost done with the budget at work and now I need to spread the annual figure by month. Initially I was thinking of spreading it evenly by 12 months, i.e. $12k annual budget = $1k per month.
However the boss doesn't want it that way. So I received inputs from certain managers as to when the bulk of the budget will be spent. Now I need to develop some type of formula so that once I input that amount let's say in June, then the rest of the month's will adjust accordingly.
For example, using the example above, if I manually input $6k in June, then the remaining $6k from the budget will be spread out evenly through the other 11 months. Is there some clever way to do this or is just a matter of multiple IF statements?
Macro For Filtering Spread Sheet By Date And Specific Person
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
Macro- Large Amount Of Data In A Single Spread Sheet
I have a large amount of data in a single spread sheet. Each row has a branch number on it, there are multiple branch numbers. Each branch number is located in Column A. I want to separate this sheet and put all of the branch numbers into their own sheet. how to do this without manually copying and pasting?
Lookup Row And Column As Criteria But Data Is Spread Across Columns
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
Running Multiple In VBA Instances: Controlling Spread Sheet Always Waits For The Code To Return Before It Continues
i have a spread sheet which has a very complex simulaton in it. excel is not the ideal place for it, but it is not possible to port it wholesale to something more sensible. i have modified the code so that it is possible to run two copies of the spread sheet (in two seperate excel processes) at one time. Due to the dual core nature of the machine this has almost no overhead.
I have created a controlling spread sheet that is cabable of launching multiple copies of the simulation and starting the code executing. the problem is the controlling spread sheet always waits for the code to return before it continues, hence it would open two seperate versions of excel, start the first simulation executing and then wait until it completes before starting the next one. is there any way of starting a function in another work book without waiting for the execution to complete?