SUM Spread / Distribute Formula?Dec 29, 2009
I've enclosed a sample summary page to see how I'm going to use the formula.
I've enclosed a sample summary page to see how I'm going to use the formula.
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.
See attached. (I added in some arrows for the first month so it is easier to follow the logic)
I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.
Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.
Essentially, I need the formula to return the correct amount that is being paid, even across different years.
I don't know if my formula can be modified or a new formula would be more efficient.
(This post originated from Commercial Services, which I just requested be removed from that forum)
On the attached spread sheet here is what I am trying to do. In B2 I will either enter D (digital label) or F (Flexo label). With that than if a D is entered nothing else will happen with in spread sheet but if F is entered D23:Q40 will need to be cleared out and blank.View 5 Replies View Related
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
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?View 7 Replies View Related
I been working on excel sheet that will distribute 200 items between the employees that are available to work. I've attached a copy of the workbook. First I need to look at the quantity of employees I have working that day. Second Distribute the items to each one of the employees. Third give the first employee the first piece. Fourth give the next available piece to the last person with a zero piece in their list.
I organize monthly audits of work completed by the team. Essentially we all audit each others work; but we cannot audit our own work.
I would like a formula that will evenly distribute the jobs for audit without passing a job back to someone that owns the job originally. e.g. Gareth cannot audit a job Gareth owns, but can audit Zach or Claire's jobs. The Excel sheet attached should make it easy to understand.
So far I have tried: =INDEX(A2:A24,RANDBETWEEN(2,24)) but this duplicates data and I cannot get it to evenly spread the jobs amongst the team.
I have a workbook that needs to fill the column B (if the column A is populated) with Column C if the column D is "YEs" once i clicked the command button. the distribution should be even. i tried rand() but its not distributively well, so i'm thinking to work it with macro.View 10 Replies View Related
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.
I am trying to distribute the total number of sessions (cell H1) across the groups in column A based on the score of each group (column D). The number of sessions a group receives cannot exceed the max (cell H2) or be less than the min (cell h3).View 7 Replies View Related
I need to create variables that splits out a length of stay across years and enters the number of days in each of those years into separate year variables. Below are two sample cases:
CaseID StartDate EndDate
222 12/23/1988 01/01/1997
After creating different variables for the different years, in this case: 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, I want to be able to pull out the number of days that goes into those years as separate variables.
caseID daysin1988 daysin1989 daysin1990 daysin1991 . . . . 1997
222 9 365.25 365.25 365.25 0
and so on
Attached a work tracker here for your ref.
I this, We are distributing work on 6 hrs/day basis. What I need is, I need to automate the work distribution on daily basis and should show the hrs also. and the projected date of completion. (yellow highlighted cells are where the formulas are needed)
How can we do that? Excel Help-Updated.xlsx
Our Favorite Gifts.xlsx
This is a simplification of what I really need. But say I have 5 people, and I have 5 gifts (A-E) Each person is in order by who gets first choice, second, etc. And each person has indicated from 1 - 5 which gift they like best. 1=most favorite 5=least favorite
I'm trying to create formulas to show who gets what. My actual spreadsheet has more people/items but is virtually the same thing.
In the attached example,the first person, Bill, gets first choice, then Mary, then Karl etc.
Bill would get Gift C. Mary would get gift A Karl would get Gift D because gift C and A are already taken.
The spreadsheet itself could be done differently if there's a easier/better way to get the data.
I'm working on a project that will will have varying tasks & estimating the total duration to complete these tasks.
If estimating that the min-project will take 90 days & I have 17 tasks, how can I distribute the 90 days evenly amongst the 17 tasks?
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.
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.
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.
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.View 5 Replies View Related
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.
How to achieve the same as in the linked thread below, but in weeks instead of months? I'm very insecure about how excel deals with weeks.
I have to create VBA according to these criterias. I have a list of 22 rates that i need to distribute by order for a month period (30 days) noting that I need to skip weekends (Friday and Saturday)
create this formula so that each time I change the date the rates are distributes accordingly
I would like to distribute values in cells depending on the input value (excel file attached). This file is a test and basically i've been entering the data manualy. The format is flexible, so it can be reorganisaed.View 2 Replies View Related
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.
i'm trying to find a way to distribute days per month between 2 dates. I have found a great exemple that should to de trick but there is still a problem left in it. it gives in the next year (this case '08) an +31 value and a negative valueView 4 Replies View Related
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.
Is there a possibility that there is a macro in the workbook even when there is no worksheet? I do not want a personal macro, instead, when I open a workbook(with no worksheet), goto tools->macro->open, I should see the macro. This would be helpful so that I can passover the macro to all my team mates and they can open any worksheet from there and work on the macro.View 2 Replies View Related
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.
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.
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
I have a spreadsheet with names on and I need to check to see if the surname in column C, also appears in column A&B.
I would like to list, in another sheet, the names that are in Column C that are NOT in A&B. Then I can see what data is missing.
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
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.
I was trying to compare string from excel spread sheet to a value I am not getting any output. It is not workingView 4 Replies View Related
I have a workbook with two sheets, and I am having difficulty in finding how to match up the data.
Sheet 1 (named "TS") consists of several columns, but it's always a series of date columns followed by multiple site numbers.
Sheet 2 (named "Dates") consists of just two columns, a site ID and a Date column.
I am having difficulty in finding how to lookup/match the date that goes with the store number, as the information in Sheet 1 (TS) is laid out in a horizontal fashion.
So, for example:
Sites: 14352, 14425, 11661, 404, 8142, 3571 should all have the date of 5/17/2014.
As such, on Sheet 2 (Dates), whenever those site IDs are encountered, the date of 5/17/2014 would need to be applied in col B.
Similarly, on Sheet 2 (Dates), for site IDs 1201, 3613, 9316, 9735, 13211, 7640, the date of 6/21/2014 would need to be applied in col B.
Here is a link to a sample workbook: [URL] ........
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...
Is there a way to lock out a spread sheet if it is sent to an unauthorized e-mail address or forwarded to another e-mail address?View 9 Replies View Related
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?
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?
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.
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,View 6 Replies View Related