I have a set number that I need to allocate over a flexible period of say 5 -11 days.
I have applied the traditional split per day to each number i.e. a monday normally results in 20% of transactions, Tuesday 25% etc.
The issue comes in when I try to allocate the budget over a period longer or shorter than a week.
Therefore the budget for the 9 days is being allocated over the period of 9 days which results in a overall allocation of 150%. (duplicate mondays etc).
Is there a way to limit the entire period to a set number and have the numbers adjust themselves accordingly up or down to match it other than multiplying it by a factor?
I have the attached table of numbers and I need a formula at the end of each column to identify whether any cells in that column consecutively have numbers in them greater than zero. Ideally by a count of how many cells in the column have consecutive numbers greater than zero (so if there are three 1's in a row and then a zero and then another 2 1's I want it to count 5).Excel Help.xlsx
I can not remember how to do the following. in column N have numbers ranging from 1 to 100. I need to get numbers counted in this grouping. >50, <50 >70, <70 >85, <85. I don't even remember how to start. I tried
I am inserting data into my spreadsheet using VBA code to read a file and insert the data into the relevant cells. My problem arises when I have a string such as 80830410205724044. The actual code that inserts the data is as shown (temp is dimmed as string)....
So no data in first 10 rows of Col B. (Or Col C, of course.) Then for the value that appears in B11, how many times (out of 10, in this example for sake of ease) was that value greater than the entries in the first ten entries in Col A:
95 (cell B11) was greater than A10, A9, A8 and A7 (91, 92, 93 and 94). So 4 times out of 10, B11 was greater; 40% is the answer the formula would yield.
16 was never greater than any entries in A2 thru A11, so 0%.
And 123 was greater than all the entries in A3 thru A12, so 100%. Etc, copied down the sheet...
I have two columns A and B. In each column are a list of numbers. I want to count the number of times the numbers in column A are greater than the numbers in column B and display it in the form of a percentage. An even easier way for me to do what I need is as follows...I have a third column (column C) that subtracts A from B...if it is a negative number, the cells in column C are autoformated to color the cell red. If it's a positive number, the cell is colored green. Is there a way I can count the number of cells that are red and divide them by the number of cells that are green to get a percentage?
I have been browsing this site for some time but never posted. It looks like a great community and looking forward to learning!
I am trying to create an array in VB that searches an entire column (L) that has nothing but numbers in it. I want to search for all numbers that are >= 6. If the number is greater than six store the value in column A. At the end, I need to display a message box that has all the values in A that match the criteria where column L is greater than 6.
I have found all kinds of copy to other sheet formulas and static finds.
I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then Select Case Target Case "0.5", "1", "U" icolor = 38 Case "C", "M", "P" icolor = 40 Case "A", "S", "D" icolor = 36 Case "L", "UP", "C/E" icolor = 35 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub
* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).
* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.
* When the cell contains no value, the borders should return to how they were previously.
I have to make a table that shows that a if someone purchases
less than 5 items they receive no discount 5-10 items they receive 2% discount 11-20 items they receive 5% discount 21-50 items they receive 8% discount over 50 items they receive 10%
and it has to be done in a way that the discount rate can be calculated using Vlookup I am struggling to find the best way to write this table. i tried numbering 1 to 50 and writing the corresponding discount rate in the second column but this looks untidy and can't calculate greater than 50 as i am not sure how to write it in the cell so it reads as >50 and not just 50.
I have a large dataset that includes information on the investment payout ratios of US companies over the period 2004-2006. I need to calculate the weighted average investment ratio for each company---see sample below
Microsoft Excel - example.xls ....
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The corresponding weight for each year must be as follows: 0.5 for 2006, 0.3 for 2005 and 0.2 for 2004. Then, for company A, the weighted average will be: (0.5*B2)+(0.3*C2)+(0.1*D2) = 7.8 (see cell E2)
The problem is that for some companies there are missing data for some years. For example, for company B, there are no data available for year 2004. In this case , the weights have to change to 0.6 for 2006 and 0.4 for 2005. Then, the weighted average investment for company B will be (0.6*B3)+(0.4*C3) = 16 (see cell E3). Similarly, in cases when there are data for only 1 year , the weight has to change to 1. Then, for company C the weighted average is 35 (see cell E4)
Is there any formula to give me the weighted average as it appears in column E without having to adjust it for each single case?
I'm doing a GCSE project for which I need a little help with Excel.
I have a set of data about a persons monthly spending habits on
Food 300 Entertainment 200 Clothing 100 Transport 260
The pre calculated budget allowance for the month was 500
I would like to write a formula that would do the following; give the person an score out of 1.1-10 based on how well he spent on the above items which are in order of priority (i.e. it is better to spend more on food than on entertainment etc) And then an over all score (1.1-10) that will reflect how well they spent.
In Col DL are digits ranging from 1-36, in Col Dm are the number of times they were drawn in 36 draws. I have a weighted avg formula in Col DN, that I found thru search, but it doesn't appear to be working right or maybe I don't understand the formula. I have no idea if this can be done.
************************************************************************>Microsoft Excel - F5 HOT-COLD.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutDN3DN4DN5DN6DN7=DLDMDNDO2DIGITSHITS 34125 47105 53395 62185 72885 DIGITS [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.
Column A is the key. If multiple rows have the same key, they're related.
The last column (DX on my spreadsheet) represents minutes participated, and is the weight variable. The middle columns are data that I'd like a weighted average for.
Here is the output I'm looking for given the above example:
Column A remains the same, with one row for each key. The middle columns have their weighted average (where 6.66 = (5*20+8*13+7*46)/(20+13+46)), and the final column (DX) is the sum of minutes for all like keys.
I have long lists of data which has name of manufacturer, number of items and fault rates of items.
E.G
Manufacturer.......Number of Items......Fault Rate Dell....................40000....................0.2% Dell....................42200....................0.4% Sharp.................303003..................0.1% Microsoft.............312340..................0.04% Dell....................3249321..................1% Panasonic............300020...................2%
I need to find the weighted average of each manufacturers fault rate for just themselves, and also how the average of all the manufactueres would be if they weren't in it.
So for example for Dell I would need their weighted average of their three data entries and also then the weighted average of Sharp, Microsoft, Panasonic together.
I have been doing this by splitting the data into sections so for the example above I would do:
Manufacturer.......Number of Items......Fault Rate Dell....................40000....................0.2% Dell....................42200....................0.4% Dell....................3249321..................1% Panasonic............300020...................2% Sharp.................303003..................0.1% Microsoft.............312340..................0.04%
I would sum the Dell items, then sum the number of items excluding Dell then do two weighted average calculations giving me the ones that include then the ones that exclude Dell.
Then I would do...
Manufacturer.......Number of Items......Fault Rate Sharp.................303003..................0.1% Microsoft.............312340..................0.04% Dell....................3249321..................1% Dell....................40000....................0.2% Dell....................42200....................0.4% Panasonic............300020...................2%
And do the same process but with Sharp totals etc then move onto the next one.....
This example is with a very small data set so I'm sure you can see that this process is very time consuming, but I don't know any other way of doing it without splitting out the data first.
I am trying to find a formula that will calculate a running total of employee time off. Time off is recorded in Excel spreadsheets that I created. V = vacation, S = sick, PD = personal day, and so on.
For example: If a V is entered in any cell in their attendance worksheet, I would like for a 1 to display under the vacation total column. The next time a V is entered in, I would like it to update the cell containing the vacation days used for the year to 2. If a 1/2 V is entered, a .5 would be added to the total used.
I have two columns. One has cost (column A) and the other has profit (column B). I'd like to calculate the percentage profit as a function of how much something cost. So, if it cost $100 and the profit was $20 I would like to calculate 20%. For all percentages, I'd like to average them. I understand this is a "weighted average" and was able to get this working with this formula:
I'm trying to use the 2007 histogram tool (or frequency) to show me the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"??
My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total).
Ceate a formula for weighted averages according to criteria?
I want to know what was the average price I bought a product at on today's date. The average price needs to be weighted against the quantity of product sold.
I created an Excel tool into which really thousands of records are based on weighted averages calculations. And discovered Excel struggling in term of permormance.
The issue is: the user needs to uploads a couple of times in a day new data for further analysis, and the only way to make the Excel spreadsheat humanely usable (performance) was to calculate everything upfront.
To optimize the tool performance, I had to:
- First I made all weighted average calculations upfront when data is uploaded the first time. Leaving formulas in the tool was not viable, since this tool run live analysis, every click would have been a pain of 30 sec at best to re-calculate new conditions,
- Second optimization was to not leave formulas into columns > as soon as a calculation is done, the macro copy/paste values
The formula I chose is sumproduct based weighted averages..
Here is my question:
1. Does it exits specific formulas in Excel to handle nicely large amount of weighted averages to calculate ?
2. Should I consider to use an external application (an Access table, SQL or whatever) to perform those weighted average calculations quickly/quicker.
I am having trouble coming up with a formula to calculate the weighted average of students Finals grades. This is the format my sheet is in to give u an idea.
----------------------------------------------------------------------- Test # 1 Test # 2Test #3Assignment #1Assignment #2Final Test Final MarkPossible Points:1001001001010100Percent Worth:20%20%20%5%5%30%Students:Anderson, Ken6473788768 ?Carter, Bill72768473.587 ?Dubeers, Ashley84887610590Jackson, Kevin7666549873Parker, Jerome82657679.560Remini, Leah969290101089Thompson, Chuck5042806.5760Vaughn, Sarah7265658970 ----------------------------------------------------------------------- - I have to come up with students averages based on the mark, the percent each grade is worth and the maximum amount of points its worth. Anyone know the formula or how to calculate the final grade using all of these factors?
If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.
I have trouble finding the right formula for a weighted average price. The formula which i used in cell B2 of the attached file doesn't exclude irrelevant numbers. In the formula "= sumproduct(a5:a21;b5:b21)/sum(b5:b21)", i like to exclude cells B8, B9, B15, B19, B20 because the list prices are missing. How can i exclude these irrelevancies in the formula above?
