Conditional Formatting based on 2 conditions

[sunilbsrv2k]

Hi all,

I have a table visual as below:

Metric	Forecast	Target	Actual
A	70%​	70%​	80%​
B		1000​	900​
C	5%​		6%

I have to give conditional formatting for "Actual" column...

however, the issue here is ... if Metric is "A", and Actual is greater than Target, it should be have Green up arrow... if its less, should be red down arrow

if Metric is C, and Actual is less than Target/Forecast, it should be Green up arrow... if its greater then Red down arrow...

So, the formatting is dependent on Metric and the corresponding Actual value against Forecast/Target.

Could you please help me in achieving this.

Thanks
Sunil B.S

 

[etaf]

you are also showing a B in metric - and no conditions
how many different metrics are there and what are the rules for each
What columns are we using and how many rows?
No Target for C, shown in example
Should be able to use a Rule with AND () for each condition
AND( A2="A", C2>B2) = green arrow
AND( A2="A", C2<B2) = RED arrow
What happens if Actual = Target ?
BUT may need an IF in the column with the arrows

Try using the add-in XL2BB as shown in my signature and on the menu - would help with showing sample spreadsheets here

 

[etaf]

using ICON Sets
and a formula in E

=IF(OR(AND(A2="C",D2<C2),AND(A2="A", D2>C2)),1,0)

ICONS dont show in XL2BB as far as i can make so here is a screen shot too

Book3
	A	B	C	D	E
1	Metric	Forecast	Target	Actual
2	A	70%	70%	80%
3	B		1000	900
4	C	5%		6%
5	A		50%	40%
6	A		40%	50%
7	C		40%	50%
8	C		50%	40%
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IF(OR(AND(A2="C",D2<C2),AND(A2="A", D2>C2)),1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E10	Other Type	Icon set		NO

 

[sunilbsrv2k]

Hi Thanks, so much... Howevwer, I am working in Power BI... is this possible in Power BI?

 

[etaf]

oh dear, sorry missed the forum you are in, unfortunately my version of excel for Mac does not include Power BI, so not used