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.
example 1:
This years sales are $3700, a decrease of 11.6%. What would last years
sales be?
example 2:
This years sales are $4500, an increase of 151%. What would last years
sales be?
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?
I have a list of YES or NO answers in a sheet. I want to calculate the % of YES in the list. E.G. if there are 100 cells in total and 25 of them say YES and the remaining 75 say NO, the percentage should be 25%.
View 2 Replies View RelatedI 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.
I have a form in work which acts as a tick sheet for tasks complete on a construction site. When a task is complete I copy & paste from a key of dates i.e. week ending 11.05.15, this allows me to see what week a task was complete.
Generally I only marked off tasks which were 100% complete but my manager wants me to enter the % of the task complete also, i.e. 80% in the box.
So my question is how can I have the formula assume that any box with a date & shading is 100% and any cell with a number i.e. 80 is only 80% complete. I need it to monitor around 150 cells and give me the overall % complete based on what i have discussed.
I 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 have a number which consist of 12 digits such as 765432123345. The first two digits denotes the year. I need to create a formula in the next column to calculate the difference in years by comparing to the current year of 2013. In this scenario, the difference will be 37. Is there a way to do this?
View 5 Replies View RelatedWe typically give out Free Rents (usually a few months) to our tenants, which is always up on the front. And then we would charge a rent increase ($.50 per year in the example) starting 12 month after the rent free rent ends and every year (12 months) afterwards. The rent will be flat between each increase. My questions have two parts:
1. Is this something array formula can handle in one formula?
2. Since I am not an expert on array formula, what I ended up doing was to create a line for "Rent Bump Date" which is defined as the Free Rent + 13 for the 1st rent increase (meaning if you dont get free rent you would start paying increase in month 13 and every 12 months after. And I put in what the rent would be for each rent bump date. My solution is if the rent for each month (1 - 240) would be determined based on the ranges of those bumps. For example,
Month 1-15 Rent $20
16-27 20.50
28-39 21
40-51 21.5
52-63 22
64-75 23
......
......
......
232-243 30......................
I am working on my companies tax reports for the first time. The wages for unemployement and FUTA do not calculate IF the total wages are over a certain amount.
Im trying to get the formula to see if the total wages are over $7000 then dont calculate. Ive tried several different things from the links, but still can't make it work.
total wages are $7800, so i only need to calculate it on $7000 and then not calculate it again for the next quarter.
I have a spreadsheet that has staff id in one column and the work items number that they have done in a daily basis in another column.
The actual list is very long. I need to summarize in another column how many work items that they have completed in a daily basis.
I have attached a sample spreadsheet as an example. I would need to summarize in column H based on the staff ID. Some work items are shared by two staff but it will have to be counted as one work item completed for each staff. If work item B123466 is completed both by staff M56 and M54, then it will be counted as one for each.Currently, I am doing this manually with the filter function which is very tedious and often has mistakes. I would like to formularize this task.
I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES.
Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question?
The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).
Need a way of getting one of 4 values in sheet 2 to perform a calculation with. The aim is to show the total volume of a given item. Because there are four data sources for the actual volume (with varying degrees of accuracy) I've incorporated all of them in one sheet but want to use the values in precedence i.e. the first figure is where I've had someone physically tell me how many fit to a pallet (cubic metre) and know that to be accurate. If the value there is 0 I want to use the next figure as certain other items were measured physically. Column three and four are from some legacy systems and therefore should only be considered if the figures are 0 in the first two columns. Rather than ramble on any more I've attached a sample sheet
View 4 Replies View RelatedI am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided
Total KM run
Basic Amount
Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
I only want the formula to operate when there are values to calculate and then ONLY refer back to the last calculation. If there are gaps between the calculations I would like those to remain blank. Is that possible?
I have attached an example of what I am trying to do.
I have long series of data in 8 columns (B, F, J, N, R, V, Z, AD) starting at row 5 down to row 86404. I want to find the total median for each row, where a condition is met for that row. The condition is if number>10 and the number is located in row 3 for each column (i.e., B3 for column B). So, if B3, J3, R3 are all >10, then I want to find the median for all the numbers in the columns B,J,R.
I assume there is an array formula that could do this? I've tried a few solutions with no luck.
I'm trying to design a Profit and Loss template that calculates off an accounting software data dump. I will attempt to explain the problem here but I have included a summary in the linked workbook which would be easier.
DOWNLOAD WORKBOOK: [URL]
I have two problems:
The Formula: Basically, I need to calculate every cell in the Profit and Loss statement based on the below criteria: The Profit and Loss item accounts, e.g. Gross Income has 6 accounts. There is a reference table for these - so incorporate index match/vlookup? User selected data from 4 combo boxes.
I am thinking a SUMIFS formula (using the combo box values) and somehow combining a vlookup to pull the items accounts. I'm starting to think that VBA is the best method?
Tweaking: The attached workbook is a small example. The full data dump contains 60,000 rows.
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies View Relatedcalculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%
I have a simple issue i cannot figure out how to write a formula for.
In A2 i have the number of operations.
In A4 i have the percentage of CPU usage it requires to complete those operations.
I need an output somewhere that will tell me how may operations I can get per 0.1% of CPU usage.
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.
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.
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.
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?
I have a column of percentages, how do I get an average % rate at the end of my column?
View 7 Replies View RelatedI need a correct formula to calculate percentage...mine is not correct
Cell J18 will hold the formula.Range C18:I18 will have values.
C18 will have a value for today,D18 will get a value tomorrow,E18 will get a value 1 day after tomorrow etc..etc..
J18 formula - =sum(C18:I18)/7
If I only has a value in J18 then it will divide that single number by 7.......can a formula only divide by entries in cells....if I have 1 entry then it divides by 1,if I have 4 entries then it divides by 4 etc..etc until thee is 7 entries.
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?
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.
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
I have in row D a list of numbers and in cell F a list of salaries. I need a forumla to tell me what percentage D1 is of F1 etc.
IE the formula will be something along the lines of D1 * 12 / F1 * 100