Calculate To Percentage Reached, Then Start Over
I have a column that is a percentage(p%) which measures the fullness of the location(loc) that the item(ite) is in, which also has a corresponding location(loc) and item column(ite).
What I need to do is add as many items(ite) to one location(loc) as possible without exceeding 100%(p%), however if the value exceeds 100%(p%) then we need to start all over in a new location. The end result would be an unknown amount of unused locations.
If p% < 100 then add the next row's p% value, else start over with a new location. I am trying to add as many items to one location as possible, and once a location fills up, we will use the next one.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Display Popup When Percentage Difference Reached
I have an excel file that linked to an external feed that receives stock prices in real time. on the file i have the stock name, the price paid and the current price. I would like to have a popup that shows up whenever a stock price has a 15% or higher return. I would like the popup to show the stock name. I realise that conditional formatting would highlight whatever is over 15%, but the workbook has multiple worksheets and I would like it to popup even when i am on another worksheet. enclosed is a copy of the file (the actual file has many more sheets)
View Replies!
View Related
Calculate The Percentage
I have attached a file with both a sample section of data on the first sheet and the outcome I would like on the second sheet... I would do it all manually but there are over 200,000 rows in the actual file. The macro needs to calculate the percentage of sale for each reference number within each part number and move down to the next part number and do the same until it reaches the bottom.
View Replies!
View Related
Calculate Cumulative Percentage
Microsoft Excel - Book1___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)boutB5= BCDEFGH5JanFebMarAprMayJunTotal6-3%-2%5%8%5%5%???Sheet1 I have this example above which i want to know what is the total % change from Jan to Jun. In the total column, the result i got is 19% using formula =1*(1+B6)*(1+C6)*(1+D6)*(1+E6)*(1+F6)*(1+G6)-1. Is there any other more simplified or better formula that can be used actually?
View Replies!
View Related
Automatically Calculate The Percentage
code needed to be able to automatically calculate the percentage needed from one year to the next. I simply need it to automatically create a third column each time the copy old data button is pressed, and I need the column to have the percent increase ((current year - past year)/current year). Hopefully you can understand what I am aiming for. I have the current code below, and I will attach a compressed and stripped version of my workbook.
View Replies!
View Related
Formula Calculate The Percentage
I have a row of numbers from 1 to 26 in cells A1 to Z1 representing numbers 'chosen' at random by 500 unique people. I have another row of numbers representing the 'frequency' that the aforementioned number was chosen in cells A2 to Z2. Example: 1 2 3 4 5 6 7 8 9 10 11 ... 10 4 16 9 23 20 61 13 18 43 17 ... Is there a formula that I could use to calculate the percentage (0%-100%) that each number was chosen?
View Replies!
View Related
Calculate Weighted Percentage
In my spreadsheet (testing.xls) I have a chart to mark the quality of a phone call based on a list of 8elements being judged. I have a likert scale for the quality result for each element, say for example 5 for highest, 3 for average, 1 for the poor, 0 for very poor and NA for an element which is not counted. At the bottom of my table I have a cell where it would work out the % based on the outcome of the result from the different cells being ticked, what would i need to do to have excel work this out for me? Please bare in mind that if there is for example 1 element out of the 8 elements marked as NA the result would be divided 7 instead of 8.
View Replies!
View Related
Extract And Calculate Percentage (%)
In the attched sheet, I am wanting to draw data from the worksheet "On-Off" Array $AI$14:$AJ$91 into column AA, while at the same time choosing the closest match to the time. I have managed to get the closest matching time into column AC. What I am having trouble with is now calculating the number of staff on that break time (column Z) against the number of staff rostered on at that stage "On-Off" (column AJ). I thought it would be a simple division and format as a percentage, however something in it using the extract from the "On-Off" sheet is stopping me, and I cannot figure it out.
View Replies!
View Related
Calculate Percentage Of Attendances
What I have is four columns with data. First column name second column number third column name fourth a number. Trying to get the total of the numbers for the names. So the first and third columns have names in them and they can be in each column or just once in either. I need to get a total column with all the unique names and then their corresponding number (added together if they are in both columns) to display the name and total. The names could be duplicated in each column as we (hopefully) have a lot of the same people coming week in week out. What it is being used for is a two week and four week revolving attendance. I have a daily sheet one that I am using data filter unique values and then a countif to show me their attendance for the week. Putting it into a totals page sheet with week 1 week 2 etc. So looking for a way to get all the unique names and their corrorsponding cell value added together to show the persons attendance for two weeks and then the month.
View Replies!
View Related
Calculate X Percentage Of Cost
I am trying to create a simple formula to extract cost from a total that includes both cost and and a percentage for maintanance. Assume $100, 10% of which is maintenance the remainder is cost. If I just subtract 10% from $100 I get $90, however 10% of $90 is $9 which equals $99.
View Replies!
View Related
Calculate Percentage Of Text Occurences
I am having problems with a macro I wrote that basically fills things in two columns based on a set of values from other columns. Lets say it looks like this (column B is based on column A): A1 = Hi A2 = Hello A3 = Hi A4 = Hello A5 = Hi A6 = Hello A7 = A8 = Hi A9 = Hello A10 = Bye After the macro runs, column B should look like this: B1 = 10% B2 = 20% B3 = 10% B4 = 20%.............
View Replies!
View Related
Calculate Percentage Based On The Number
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out. Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
View Replies!
View Related
Calculate Percentage Based On Criteria
I have events in column 1,in my sheet there are about 800 events. In col 2. each contestant has a label,blue(b),green(g) or red(r) In column 3 I want to print the % of blue + green of the total for each event, e.g first event in attached sheet would show 77.7% down to row 10 in col 3 etc.
View Replies!
View Related
Macro To Calculate Range By A Percentage
I'm trying to do a forecast of my organization's budget for the next five years and need to create scenarios where certain figures will either be increased or decreased by a specified percentage that varies from 5% to 20%. I have recorded eight macros that can accomplish this task, but I feel like there must be a more effienct method. In addition, I would like to increase or decrease the figures from either column H or column J. Column R is the results column. This is the macro I recorded that provides for a 10% decrease:
View Replies!
View Related
Calculate Percentage Of Positive Numbers Only
I am trying to find a formula to calculate the percentage of the positive numbers in a column. For example, if I have a column that has positive(green) numbers and negative(red) numbers in the cells to show over daily goal or under daily goal, and I want to at know what percentage of all the numbers are the positive ones.
View Replies!
View Related
Calculate Percentage Of Total Salary
I have percentages set up for my monthly costs (student loans, food, spending money) and I want to be able to type in my weekly salary and have the percentages break it down into dollar amounts. Later, I'd like to put that into a pie chart, but I'm really only asking for help referring each category/percentage to the total $ value I enter each day/week/month.
View Replies!
View Related
Calculate & Display Percentage
how to display a percentage in a field in excel. EG. If I have a calculated price in cell A and I would like to enter a price manualy in cell B , how would I get excel to display in another cell what perecentage A is of B price A would would be for example a purchase price , Price B would be a suggested retail price entered manualy and i would want cell C to show me what percentage increase it is of price A
View Replies!
View Related
Calculate Numbers Required For X Percentage
what I'm trying to do. Background: I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games. What I would like to accomplish: I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play. I tried to make the question as clear as possible, if anyone needs clarification, Originally Posted by shg Welcome to Oz, Basca spreadsheet with a representative sample of your data? Games Played Player 1 20 Player 2 7 Player 3 13 Player 4 20 Player 5 13 Player 6 13 Player 7 7 Player 8 7 Total Games Played20
View Replies!
View Related
Calculate Percentage With Greatest Denominator
I have two columns of data, in another column I have the difference between these two columns, now I want to show the difference as a percentage. Assume cell A is 0.02 and cell B is 0.03 the difference is 0.01 and the percentage of the difference would be 33% or 0.01/0.03, my problem is when cell A is 0.04 and cell B is 0.01 the same formula wouldn’t work in both cases since in the first A<B and the second A>B is there a way that I can take the difference and divide it by the greater of A and B without having to do it manually?
View Replies!
View Related
Calculate Start/End Times
Trying to get times worked in hours and mins, from a given start time to a given finished time. I would like H18 to read 14 and H19 to read 13.5 and so on, H23 and H24 are full 24 hour coverage, but i get a 0, when using 08:00 - 08:00 as we only deal with a full or half hours, the figures should read whole numbers or half number as in th example above. Also would like to total all hours worked in H28 to 117 as opposed to the figure given in the attached file. DAY / DATESTARTENDHOURS MON22-May200618:008:00 AM14:00 TUE23-May200617:006:30 AM13:30 WED24-May200617:307:30 AM14:00 THU25-May200618:007:30 AM13:30 FRI26-May200618:008:00 AM14:00 SAT27-May20068:008:00 AM0:00 SUN28-May20068:008:00 AM0:00 TOTAL2.875 Couldnt upload the file as it was 75kbs, a
View Replies!
View Related
Calculate Taxes And Pension As A Percentage Of Total W-2
I have attached a sample workbook of what I am struggling with. It is for payroll, and I need to calculate taxes and pension as a percentage of Total W-2. The problem is that the 'Total W-2' column then deducts these taxes and pension. Currently, the worksheet has about 30 columns out to the right using 'guesses' to back into these numbers, but I hope there is an easier way.
View Replies!
View Related
Calculate APR (Annual Percentage Rate)
I am trying to calculate APR (Annual Percentage Rate) for a mortgage loan that has a balloon feature. I have tried to the the RATE function but it only gives me the APR for a loan that is ammortized over 30 yrs and paid in 30 years. I need the APR for a loan that is ammortized for 30 years with a baloon in 5 years.
View Replies!
View Related
Multiple Drop Down Menu To Calculate A Percentage
I have a bunch of cells that use a drop down menu validation from a different set of cells so Legend (this are in cells $A$7:$A$11) NA - Not Applicable 0 - Not attempted 1 - Not Yet Competant 2 - Satisfactory 3 - Exceedes Expectations Other cells have Data Validation using Allow List =$A$7:$A$11 to make the drop down menu option appear. What I would like to do is add a 'hidden number' on cells A8:A11 (A8=0, A9=1, A10=2, A11=3) A7, being NA isn't included in the formula I want to set up. In cells later on I'd like to make a formula that takes those 'hidden numbers' into account and works out an average. So 'hidden number' / 'potential total' and make the cell display as a percentage, the 'potential total would have to take into consideration that a value has been set, but not use that value, and times the number of values set by three.
View Replies!
View Related
Calculate The Number Of Days From Start To End
I have 2 dates a Start Date and an End Date. I need to calculate the number of days from Start to End over eight years with each of the eight years in its own column. Start End Yr1 Yr2 Yr3 11/01/99 11/01/00 11/01/01 10/31/00 10/31/01 10/31/02 Col A Col B Col C Col D Col E 10/31/00 07/05/01 365 117 0 I thought a series of IF statements but I am open to an easy solution...
View Replies!
View Related
WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends? For example, Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009 Start Date: ??? Finish Date: 8 Jun 2009 Duration: 5 days The Start Date should be 30 May 2009. Seems like I am not able to use WORKDAY function to calculate the start date.
View Replies!
View Related
Calculate Weekly Sales With A Midweek Start
I'm trying to create a simple sales report. No VBA code, only excel formulas. I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week. Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only. I want it done automatically. I've included a zipped excel sheet example of the worksheet for a visual example.
View Replies!
View Related
Calculate Holidays Remaing Based On Start Date
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.
View Replies!
View Related
Percentage Of Cells That Start With X If Corresponding Cells Have Data
I need to calculate the percentage of cells in one column that have data in them compared to those that do not. This is easy enough to do by itself but here's where it gets tricky for me. I need this percentage calculated only when cells in a different column contain data. Here's the specifics. All of the row and column references are exactly what I'm looking to have in my worksheet. If A5:A200 contain any value then I want to know the % of the cells in F5:F200 that have a value in them compared to the same number in F5:F200 that contain no value. I would like the answer to show up in B2. Part of the issue is that cells A5:A200 already have a formula in them so I think that would count as a value. The one thing that might help is the fact that A5:A200 values that I'm looking for ALWAYS start with TN so that could be used as the search function. The reason why I need to base it on a value in A5:A200 is that the sheet is blank until I enter data in the rows. I can't have the blank rows below the last data that I've entered on the sheet count towards the percentage that I'm looking for. Basically it's a sheet that I enter installations and service calls on. The information always has a TN# associated with it. Which through a formula is automatically populated in the same row in the A column. I track the installation or service call and when it's completed I enter a completion date in the F column in the same row. I am trying to be able to keep track of the percentage of jobs completed (completion date entered in column F) compared to incomplete (cells in column F that have no completion date) but it has to only look at rows that actually have information entered in them which I think should be done by column A. column D5:D200 is where I enter the main information and there is no formula entered in the cells in column D so if it's easier to base it on that column then that's fine.
View Replies!
View Related
Calculate Total Pay Based On Sliding Percentage Scale
I am trying to set up a spreadsheet where all the sales manager will have to do is input the number of cars sold to determine what the total amount of commission will be for a salesperson. I have something set up so far, but need the spread sheet to do an if/then type thing where it will search the table, and produce the dollar amount for the number of cars sold based on the scale and the number in the # cars sold cell. I've tried conditional sum, IF, goal seek, and all kinds of stuff to no avail. The spreadsheet is attached.
View Replies!
View Related
Calculate What Months An Employee Worked By Start And End Date
I am working on a sheet that will allow a user to enter a start month (from a pre-defigned drop down list) and an end month (again from ddl). What I need to calculate is in what months the employee worked in so I can calculate their salary cost by quarter. It can populate other cells in order to do the calculation if needed. So, for example if an employee started in May, and worked until December, I need to calculate that he worked 2 months in Q1, 3 months in Q2, 3 months in Q3, and 0 months in Q4. Our fiscal year is April to March.. ;-) I have thought of several options, but none of them have worked 100%.
View Replies!
View Related
Calculate Future Dates From Start Date With Varying Time Period/cycle
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods. For example: I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is. I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated. I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
View Replies!
View Related
To Calculate The Difference Between The Start Time And End Time
This may be a bit vague but here goes. I have to calculate the difference between the start time and end time of a job. The only catch is, how can I avoid calculating "out of hours" time. So, if a job goes from 9am to 9am the next day, I want it to avoid calculating between the hours of 23:30 and 03:30. Another example is if a job goes from 02:00 to 04:00, I want it to avoid the tim between 02:00 and 03:00. If there is a difference in days, so the job goes overnight, how do I take that into consideration also.
View Replies!
View Related
Increment Until Value Is Reached
I have a value in cell P1 and is always -ve. This value is calculated by some tedious calcualtion in some other part of the sheet. By entering value in Cell P2, the value in cell P1 will progress towards +ve number. What i want is a formula or Macro to increment the value in Cell P2 by 0.01 until value in Cell P1 becomes +ve... Below is the example of my manual calculation. When i have P2=0 P1=-23.58 P2=0.01 P1=-14.78 P2=0.02 P1= -9.30 P2=0.03 P1= -2.92 P2=.04 P1= 1.05 Since i have achieved P1 to be positive, calculation should stop with value in P2=0.04 I have to do this for 31 column and hence manually doing it takes long time
View Replies!
View Related
Stop Code When Value Reached
I have written a code but i can not make it stop where i want it to stop. I would like the code stop when cells(31,4).value reaches 0. Pls refer to attached sheet for a clear example. The code is as follows Sub production2() If Cells(35, 4).Value > 0 Then Cells(31, 4).Value = Cells(35, 4).Value For j = 5 To 16 If Cells(36, j).Value < Cells(29, 4).Value Then If Cells(31, 4).Value > Cells(29, 4).Value Then Cells(37, j).Value = Cells(29, 4).Value If Cells(36, j).Value < Cells(29, 4).Value Then Cells(37, j).Value = Cells(29, 4).Value Cells(31, 4).Value = Cells(31, 4).Value - Cells(37, j).Value Next j End If End Sub
View Replies!
View Related
To Sum Cells Until Preset Value Is Reached
I have a spreadsheet with time values incremented in column A. In column K, there are numerical values that correspond to the time in column A. Elsewhere, a specific time is chosen from which to begin the summing of the values in column K. I need to be able to sum the values in column K until preset values are reached. Then I need to know at which corresponding time value the summing loop stops. For example, a time of 11:00 AM is chosen as the "start time". I want to sum the values in column K until they are >= 2.000. At that point, I need to know what the cell value was in colum A that is in the same row as the last cell value in column K. I can't seem to do it with simple Excel functions without falling into a self referencing loop. So what I need is for a VBA loop to lookup the appropriate values in column K, sum the values, check the total, and continue the loop until the preset total is reached. Then look up the value in column A in the last row of summed values, and return that value.
View Replies!
View Related
Set And Retain The Date That A Value Is Reached
I have a quantity column on an inventory sheet which automatically updates the available quantity based on sales from a sales sheet. But I need to know the date the quantity reached zero. That date would always be "today," on the day the quantity = 0, but how do I make the date NOT update to "todays date" on the next day, and the next, etc. It needs to stay as the date that 0 qty was reached.
View Replies!
View Related
Keep Row HIDDEN Until Specific Cell Has Been Reached
I want to Freeze my Header Row (A16 to T16) on my spreadsheet but I can't seem to get it to freeze. I wanted to have it freeze when that row reached the Top of the page but all I can do is freeze it and the 15 rows above it. Can one row be made to be visible at all times? I don't think so as I asked this question before but thought I would start by asking it again... Now my workaround for that was to put the header row at the top of the page as well and freeze it. This works but when you initially open the spreadsheet it look weird because you see a header row on top and one 15 rows below it. My question is is there a way to hide row 1 until a specific cell becomes active and then the code can undide the row? I want row one to become visible when the user gets to cell A41. Does anyone know how I can accomplish this or do you have any other suggestions for me to try?
View Replies!
View Related
Disable A Range When A Numerical Limit Is Reached
How do I disable a range of cells when a numerical limit is reached? Here is the scenario: I have two worksheets, one called “Items” which contains a list of Items and their weight. The other sheet is called “Container” and displays a Type of container in cell B2, the weight capacity of that container (the numerical limit) in cell C2, and a range from B5 to B14 that needs to be filled out by an end user. The items in B2:B14 are chosen using Validation, form the Items sheet. The weight is brought in via a vLookup, and the total weight is calculated as items are added. My problem is that I have yet to find a way to “disable” any extra cells in B5:B14 once the weight limit is reached, and clear the last cell data was selected for, all without destroying the Validation for the cells in range B5:B14. I have tried many different things in the Worksheet Change Event, but none have yielded the desired result.
View Replies!
View Related
|
TRACKER
