I have this formula that I've been working on - not sure if I am even remotely in the ballpark of how it should be configured and need another persons eye with more formula experience. Here is the background data.
I am trying to say if cell H15,J15,L15,N15,P15 all equality 100% or above keep H15,J15,L15,N15,P15 the same. If any any of these cells equal below 100% all cells over 100% should equal 100% and the cell below 100% should show the actual.
Am I even making sense? I used =IF(H15>100%,TRUE) in line 20 for each cell and then used the below formula based on those results.
=IF($H20&$J20&$L20&$N20&$P20=TRUE,H15,IF($H20&$J20&$L20&$N20&$P20=FALSE,100%,IF(H20=FALSE,H15,1)))
This formula works for if I have at least one cell False. The problem is if all cells are True it is not pulling the actual number.
Any ideas? I included the template I've been working on.
Bookmarks