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.
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
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".
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.
I 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.
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?
I'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
I've recently got a new computer and this problem is driving me nuts. When I click view!toolbars!drawing, nothing happens. No drawing toolbar appears at the bottom of the screen where it used to be. There's a small "drawing toolbar" icon on the main toolbar, but whether this is depressed or not makes no difference either.
I understand this may be changed in the 2007 version, but I’m using Excel 2000
I want to have 2 cells where I can enter 2 numbers (length & height) so that a grid can be plotted. For example, a big sheet of paper size is length 30" x height 8", if first number is 10" (length) is entered, it will plot 3 lines across 30" (cos 30" divided by 10" equals to 3) and if I enter 2" (height) on 8", it will then cut it into 4". Attached a file for better understanding.
I want to use VBA to update a drawing object's color, such as a circle.
For example, pretend I am counting sheep and I need to know when I have reached the maximum count by the visual aide of a stoplight. The script in my head reads: If the max. count of the sheep is less than 5, then the stoplight is green. If the max. count of the sheep is equal to 5, then the stoplight is yellow. If the max. count of the sheep is greater than 5, then the stoplight is red.
Is there a way to do such a thing on excel and with VBA?
How would i have VBA draw a line between a two ranges? say i have a named range of "start" and another name range "stop". i want to draw a red line between them. how can i do that with code. this is beggining my education for a much larger project I want to do later in the future.
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?
I 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.
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).
is there a code to draw a circle? by supplying the center position and the diameter? for example cell "A1" and " A2" are for the center of the circle position and "A3" for the diameter? and whats the code for drawing a rectangular by supplying the starting position and short length and the long length?
Any way to retrieve values from a combobox on a webpage (For my case there are two values in the combobox.) I know how to manipulate the webpage to choose the values in the combobox and so on but i want my user to be able to see the options they have from my userform.
I am having trouble writing a macro that will draw a line relative to what cell is currently selected. I can only get the macro to draw a line in one specific spot.
I have a sheet with 30 textboxes made with the drawing toolbar. The user enters data, then clicks on a button which formats the page to adjust row size to match amount of info. I am having trouble finding the code to store the currently selected textbox in a variable so that I can place the focus back to the same textbox when the macro ends. Can anyone help me with the code to get the active textbox?
