# Less Than, Greater Than, Equal To Conditional Formatting

Jun 10, 2009
i'm trying to create a markscheme progress sheet.

You take a childs actual age in months and years (ie 9.8 for 9years and 8months). They then, in theory, should be at that age level for reading and spelling.

I have the age in B4, the reading in C4 and the spelling in D4. If i use CF to say i want C4 to be red if it's less than B4, Amber if it's the same and green if it's over, as soon as i okay it, C4 goes amber, because it's equal to B4, which is blank. I can get the colours to work, i just can't figure out what to put to turn the amber off when cell B4 is empty.

I've tried as many combinations as my little amount of excel knowledge can cope with.

View 9 Replies
ADVERTISEMENT
Jun 5, 2012

I tried using the following in Cond Formatting:

When the Cell in F10 does not equal w/a, I want the text to be BOLD. I tried this and it didn't work...

=$F10w/a

also tried

=$F10"w/a"

Neither worked.

View 1 Replies
View Related
Oct 8, 2008

In a job workbook, Row 51 is to be filled with reimbursable expenses every month, beginning in Column E and continuing through Z. The numbers that are entered in that row need to be marked up 10%. Is it possible to format the row in such a way that each number entered in each cell is automatically marked up 110%, or multiplied by 1.1?

View 5 Replies
View Related
Jul 23, 2009

I would like to use conditional formatting to say that if the value in A5 is greater than 50% of the sum of B5 and C5 then turn green.

View 3 Replies
View Related
Jun 23, 2011

I want to format a cell to change colors dependent on if it falls within a certain range from todays date.

If it's 60 days or less from todays date I want it to turn the cell red

If it's between 61 and 89 days I want the cell to turn orange

If it's between 90 and 105 days I want the cell to turn yellow

The current formulas I'm using are

RED Condition 1: =($F$1-TODAY())

View 2 Replies
View Related
Aug 21, 2014

This seems way too simple to do yet I can't get it right. It will highlight some that are different and not highlight others that are different. I need to compare column A to O and highlight differences, B to P and highlight difference, and so on.

View 2 Replies
View Related
Nov 14, 2006

I have a cell that I want changed to green (red 133 Green 180 Blue 0) if a line is considered active. As conditional formatting only allows 3 arguments, I need to perhaps code it?

Along the columns are cells that can change a row to active or inactive.

Here are my arguments.

If B5>1 .. row = active

If B5>1 & G5>1 .. row = inactive

If B5>1 & G5>1 & H5>1 .. row = active

If at any time L5>1 & M5="" .. row = inactive .. OR

If at any time Q5>1 & R5="" .. row = inactive .. BUT

If at any time L5>1 & M5>1 .. or .. Q5>1 & R5>1 .. row = active

then ..

If H5>1, M5>1, R5>1, T5>1 & AH5="", AJ5="", AL5="" .. row = active

so If at any time AH5>1 .. or .. AJ5>1 .. or .. AL5>1 .. row = inactive

View 6 Replies
View Related
Jul 9, 2012

I am trying to create conditional formatting to ascertain whether values in one table (Table 1) are different from another identically formatted table above it (Table 2) (so each cell in Table 1 has a corresponding cell in Table 2).

If the value in a cell in Table 1 is different from its corresponding cell in Table 2, the cell should highlight in red.

Every cell's corresponding cell is the same distance away.

I tried to put in a conditional formatting for C5 (C5 being the first cell in Table 2), which works, but this breaks and turns to #REF every time i save and re-open the document.

View 2 Replies
View Related
Oct 15, 2012

I have 3 rows with numbers across several columns, each cell holding one number only. For example:

R1: 1101 - 1102 - 1103 - 1104 - etc.

R2: 2101 - 2102 - 2103 - 2104 - etc.

R3: 3101 - 3102 - 3103 - 3104 - etc.

A little further down I am having a list where the end user will write down the exact same number. Once the same number has been written into the list, I would like the number shown in the top rows (row 1 to 3) to be highlighted in green, basically telling the user that the number has been entered into the list below and is ok now.

How can I manage this via conditional formatting?

View 3 Replies
View Related
Dec 15, 2009

how to create a conditional formatting formula that looks like this..

"If A1 is equal to DATA highlights greater than zero on cells A2 to A1000.

View 9 Replies
View Related
Apr 21, 2009

I have a cell, M87. The score in M87 can be less than 13 or greater than 25. I need a formula within M94 which refers to M87, and outputs depending on the the following criteria. If M87 is less than 13 then output as D. If M87 is 14, 15, 16, or 17 then output as C. If M87 is 18, 19, 20, 21, 22, 23 or 24 then output as B. If M87 is greater than 24 then output as A.

View 3 Replies
View Related
Aug 24, 2009

If A7 is equal to and greater than 95% than B7 is equal to "A". If A7 is equal to 91% but less than 94.99% than B7 is equal to "B"...

View 4 Replies
View Related
Jul 3, 2014

I'm trying to sum all values greater than 5,000,00 and less than or equal to 10,000,000. I have this formula:

Code:

=SUMIFS(B6:B1359,B6:B1359,">"&L2,B6:B1359,"=

View 5 Replies
View Related
Jan 2, 2009

I am looking for a function like MATCH if the match type were set to -1. However my data is sorted in ascending order. I am mining data from a Pivot Table, and it has dates across the top. Of course the pivot table will have the data sorted in ascending order from left to right. I want to find the first date that is greater than today. With weekends and holidays I can't just use TODAY()+1. Is there a function that can do what I am asking? Also I do not want to change the pivot table itself.

View 9 Replies
View Related
Jun 16, 2009

I need a formula that looks at the total in H40 and if the number is between 32 and 40 I need it to return the number then if the number exceds forty I need to multiply the overage by 1.5 and add it to the 8 for a total of 11.

I think it would be something like:

SUMIf(H40>"32",

View 9 Replies
View Related
Oct 14, 2006

I am creating my first Userform and having some problems. I take the data supplied by the userform and try to match it as closely as possible to a row of information. Currently I am using four cells to autofilter my spreadsheet data. Two of the cells I am looking for a exact match. The other two cells I am looking for the number that has been input or anything greater than it. Here is the code I have come up with...

View 9 Replies
View Related
Dec 2, 2009

How can I count the number of cells in a range that contain values that are greater than or equal to zero?

View 2 Replies
View Related
Oct 29, 2009

I have start date(Column A) and an end date(Column B) in two columns and I have found out the difference between them in column C in hh:mm:ss format. I want to find out how many cases are greater than 2 Hrs but less than or equal to 4 Hrs.

View 9 Replies
View Related
Jul 12, 2012

i have table of values if any value in the coulomb G and I greater than or equal to 1 the entire row must be deleted how can i do this ?

View 4 Replies
View Related
May 14, 2009

Starting in cell F3, if Column F is greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Then go to Column F in that same row that had contents in Column A, if the contents in that row of Column F are greater than or equal to zero, delete the entire row and continue deleting rows until Column A has contents in it. Persist with this pattern until every row in Column F has been checked.

View 4 Replies
View Related
Jan 13, 2014

Need a formula for counting how many consecutive cells where the value is gretaer than or equal to +1

For example 1,

J18 = 7

K18 = 9

L18 = 3

M18 = 2

N18 = 8

O18 = 1

P18 = 8

Q18 = -17

In the above example the result would be 7

Example 2:

J12 = -22

K12 = 9

L12 = 4

In the above example the result would be 0 since the 1st value was -22

View 3 Replies
View Related
Sep 21, 2009

I am revising a spreadsheet to automate some cells and size cable for my job. I am currently using the VLOOKUP command to search a table for the proper ampacity and return a cable size based on the minimum circuit amps. The problem however is the VLOOKUP command searches for a number equal to or less than my circuit amps and returns this cable size. I need it to return a size equal to or greater than the minimum circuit amps. How do I get it to lookup something equal to or greater than the minimum amount?

View 9 Replies
View Related
May 4, 2009

I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?

View 3 Replies
View Related
Dec 15, 2011

I want to display a warning pop up message when the following cells (Cell A1:A10) is greater and equal to 3.

I would like some code in VBA

View 6 Replies
View Related
Mar 8, 2007

In my Excel spreadsheet I enter todays date in a single cell (A2), then I list various dates that jobs come into shop in other cells (A8:A108). I have cells ( F8:F108) where I have been manually entering an asterik (*) for those jobs equal to or greater than five days old in cell (A2). Is there a formula that can do the math for me? I've tried Excel help but to no avail.

View 3 Replies
View Related
Aug 24, 2006

I have what may be a math question as much as an Excel question. See my attached spreadsheet example in which I grow the denominator of a ratio by a fixed percentage until it equals or exceeds the numerator. This results in the denominator being grown X number of times.

View 2 Replies
View Related
Jan 17, 2013

I'd like all Cells in column AC (e.g. AC$3$:AC$517$) to be filled with yellow fill if the cell value is any date greater than or equal to today. Any past dates can be left blank (for now)

=$AC3>=TODAY()

View 3 Replies
View Related
Nov 13, 2013

SUMIFS with less than & more than

I found this thread which works for normal ranges

I want to Sum for Values Greater than a specific date in my formula.

This first one works for a specific date (Relevant reference in red), so say 01/01/2014

Code:

=SUMIFS(Table2[[#All],[Basic]],

Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],

Table2[[#All],[Start Date]],Table3[#Headers],

Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

Code:

To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.

=SUMIFS(Table2[[#All],[Basic]],

Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],

">="Table2[[#All],[Start Date]],Table3[#Headers],

Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

View 4 Replies
View Related
May 13, 2009

Is there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values?

As in this for example:

Pieces of Paper (Blue)

I would like to keep the formatting for (Blue) , color and bold, but I have not figured out how to do this.

View 12 Replies
View Related
Jun 28, 2006

This seems simple...

If I let A1=8

If I fill in the cell green, either manually or with conditional formatting, and then copy and paste the cell into A2, A2 is now green with the number 8 inside.

However, if in A2 I type "=A1", A2 still has the 8 but is no longer green.

Is there a way to copy the formatting of a cell by setting it equal to the original?

View 3 Replies
View Related