Forum Discussion
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 photo below) in column C that has the current score and a line (or visual) for the ideal score.
I am also open to creating a graph elsewhere and referencing it with an =CELL formula, but I do not think this is possible to do with a graph.
Thanks in advance!
- 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"
6 Replies
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?
- 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.
