Forum Discussion
Solved
Number and Goal Visual
Hello - I am working on a table with 2 numbers - the first number is the current score (column A) and the second number is ideal score (column B). I would like to show a graph (similar to the ph...
- no you can't use relative references but you CAN use FIXED numbers so you can set min = 0 and max = 1 and then in K use the formula =G4/I4 (or if that is a table =[Current]/[Desired] or using dynamic arrays =G4:G6/I4:I6)
Then also click "Show Bar Only"
ligg_1 So in the attached I did 2 tricks using conditional formatting:
a) using the data bar formatting you can select those 2 cells and it will fill a bar graph based on the value in that cell compared to the max value. And then I used a second conditional formatting to just override the first in that second cell (see row 5 in the picture/file)
b) that is a pain because you will need 2 rules for each line so another option I give uses the cells to the right and highlights cells based on the values in those cells and for that I created only 2 rules that cover ALL the rows.
BTW, you could also use a large fixed number of columns (e.g. 100) and make the columns very narrow (i.e. for better 'resolution') and then use INT(A1/B1) as the count of number of cells to highlight.