## New answers tagged conditional-formatting

0

If others come across this issue, you can kind-of use relative referencing... at least this is faster than setting up the rule for each cell or column manually. First do one cell (you must only choose one cell), and use INDIRECT to reference the value, e.g. to relative reference a column value inside the conditional rule (or could use rows if needed, ...

1

You can use a formula like this in column B
=IF(A1>=PERCENTILE($A$1:$A$10,0.4),1,0)
Depending on your specific data, you may need >= or just > - you can determine that when working with the data.

2

Here’s a tricky way (that might be considered elegant).
I’ll assume that < 83 is the “good” condition. Use this formula:
=CHOOSE((D1<83)+(F1<83)+1, "bad", "neutral", "good")
Logical expressions like D1<83 evaluate to one of the Boolean values
TRUE and FALSE, which are numerically equal to 1 and 0, respectively.
By adding ...

2

Well, I made a quick attempt and this and came up with a rough version that works. It probably isn't the best so hopefully someone responds with a more elegant solution, but try this:
=IF(AND($B$2<=$B$1,$B$3<=$B$1),"Good",IF(AND($B$3>$B$1,$B$2>$B$1),"Bad", "Neutral"))
B1 = 0.83 (just my value for budget)
B2 = YTD value entered by user
B3 = MTD value ...

0

Try using an OR statement, e.g. =OR(Sheet2!A1=Sheet2!$A7) I haven't tested this, but I use OR or AND statements often in these types of situations. It really doesn't matter if you use OR or AND, but for single-statement logic I prefer OR. It's just a preference.

0

Not sure if this works in pivot tables, but for normal use-
Select the range of interest and note the starting cell (e.g. A1)
Alt+O, D or however you like to open the conditional formatting dialog ;)
Add condition
Choose "Formula"
Enter =A1=MAX(A1:A65536) as the formula (MAX(A:A) may also work but I haven't tried it). If the starting cell isn't in column ...

0

Type in the system size limits in cells A37 to A42, as in:
3
4
5
6
8
10
Then =VLOOKUP(D24;$A$37:$B$42;2;1) will pick out the relevant system cost factor from B37 to B42.
Check the Excel help for VLOOKUP() function for understanding the innards of this.
Save this as "filename.csv", then OPEN it in Excel or LibreOffice:
Sys Size,,,Cost factor
...

0

This is the non-macro way.
=IF((A1>=3) AND (A1<=3.99),B37,IF((A1>=4) AND (A1<=4.99),B38,IF((A1>=5) AND (A1<=5.99),B39,IF((A1>=6) AND (A1<=7.99),B40,IF((A1>=8) AND (A1<=9.99),B41,IF((A1>=10),B42,"not found"))))))
You can put formulas in place of b38 through b42

0

This should work when you paste it in Developer -> Visual Basic, under the sheet you're working in. Make sure to change the range.
Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("A1:B2")
If Application.Intersect(rng1, rng2) Is Not Nothing Then
Dim Min, Max, Index As Double
Dim Red, Green As Integer
Dim Cell ...

0

How about INDEX and MATCH?
Tutorial:
http://www.mrexcel.com/articles/excel-vlookup-index-match.php
You can do a MATCH in one column and use that in the INDEX function for the other columns in the same row. That way you only lookup the row once.
You would basically have two tables on top of each other. The top one showing values aligned with table 1 that ...

Top 50 recent answers are included