Displaying 1 of 3 pictures based on a dynamic number

[MDSlom]

I’m trying to create a report on excel that produces a red light, yellow light, or green light (all pictures) based on if an inputted number is lower, equal to, or higher than a dynamic number (which steadily increases week after week).So far, I have been able to produce this with a static number, by associating the green light with all numbers below the static number, the yellow light with the same number, and the red light with numbers above the static number. I literally typed out the list of numbers and pasted the 3 pictures repeatedly with their corresponding numbers.I’m finding it difficult to explain over text. Please let me know if you need more information.I’d like to stick with formulas if possible. Since I’ve gotten to the point where everything works with a static number, I imagine it is just a little more formula work to using a dynamic #.

The formula posted below is what I used for the static number. I replaced the ranges and cell selection with a description.

=INDEX(range with traffic light pictures,MATCH(cell with number that is either higher, lower, or equal to static number,range with numbers corresponding with the traffic light pictures ,0))

​

 

[Perpa]

MDSlom,
Welcome to the Forum. If I understood correctly...
I assumed the static number to be in cell B1 and the dynamic number to be in cell C1. You didn't say how those numbers are determined so I assumed they are entered manually, or by another formula.

The formula below will provide the Picture Name, ie. 'PictureGreen', 'PictureYellow', or 'PictureRed' depending on the criteria. You can change the picture names, the criteria for each color (I used a value in C1), and the cell where the static number is located to suit. In this example if the Static number is LESS THAN C1 then 'PictureGreen' is selected...If the static number is EQUAL TO the value in C1 then 'PictureYellow' is selected...OTHERWISE 'PictureRed' is selected.

For some reason (unknown to me) the formula keeps being truncated to the following...not sure why. Perhaps a Forum Monitor can tell us why. Sorry for the inconvience...never had this happen before.
'E1 =if(B1...'<c1,"picturegreen",if(b1=c1,"pictureyellow","picturered"))


Perpa

Sheet1

	A	B	C	D	E
		PictureYellow

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]100
[/TD]
[TD="align: right"]100
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]4
[/TD]

</tbody>

</c1,"picturegreen",if(b1=c1,"pictureyellow","picturered"))

 

[Perpa]

Here is a link to imgur.com with a picture of the above post which includes the formula I provided:

Perpa