Dynamic Dashboard

[GN0001]

Does anybody know how this formula inserts Green Arrow or Red Arrow?


IF(F2>PERCENTILE(F$2:F$51,Green_2),"◄+",IF(F2<PERCENTILE(F$2:F$51,Red_2),"◄-",""))


The top function is coming from Chandoo's dynamic dashboard in ch6Data Sheet. This is the link to the mentioned dashboard.


http://chandoo.org/wp/2010/03/16/excel-dashboard-tutorial-1/


Your help is greatly appreciated.


Regards,

Guity

 

[vijaySharma]

Guity,


If you have the workbook downloaded already; look for the Conditional Formatting rules that have been set for these cell.


~Vijay

 

[GN0001]

Hi Vijay,

There are some rules and please give me sometime to go through it and I will be back.

Thx,

Guity

 

[GN0001]

Vijay,

This formula:


IF(F2>PERCENTILE(F$2:F$51,Green_2),"◄+",IF(F2<PERCENTILE(F$2:F$51,Red_2),"◄-",""))


is entered in a data sheet and there is no conditional formatting rule on that sheet as far as I see. I don't understand whey Percentile function has taken Green_2 for a argument which needs to be between 0 to 1.


Regards,

Guity

 

[Hui]

Guity

Goto the Number Format Ctrl 1

What does it say in the Sample Box on the Number Tab

 

[GN0001]

Hi Hui,

There are two sheets: Input data sheet and chart sheet.

When I checked the chart sheet I understood why the arrow turns green or red. It has utilized conditional formatting.


This part of percentile function that needs to take a value, takes a string.


IF(B2>PERCENTILE(B$2:B$51,Green_2),"◄+",IF(B2<PERCENTILE(B$2:B$51,Red_2),"◄-",""))


Takes either Green_2 or Red_2.


Green_2 or Red_2 should be a number between 0 to 1.

Regards,

Guity

 

[Hui]

Green_2 & Red_2 ae named ranges not text

Goto Formulas Named Ranges and see what it says

 

[GN0001]

Hui,


They are named ranges: 0.90 and 0.10. Now, it makes sense. I can't understand why are these values are entered in G24 and G25 as well. I will continue to work on this.


There are some values on the right hand side of sheet(Input Ch6 datasheet) in R, S, T, U, V, W. Are they outsource data ?


Thank you very much,

Guity

 

[GN0001]