Corresponding Value Drawing According To Rule

Oct 29, 2013

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‎

View 7 Replies


ADVERTISEMENT

“Five-even” Rounding Rule

Jun 18, 2009

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

View 9 Replies View Related

Looking For A Rule To Find Weekends

Jan 31, 2014

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

View 9 Replies View Related

Cross-Validation Rule

Jan 12, 2009

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

View 11 Replies View Related

IF Rule: Another Cell To State N/a

Jul 8, 2009

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 Related

Duplicate Rule Without Pasting

Dec 22, 2009

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?

View 9 Replies View Related

Conditional Format With More Than One Rule

Aug 29, 2013

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.

View 3 Replies View Related

VLookup Function Or IF Rule?

Jan 4, 2012

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

View 8 Replies View Related

Get All Three Of Fields Formatted With One Rule?

Sep 6, 2013

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?

View 2 Replies View Related

Turning Name Into Email Address As A Rule

May 20, 2014

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.

View 1 Replies View Related

Rule For Random Number To Be Picked Up?

Jun 24, 2014

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 Related

Rule For Removing Duplicates In Data?

Jun 10, 2014

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?

View 3 Replies View Related

Create If Rule Of % Into Text Mark A B C

Mar 14, 2008

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

View 11 Replies View Related

How To Count Conditional Formatting Rule

Feb 15, 2013

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 Related

Cant See Drawing Toolbar

May 22, 2007

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

View 10 Replies View Related

Drawing Grid

Nov 26, 2007

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.

View 14 Replies View Related

VBA Drawing Objects

Jul 15, 2008

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?

View 9 Replies View Related

Drawing A Line With VBA

Jun 22, 2009

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.

View 9 Replies View Related

Multiple Criteria Per Conditional Formatting Rule

Sep 9, 2009

Is 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" ....

View 13 Replies View Related

Multiple Criteria Per Conditional Formatting Rule..

Sep 18, 2009

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 Related

Set A Conditional Formatting Rule That Makes A Cell Red?

Jan 9, 2014

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.

View 6 Replies View Related

Multiple 'if-thens' (Chain Rule) With Two Sheets

Feb 4, 2010

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.

View 12 Replies View Related

Formula For Three Sigma Rule Of Standard Deviation

Sep 20, 2009

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.

View 2 Replies View Related

How To Make Formatting Rule Permanent To Cell

Apr 21, 2014

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.

View 5 Replies View Related

Rule To Delete Data From Adjacent Cells

Feb 5, 2008

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 Related

Drawing Circle And Rectangular Using Vba?

Sep 15, 2014

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?

View 6 Replies View Related

Drawing Circles And Other Shapes?

Jun 16, 2013

I want to draw circles from 3 points and other geometrical shapes. The points will be given from clicks on the background picture.

View 1 Replies View Related

Drawing ComboBox From IE For Use In Excel VBA

Jun 18, 2013

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.

View 8 Replies View Related

Drawing Lines With Macro

Jun 4, 2007

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.

View 9 Replies View Related

How Can I Tell Which DRAWING Textbox Is Active

Sep 26, 2008

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?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved