SUM Spread / Distribute Formula?

Dec 29, 2009

I've enclosed a sample summary page to see how I'm going to use the formula.



ADVERTISEMENT

Formula To Distribute A Value Along A Bell Curve.

Nov 19, 2008

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.

View 5 Replies View Related

Formula To Spread Costs Between Months And Years?

Dec 3, 2013

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)

View 3 Replies View Related

Formula To Clear Out Cells With In A Spread Sheet If Certain Input Entered.

Jun 23, 2009

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

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

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.

Details:
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 ...

View 4 Replies View Related

Distribute Macro To Others

Feb 28, 2008

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

How To Distribute 200 Items Into 14 Employees

May 19, 2014

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.

book8.zip‎

View 12 Replies View Related

Distribute Data Without Repetition

Feb 17, 2014

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.

View 3 Replies View Related

Distribute ID Based On Criteria?

Mar 10, 2014

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

Password Protection & Distribute

Dec 15, 2009

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.

View 2 Replies View Related

Distribute Number Based On Score?

May 14, 2014

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

Formulas That Distribute Days Across Years?

May 16, 2014

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
22112/25/1989 2/12/1990

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.

Thus

caseID daysin1988 daysin1989 daysin1990 daysin1991 . . . . 1997
222 9 365.25 365.25 365.25 0
221 742

and so on

View 3 Replies View Related

Distribute Work On Daily Basis

Jun 16, 2014

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

View 5 Replies View Related

Formulas To Distribute Ranked Items?

Jan 7, 2014

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.

View 10 Replies View Related

Distribute Duration Evenly Amongst Tasks?

Sep 10, 2013

I'm working on a project that will will have varying tasks & estimating the total duration to complete these tasks.

For example:

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?

View 5 Replies View Related

Distribute Data Columns To Rows

Feb 15, 2010

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:

Location,secnumb
1000001,10052950
1000001,10052960
1000002,10052980
1000002,10052990
1000002,10053000
1000002,10053010
1000002,10053020
1000004,10053530
1000004,10053540
1000004,10053550

I need to get this data into the following format for a mail merge:
Location,secnumb1,secnumb2,secnumb3,secnumb4,secnumb5
1000001,10052950,10052960
1000002,10052980,10052990,10053000,10053010,10053020
1000004,10053530,10053540,10053550

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.

View 9 Replies View Related

Evenly Distribute Hours Over Month

Dec 9, 2006

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.

View 9 Replies View Related

Distribute Quantities From Known Average Price

Dec 10, 2006

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.

Qtty Price
10041,71
30041,61
70041,78
1.50041,72
4.40041,68
5.50041,75
10.00041,94
13.50041,70
64.00041,80

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.

View 4 Replies View Related

Distribute Across Columns Based On Number

Dec 20, 2006

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

Distribute Workbook With Calendar Control

May 16, 2008

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.

View 4 Replies View Related

Distribute Project Hours Automatically Across Weeks

Mar 27, 2013

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.

[URL] .....

View 1 Replies View Related

Create VBA To Distribute Rates And Exclude Weekend

Jun 24, 2014

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)

Day 123.06.2014Monday20%
Day 224.06.2014Tuesday10%
Day 325.06.2014Wednesday7%

[Code]....

create this formula so that each time I change the date the rates are distributes accordingly

View 4 Replies View Related

Distribute Values To Cells Depending On The Input Value

Feb 25, 2009

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

Distribute Data From One Main Worksheet To Several Other Worksheets

Jan 6, 2010

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.

View 13 Replies View Related

Distribute Days Per Month Between Date Range

Aug 31, 2007

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 value

View 4 Replies View Related

Distribute Data In Single Sheet To Multiple Sheets

Sep 23, 2009

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.

View 5 Replies View Related

Distribute Macros: Is Possibility That There Is A Macro In The Workbook Even When There Is No Worksheet

Dec 13, 2006

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

Spread Of Consumption

Nov 6, 2008

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.

******** ******************** ************************************************************************>Microsoft Excel - claimv1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2=BCDEFGHIJKLM2Start DateEnd DateTotal consumptionLast week day14-dec21-dec28-dec04-jan11-jan18-jan25-jan01-feb317-dec24-jan555Assesment        4   Actual        Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Revenue Spread Over Months

Apr 21, 2009

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.

View 9 Replies View Related

Spread Sheets Vlookup

Apr 30, 2006

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

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved