# Percentage With Formula Or Conditional Formula

Mar 14, 2009
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.

View 9 Replies
ADVERTISEMENT
Mar 6, 2008

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.

View 11 Replies
View Related
May 7, 2009

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 Related
Mar 26, 2008

how 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?

View 9 Replies
View Related
Feb 11, 2014

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

View 2 Replies
View Related
Nov 16, 2005

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 14 Replies
View Related
Aug 28, 2002

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.

View 4 Replies
View Related
Dec 20, 2013

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.

View 4 Replies
View Related
Jan 28, 2009

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.

View 9 Replies
View Related
Jun 1, 2006

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

View 6 Replies
View Related
Nov 15, 2006

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

View 5 Replies
View Related
Feb 5, 2007

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
View Related
Dec 19, 2013

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.

View 6 Replies
View Related
Mar 24, 2012

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

View 4 Replies
View Related
Jul 24, 2012

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.

View 4 Replies
View Related
Jun 10, 2013

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?

View 2 Replies
View Related
Jun 27, 2013

=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

View 4 Replies
View Related
Oct 1, 2013

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 Related
Oct 6, 2006

I 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.

View 3 Replies
View Related
May 15, 2014

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%.

View 1 Replies
View Related
Jan 24, 2013

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.

View 6 Replies
View Related
Apr 3, 2013

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.

View 2 Replies
View Related
May 14, 2014

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.

View 5 Replies
View Related
Aug 24, 2014

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.

View 5 Replies
View Related
Jan 28, 2013

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.

View 3 Replies
View Related
Feb 20, 2014

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.

View 1 Replies
View Related
Apr 15, 2014

Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!$B$15:$DZ$15=1000)

View 2 Replies
View Related
Mar 13, 2008

I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g

1400 156

1400 99

1400 74

1400 86

1400 256

The number of entries may vary for each block but is never more than 60. the next block may be e.g.

14:05 15

14:05 42

etc

and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............

View 5 Replies
View Related
Oct 5, 2013

I need a conditioning format formula. If cell A2 is less than 50% of cell A1, then highlight red.

View 2 Replies
View Related
Jan 26, 2014

Using conditional formatting to make all the cells that contain numbers and are formatted to percentage format make display in red color? let's say I want to set this conditional formatting rule initially after just opening the excel book and afterwards when I type in values/text etc. into the cells I want the cells where I have input numbers and chose the percentage format to come out in red color? and all the rest stay in black color?

View 6 Replies
View Related