I am trying to do some work in Google Sheets. I have to generate a report that shows when 80% of users are reached for certain metrics. I am trying to set up a table with all the data and add conditional formatting to each column so that once the running sum of the column values reach 80% or more the cell where this occurs is highlighted.
For starters, is this even possible to achieve it really seems like it shouldn't be that hard. All i need to do is dynamically sum the values of a cell the ones above it in the column and if that value is 80% or more add the formatting.
I have tried using the Array, and Indirect and Sum functions but it never seems to properly apply to the specific cell, it either affects the entire column or just the last cell.
Example
So in Column C I should see highlighting at row 4, but nothing I do with the conditional formatting seems to make this work.
How can i input into a formula that for current cell sum this value all above & if value is >= 80% apply the formatting rule
I am really at a loss for the syntax to do this in Excel or Google sheets. If anyone has any suggestions its greatly appreciated. I am not used to working with these application so please let me know if you have any ideas as to how i can achieve this, if its even possible.
Cheers.
CodePudding user response：
try:
=(SUM(C$2:C2)>0.8)*(C2<>"")
CodePudding user response：
So Although @player0's answer did work, I found a solution that i think is just more human readable so I want to also include it as an answer for other in the future with some explanation since it took me a while to fully figure out how to set it up and properly understand all aspects of the function.
=ARRAYFORMULA( IF(sum(C2:$C$2) >= 0.8 , true))
The function arrayformula allows you to take a range of cells and define some operations that will be performed on the set and return a value. I used this formula as a custom conditional format rule. It simply states, for the array of cells to the current cell, if the running sum is greater than or equal to 0.8 (since the column is dealing with % values) return true, and when paired with the conditional format it will apply the formatting rule for all cells where the condition return true. For myself what was difficult was getting the relative values for the cell in each cell. the $ syntax when paired with the cell range defines it as relative to the current position meaning $C$2 can be variable based on where the formula is applied, C2 however is the constant start point for the formula evaluation
