I am working on a maintenance form that I have attached.
I am wondering if there is a formula or condition that I can input to find and mark all the spaces that fall on a weekend.
Either to have the form auto-populate with a "W" as I have done in the form, or shade the boxes that fall on a weekend. Shading would probably be better or even shading with the "W".
My aim is to find the end working date for each task, as well as the next working date for the next task. The working days for this employee are only Monday (6hours), Wednesday (6 hours) and Friday (8hours).
　
D
E
F
G
H
　
Start Date
Duration
Completed Days
Remaining Days
End Date
4
5/05/14
3
=IF(TODAY()>=H4, E4,IF(TODAY()
In my attached sheet I've to find out final result in Column "F" (green shaded). My boss many times a year increases salary by certain percentage (say 30% or 40%) which is given in "E" column. After that I need to adjust it according to Pay Scale given in right side. For example one Executive's salary reaches 19880 after % increase. Now in pay scale we see in executive (row no. 13) it crosses 19800 so his final salary should be next upper value i.e 20600 (if it reached to 20700 after % increase, then his final salary could be 21400). That is final salary should be multiple of designation's increment plus minimum gross salary (Here 800X10=8000+11800=19800, since it exceed by 800, so salary should be 800X11=8800+11800=20600)
So we can either pull data from right side table or we can give formula in "F" column to get the required result.
salary adjustment.xlsx‎
Does anyone know an efficient formula for the “five-even” rounding rule? Someone asked me about this and gave me these examples:
6.6 must round to this ==> 7
6.5 must round to this ==> 6
5.5 must round to this ==> 6
8.5 must round to this ==> 8
9.5 must round to this ==> 10
66 must round to this ==> 70
65 must round to this ==> 60
55 must round to this ==> 60
85 must round to this ==> 80
95 must round to this ==> 100
Is there any way to create a cross-validation rule between 2 cells?
For example,
Cell A5 will have one of the following values (AA, BB, or AB) ....
Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.
View 3 Replies View RelatedI am using excell 2007 and am having a few problems with conditional formatting.
I am trying to do the following.
i have 6 cells in 6 columns ( c2, d2, e2, f2, g2, h2 ) each with figures 17.14+
Now i am trying to add a formula so when ever i insert a number in the cell below e.g c3 i want the cell c3 to turn green or red depending on the value in the above cell (c2).
so in all:
c2 has the value 17.14
if i insert the value 17.14 or less in cell c3 i want the c3 cell to turn green.
if i insert the value 17.15 or more i want the cell to turn red.
now i know how to do this in the conditional formatting feature but i want to do this will all the cells refering the the cell above.
i also do not want to create 6 diffrent conditional rules.
Is there a formula i can use that can be pasted into each cell and the cell number edited?
I have a table where Sales are in Column B and a % calculation is in Column E. I want to highlight the cells in green that are over 40% and that also meet the criteria where the sale in that row is above $100.
In conditional formatting I tried to use =and(b4>100,e4>40%) but it did not do anything. I am also okay with using a nested if statement.
What I need is for in the order start date column, I need a formula to pick out the first year where the value is inserted into the column and then insert the year into the order start date column. I would do this manually, but with thousands of rows a formula would prove to be most beneficial.
2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 Order start date
515 151 2013 200 2015 1515 2018 5454 2012
I have a project where if the user enters 'R' into cell H17 then cells B19:K19 are to be filled with black color. These cells are divided into three fields: B19:E19, F19:H19, and I19:K19.
When I select these three fields and use the conditional formatting tool the only cells that are black are B19:E19. Even though the rule explicitly shows that cells B19:K19 are to be affected. In order to have all three fields black I have to make three rules (one for each field).
The same behavior happens when I use the conditional formatting tool and select the cells from within the tool's dialog.
How can I get all three of the fields formatted with one rule?
Is it possible to create a rule that every time I type my name, for my e-mail address to pop up in excel instead?
For example, if I type "purple1686", I want "12purpleexample@domain.com" to show up as a hyperlink to my default e-mail program.
I am also curious if I was to write "my website" is it possible to make it a hyperlink to "www.purple1686example.com"?
Those two first may be easy to answer, so here is the challenging part for me:
Can I make that happen as a rule on every spreadsheet I open?
I don't know if I should make a template, or use a formula? The ideal scenario would be if there was a way to do it through conditional formatting.
I have used conditional formatting to change the color of a word every single time I open a new template and paste the words into it, or even by typing them out.
is there any rule for random number to be picked up? Any Rule, Any Formulae, Any Calculation Anything, Any authentic routine which may guess what would be the next random number? for example if first random number is 825587 then what would be the next?
View 4 Replies View RelatedI'm trying to find out the rule for de-duplicating data. I am removing duplicates based on an identification number in a data set of about 6000 records, including the duplicates (some records appear about 4 times). Due to the nature of the data I'm working with, there are only a handful of records that are "true" duplicates, i.e. some of the records appear 4 times but there is a difference in terms of location, etc and some are true duplicates in that there is no difference.
I need to know how Excel removes duplicates - does it only keep the first line that it finds for that identification number? Also, is there a way that I could create a rule for it to keep the record with the highest rate for example?
Want to make an if rule where a student gets overall 75% in a column, then in another column next to that that would automatically give him a B or a B+ you know what I mean?
Hear are the ratios
A+ - 90-100
A - 80-89
B+ - 75-79
B - 70-74
C+ - 65-69
C - 60-64
D+ - 55-59
D - 50-54
E+ - 45-49
E - 40-44
and another one
Excellent - 80-100
Very Good - 70-79
Good - 60-69
Moderate - 50-59
Borderline - 40-49
Is there a way to programatically count the amount of conditional formatting rules in a workbook as i have been given a workbook which performs really slowly and the amount of rules seem to be a lot just wanted to count them
View 5 Replies View RelatedIs there a way color a cell red if the the text is "Z" or a number between 0.5 and 10
Or if the cell contains a "Z" or a "C" ....
how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?
View 2 Replies View RelatedI want to set a conditional formatting rule that makes a cell red or green depending on if the value in the cell is larger or smaller than the value in another cell +2% or -3%. If neither of these conditions are met no formatting should be applied..
a) In the cell (R104) where the condition is set I have the following formula:
=if(BP104=0;"";BQ104/BP104) (the values in BP104 and BQ104 is retrieved from a database)
b) The conditions I've set in R104 are the following
1) =isblank($R$104) (condition: "nothing")
2) =$R$104<$R$106-0,02 (condition: "red")
3) =$R$104>$R$106+0,03 (condition: "green")
c) In cell R106 the value there is a value of 90%
The conditions seem to work fine when the value in R104 is higher or lower than the conditions set in 2 and 3 above but when the value is blank in R104 the cell goes green, which it shouldn't.
I have attached a sample file with two sheets.
Sheet 1 has two columns, 'A' represents a parcel number, and 'B' represents an indicator for the plat name in which the parcel number is located.
Sheet 2 also has two columns. 'A' represents the same indicator as Sheet 1 Column 'B' and Column 'B' is the formal name for the plat.
What I hope to achieve is Sheet 1 Column 'A' to be the parcel number (as it currently is)and Column 'B' to be the formal name for the plat (instead of the number).
As you will notice, the plat numbers on Sheet 1 Column 'B' can repeat many times. A plat number of '0' would mean it is not associated with a plat and therefore should return a blank.
The 'real' file has over 275,000 parcel numbers. All total, there are approximately 8800 differnent plat names.
Due to my 'lacking' skill level, I need it to be a macro as I have yet to learn other methods.
I want to find the correct formula to find answers using excel for the folliowing:
1. >3Sds
2. 3SDs<>2SDs
3. 2SDs<>1SD
4. <1SDs
This question uses 3 sigma rule (see attached worksheet) and the formula needs to be re-arranged to meet new criteria. So, we need to consider both sides of nornmal distributiion (ND) curve. eg. when we say >3SD it means it should count all valuee falling in the rage "Mean(Average)+3SD AND Mean-3SD" i.e two extreme ends of the ND cureve. I have inserted a normal distribution diagram and colour coded the ranges so that it will be easy to identify. Please refer comments inserted in K7to N7, cells that contain formula that need to be reset.
How do i make a formatting rule permanent to certain cell's.
The steps i followed:
I selected the columns and rows to be formatted. in conditional formatting I selected new rule. then selected the second function, "format only cells that contain" in format only cells with: specific Text , then " ending with" Over Issues.
in format i selected the font and fill.
I saved and close the document, after reopening the rule is no longer working.
For the first grouping of data (Ex 1) I need to do the following; if the value in column J is less than 49 then delete the data in the corresponding row from column E to K. For the second grouping (Flx 1) if the value in column R is less than 49 then delete the data in the corresponding row from column M to S. I would like to be able to do this for all groups all the way to group Flx 5. All my sheets are set out in this way but I am not sure if I can do this using a formula or a macro (which I am not very experienced at writing).
View 3 Replies View RelatedIs there a way to apply a custom validation rule to a drop down field in Excel (which itself is already using a validation rule)? Here is what I wish to do: I have a range of cells that can only pick up values from a list. I want to further apply a custom validation rule to these cells - display an error message when one chooses an element from the list that has already been used by another cell in the range.
View 2 Replies View RelatedHow to get rid of duplicate values in validation dropdown menu. I am using a range for the list that has a lot of repetitive values. I want to have only unique ones in the list.
View 9 Replies View RelatedI'm working on a time sheet, and to make it easier to enter time i created a macro that sets the auto correct to correct a "." to a ":", but when the workbook closes i want to run a macro that deletes the auto correct rule.
Im trying to find a formula that when a cell is empty ie this cell is linked to another cell on another worksheet and info has not been entered or is 0 then i want the cell to display the the current date ie =NOW()
How to plot circles via vb. Basically I would like to plot circles on the vertices of a triangle with the following data.
1) length a = 69, b=103, c=119.9967, angle between a and b =86.1. (These values are determined from the cosine rule)
2) Diameter D1=40,D2=98, D3=108
See link of circles : IMAG0176.jpg
I want to change the colour of rows depending on the letter that is in a column. I want it to do it over all the sheets of the document. In the G column there will be letters like 'W' and 'L' that signify something and I want to make it clear by changing the row colour fill. I've tried a few formulas I found by googling but they didn't work. I should point out that the letters are connected, so if I put a 'W' on the second sheet it appears on the first sheet as well.
View 2 Replies View RelatedAllows me to check like 10 different text values and return false for all of those in the conditional formatting rule. When those are not present, the conditional format is true thus applying the format.
I tried
=NOT(FIND("text1",$A$1,1))
It did not work because I believe this statement doesnt give the rule its TRUE value its looking for.
I am having trouble with this, to clarify I need the below...
Cell has conditional formatting checking for text1 text2 text3...text 10ish and if they are present nothing will happen and if they arent present then format the cell.
EDIT: to clarify, the code should look for either text1 or text2 or text3 so on...
I need to count the hours between dates eliminating the 48 hours for the weekend starting at 0:700 Saturday and recommencing at 0:700 Monday morning.
View 9 Replies View Related
