AMOUNT 42400 is distributed among 6 people (C) in percentage if any of them or any 2 of them is deleted their contribution percentage need to be distributed among other.
I am attaching the example sheet... EXAMPLE.xlsx‎
I have a task that I thought would be quite simple but I can’t find a solution in any of the books I have or on this forum.
I receive a list of companies each day who owe money, in Worksheet1. The next day I receive an updated list in Worksheet2. Those companies who have now paid have been deleted from the list. I want to compare the two lists and highlight those companies in Worksheet1 who are no longer in Worksheet 2.
I’ve tried to do this by getting the values in the list in Worksheet2 to loop through the list in Worksheet1 but can’t make it happen.
I’d be really grateful if someone was able to help me with the code I need.
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total
1234Bed150
5678Bed100
1547mattress52
58658mattress188
12356bed12
8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I'll use following as an example.
I work for a supermarket, I receive many fruits per day and I want to see what percentage of them are bananas, apples, oranges, watermelons etc.
Are there any templates of pie charts and bar graphs that I can use for this that will show the percentage of said fruits daily, weekly and monthly?
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
I know EXCEL has functions for both the NORMAL DISTRIBUTION and the
cumulative value thereof...
but is it possible to graph this data..
(I imagine if I sort the data and then calculate the ***. distributions into
a column...then chart it, it should work; BUT IS THERE ANOTHER WAY?)
I need a formula that distributes a % based on position.
The positions are 1 through 20, and each one has a corresponding % value ...
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
I have been working on a problem that requires being able to calculate the chances of any winnng a series of coin tosses with a weighted coin.
The coin has for arguments sake a 65% / 35% chance of landing heads or tails and to win the game you need to hit your side 9 times.
Can you use excel to calculate things like: If heads takes the lead 1-0, what does that mean the its overall percentage chance of winning? If the score is 7-3 to tails, what are the chances of either side gaining victory? etc, etc.
I thought that there would be a way of setting up a spreadsheet that listed the goal (first to 9, 12, 15 or whatever) and below this the percentage chance of heads and the percentage chance of tails. Then somewhere would be a box to enter the current score (0-0, 3-5, 1-1 or whatever) and beside this the current chance of heads or tails gaining victory.
I've tried setting up something like this but am a bit of a dufus when it comes to knowing how, where and when to use the different functions of excel.
I'm looking to analyse is how long these articles take to reach maturity from their date of publication (so, when they achieve their optimum performance).
I'd then like to further analyse this across various metrics like article type/producer etc., which, I guess, would be in some kind of Pivot Table filter-type-thing.
Here's an example of some of my data:
ArticleName - PublishedDate - OctViews - NovViews - DecViews - ArticleType - Producer
Article1 - Oct-08 - 3701 - 4856 - 4561 - Food - ABCTV
Article2 - Nov-08 - 0 - 9824 - 1256 - Food - ABCTV
Article3 - Oct-08 - 1358 - 8953 - 9652 - Tech - XYZ.com
Article4 - Oct-08 - 1351 - 2354 - 3562 - Tech - ABCTV
The part I'm finding tricky is this; what we'd like to do is rather than look at a specific date (say, articles published on 09/04/08) it would be useful to combine all the dates so that the first month something is published is Month 1, regardless of the date it is actually published.
As the same article can be from the same producer but published at different times, if I could remove the "Jan-Mar = Month 1-Month 3" problem it would allow us to graph all the data into one snapshot of the publishing process and find trends. If this could then be cross examinable by the ArticleType etc. it'd be really useful.
I have a spreadsheet which has a list of staff (column B) and work categories (row 3).
Row 4 is updated each day with a count of the incoming work for each work category.
I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....
Say I measure the height of 1000 people and I have those in a column in excel. It should be a normal distribution with mean x and standard deviation y. How do I actually graph it so I can see the normal distrubution curve that looks something like this:
[url]
I tried making a scatter graph but it graphs them point by point. I want the height to be on the x axis and something like frequency on the y axis.
There is trading system which generates a winning trade per ONE stock in ONE month’s time with a probability of 5/12 (0,4166%). If the system trades 10 independent stocks the probability of any number of winning trades occurring out of all of them in ONE month is shown below:
None 0,0046
One 0,0326
Two 0,1047
Three 0,1995
Four 0,2494
Five 0,2172
Six 0,1272
Seven 0,0519
Eight 0,0139
Nine 0,0022
Ten 0,0002
I took these values from a book…I tried to reproduce these values in the spreadsheet that I attached. My table looks as follows:
0 0,0046
1 0,0033
2 0,0023
3 0,0017
4 0,0012
5 0,0008
6 0,0006..........
There are 16,777,216 cells in a spreadsheet. These cells are distributed in a spreadsheet by 256 columns and 65,536 rows.
Can I in any way distribute these cells otherwise?
Say distributing them in 512 columns and 32,768 rows?
i have some number that i want to put in a normal distribution
so.. i have age ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and have the number of times that the kid wake in the night ( 10, 12, 9,11,8,9,7,9,11) and want to do the normal distribution grafic with that .
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 need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
How to crate a chart for normal distribution for a set of datas. say marks of students/ number of students in a class
View 1 Replies View RelatedI'm trying to make a bell curve (normal gauss distribution). I have some problems regarding the probability density curve, since its values are so low. If you look column N in the attached file - I used the NORMDIST command. Values should be somewhere between 0,2 and 0,4 - but they're about 100x lower.
View 2 Replies View RelatedI need to know how to send an email from Outlook using a rather large email address list (500 addresses) that resides in Excel.
Also, I need to know how to hide the individual recipients and send it out under a title name. I want all of the recipients to see the title name only - and not each other.
I'm trying to automatize the filling of 4 different tables regarding time (hours , minute)
In other words first i need to select from about 500 products names about 180 products i need. I solved this with an automatic filter like this
I want to email with excel, and I know how to do it if I have a distribution list already set up in outlook. I want to avoid having this DL in outlook though, and instead keep a list in the actual excel workbook. I tried having the list and then combining all the cells with a semicolon inbetween the addresses into one single cell, then assigning that cell to a variable and inserting it in the cose in place of the DL name, but it doesn't work. Apparently you can't mail to mutliple address thru the VB code, either a single address or a DL. Am I incorrect in this? So I thought maybe I could somehow create the DL first using the names in the list thru code, and then using that DL name to send the workbook.
View 9 Replies View RelatedI am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):
1. Both investors receive the return of their invested capital from cash flow distributions.
1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested).
2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.
On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:
($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443
I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.
in VBA the RND function generates a random number using uniform distribution (every number is as likely as any other). Is there a function to generate a random number using normal distribution where the numbers closest to the mean are more likely to occure then numbers further away from the mean. I've created the following custom function to generate a random number between two given values using uniform distribution.
Function RandNum(Min As Double, Max As Double) As Double
RandNum = (Max - Min) * Rnd + Min
End Function
What formula should I use if I have a list of maturity dates and current investments amounts as of 06/30/07:
06/30/08 10,000
08/31/14 18,000
05/31/08 11,000
07/31/09 25,000
06/30/10 14,000
12/31/25 80,000
and the amounts need to be distributed in columns titled:
Within 1 Yr (From 7/1/07 to 6/30/08)
After 1 to 5 Yrs (From 7/1/08 to 6/30/13)
After 5 yrs (After 7/1/13)
I would like to create a random number based on that data.
View 3 Replies View RelatedI have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
I am in need of a formula to calculate the even distribution of values between two set values over a set number of columns. For example take a look at the attached screenshot. I have the numbers 2.88 and 2.44 and I need to fill in the empty cells between the two with with an even distribution of numbers in between the two (in descending order) over the 8 columns Screen-shot-2014-02-16-at-3.33.08-PM.jpg
View 1 Replies View RelatedI'm working on has me making a frequency distribution of the yields column. If you look at M6 on the spreadsheet i'm trying to use the formula:
=COUNTIF($I$6:$I$35, "<=L6")
to represent all yields that are less than or equal to 0, in the next box (M7) I want to represent all yields that are greater than 0% and less than or equal to 1%. etc until I reach 7%, however the original formula isn't working and I don't know whats wrong with it.