Need a formula for level report. cell c-1 contains percentage for shop production. 100% 105% etc, etc. 100-109=level 1, 109.1-119=level2, and 119.1 and above=level 3. I am in need for a cell to translate the percentage to type 1,2, or 3 accordingly. cell c-1 will recalculate when work production figures change.
View 3 Replies
how to deduct a percentage off of a range of units.
For example, if I have 100 units that cost $50, I'd like to deduct 20% off of the first 10 units, and then have the total dollar amount put into a separate cell.
So, 100 x $50 = $5000
With 20% off of the first 10 units: $4900.
__________________________
20% of $50 = $10
$10 x 10 units = $100
$5000 - $100 = $4900
I need to have a conditional format that will adjust based on percentage ranges.
1% to 25% would be shaded red
26% to 50% would be shaded Orange
51% to 100% would be shaded Green
I was hoping to reference another cell and have the formatted cell reflect the conditional format. I dont think that's possible, so I can include a percentage in another cell and have that highlighted.
Is there a formula that I may use to keep track of the YTD percentage of attendance of volunteer workers over the course of a year? I have approx 200 workers meeting once a week. Their names are listed in column "A" from A-4 to A-204. Starting in column "E" is the date of the weekday for attendance.
For Example: E-1 is dated Jan. 7, 2008. F-1 is dated Jan. 14, 08 etc. I place a one (1) in the corresponding cell to show that they attended. The formula I need would start in cell D-4 and be dragged down to D-204.
There also must be a way to factor in the weeks which we are to meet that must be cancelled due to weather etc. The worker should not be penalized due to our cancellation.
I have a simple spread sheet where i need a formula to work out a percentage. eg total cases 35246 number of errors 3. what percentage is the 3 of the total cases and what is the formula required to reflect the answer.
View 3 Replies View Relatedhow to construct a formula that would give the amount of deposit needed initially that would equal, after an immediate percentage reduction, a certain amount.
For example, if the initial deposit was immediately decreased by 2.9 percent and 30 cents (as in a PayPal transaction), what would that initial deposit have to be to equal exactly 50 dollars after being reduced?
I am looking for a formula that looks across and row or column and makes sure that a max of 100% or dollar value is not reached. Is there any solution that would allow for flexibility. Below would be the wrong result.
30% 20%25% 10%30% 115%
600,000 200,000 250,000 100,000 x 1,150,000
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 need to write a formula to minus a percentage of a total.
for example I want to take away 20% of the total of £8.75.
I 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.
I have a question regarding calculating percentage differences, here is how our formula works, but I've done a little research regarding using negative number for percentage calculations and am not sure...
For sample:
this year (TY) we had net profit of $200 million
last year (LY) we had net loss of -$32 million
our formula works kinda like this:
=IF(B49="","-",IF(B49=0,0,D49/B49-1))
but if, the comparing numbers is negative should I convert that to an absolute number using ABS ? what if both numbers are negative ?
The way I understand the current formula is like this:
if B49 is blank, then insert a dash, otherwise IF B49 is 0, then 0 (since we can't divide by 0), otherwise do the formula.
Formula request
I need to ( in currency)
Example
Col B = $2.06 . Col C = $3.42
I need to in Col D to calc the difference between B and C less 25%.
So in the above example D should = $2.90
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
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows:
For example, Jamaal is authorized for 80 hours per month but only uses 15 hours.
What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance.
Therefore, I came to the conclusion that:
0.25% x 80 = 20 hours
0.50% x 80 = 40 hours
0.75% x 80 = 60 hours
Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 25-50, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
The attached spreadsheet is a sample of a master sheet I need to maintain. Keep in mind that the spreadsheet has hundreds of names on it and 10 sheets in the workbook. What name appears most in the "person who called" column, then I need to know out of all the people who called, what percentage of calls he made....
I have to do this on each sheet, so if it is possible to have it work for all the sheets.
In cell B77 i have a percentage formulated from other information in the sheet. I want cell B78 to show a word based on the percentage score.
As follows:
0%-25%- Poor
25%-50% - Competent
50%-75% - Professional
75%-100% - Expert
I have a set number in col A for example 100. Col B is variable value. And Col C is where I need the formula.
If for example Col B is within 5% of Col A return value of "G" in Col C.
If Col B is within 20% of Col A return value of "A" in Col C.
If Col B is under or over 20% ( example if Col B is 79 or 121 ) of Col A return value of "R" in Col C.
Formula to calculate the YTD % ?
If A1= YTD 100% and B1, C1 ,D1 etc.. do represent the month of Jan, Feb, Mar...etc
What do I have to use for A1 to auto-cal Jan - Dec?
=IF(AND(G9=0,H9=0),"",IF(AND(G9=0),1,H9-G9/G9))
this works great execot for the percentag is wrong when h9 and g9 are populated
here is the formula I am using. =IF(A5 < =Schedule!$K$9,100%,0%) We only track complete and not complete....so my formula above just comes out to 100% or 0%. What I am wandering is if I can add a modifier so that it can be adjusted w/o just over writing the formula. Ok so my vision is, the formula above is in cell B5.....next to it in cell C5 I would like a modifier so that if that amount should really be 85%. I can type 85% in C5 and it will change B5 to 85%. So I guess the formula would be more like =IF((A5 < =Schedule!$K$9,100%,0%),or = C5))
View 2 Replies View RelatedI need to make a formula to extract GST from a total amount, when the total amount also has PST (like state tax) in it.
GST is 7%
This is how I would figure it out manually
$100/1.07=93.46
$100-93.46=6.54
$6.54 is the number I'm trying to figure out how to get to with an excel formula.
I have data one sheet which is
Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100
On the next tab I am consolidating all the reference numbers and want to capture the latest % complete
I have been trying =Max(if ref no=1 and type = Erect, % complete),1
But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.
Looking for a formula to show percentage increase of a figure.
Cell A1 is £45,000
Cell A2 is £51,000
Cell A3 ANSWER result shown as a percentage increase.
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.
Data is;
83300 - hypothetical number of times I have fired my gun at target.
43209 - hypothetical number of times I have scored bullseye.
So, my bullseye percentage = 43209/83300, or ~51.87%.
Need formula to determine how many more consecutive bullseyes I need to shoot, in order to achieve 70% ratio.
Since each shot from now on will be a bullseye, both values (hits & shots) will increment together.
I have the following table in excel:
Price: Percentage:
$150 10%
$100 12%
$50 14%
I'm trying to use the percentage depending on what price I enter in a cell. For example; if I enter $135 then 10% will be used, if I enter $65 then 12% will be used, and if I enter $47 then 14% will be used.
I need a formula that will calculate the percentage of the total duration between two dates (in dd.mm.yyyy hh:mm format) that a third, intervening date represents.
As an example:
a) 10th Jan 2014 09:00
b) 20th Jan 2014 09:00
c) 30th Jan 2014 09:00
In this case the answer sould be 50% because 'b' represents exactly half of the time elapsed between 'a' and 'c'.
Note: the formula needs to work effectively across the boundary between two consecutive years.
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 start dates (column A) and maintenance expiry dates (column B). What I want to do is set up a formula to see if the product was renewed between 01/04/2007 - 31/03/2008 and return a simple yes, no or new_sales.
Examples
If the start date was 26/09/2006 and maintenance expiry date was 26/09/2008 then it would return a yes that they have renewed
If the start date was 18/04/2006 and maintenance expiry date was 18/04/2007 then it would return a no that they have not renewed
If the start date was 20/06/2008 and maintenance expiry date was 20/06/2009 then it would return a new_sale
The section I have highlighted in orange has three ranges added together how would I show this in a simplified formula?
=sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$85:$GA$85)+sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$44:$G A$44)+sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$41:$GA$41)
