Here's how it looks.

Hi. I'll try to be as specific as possible about this :)

I'm making a Google Sheet page similarly to a game character progression, that will automatically fill up based on data from other sheets: specifically, these "EXP" bars fill up with values based on the hidden cell A5.

For instance, the cells from D5 to BA5 represent LV1.

As the function shows, every time the value in A5 reaches an even number (for LV1, it's all even numbers from 2 to 100), one of the slots will fill up with a number, that with conditional formatting, will change the color of the cell and make it look like an actual exp bar filling up.

I'm using the function =COUNTIF(A5,">=2") and manually changing it to ">=4", ">=6" etc until ">=100" for the first bar. The second bar will have its 50 cells with all even numbers from ">=102" to ">=200".

So, to get to the question: is there an array formula that facilitates the process of filling a great amount of these bars with functions, without changing them all manually? Even automating *some *of this process would be great. Besides it being tedious, it leaves a lot of room for human error.

Thank you in advance to whoever's got an answer. P

I haven't tried much as at the moment I am very confused by the vast amount of options array formulas provide. I have 48h of experience in the matter.

CodePudding user response：

I suggest you to use the values of columns and rows here as helpers to do calculations. If you know that every column adds 2 and every row adds 100 you can set a formula like this for the whole range (select the whole range and add just one rule of conditional formatting):

=$A$5>((Column(D5)-3)*2 (Row(D5)-5)*100))

You use $symbol to make A5 steady and D5 value will "move" to each cell for conditional formatting, so you don't have to make an ARRAYFORMULA for this