Forum Discussion
Number and Goal Visual
- 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.
Thanks, mtarler. I tried to go with the option pictured below but I don't think that you can simply copy the conditional formatting and have the reference be the row intended (ideally the last row in the sample which shows current and desired both at 1 would be fully greyed out). Is there a way to do this or would I need to update each individually?
- 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"
- 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.
