Hey,
I am trying to set up an auto RAG for a speadsheet which automatically populates it's data from a database.
Currently I am using this formula / layout
A BC DE F
Project NameProject IDActual CostsLatest ForecastVariance
SavingsH344.9406.261.3AThe amber shaded cell has the formula: =IF(C16="H",IF((G17/B17)>0.79,"G",IF((G17/B17)>0.59,"A","R")),IF(SQRT(F16*F16)<10000,"G",IF(AND(E16=0,E16<>D16),"R",IF(SQRT((F16/E16)*(F16/E16))<0.05,"G",IF(SQRT((F16/E16)*(F16/E16))>0.1,"R","A")))))
Which basically checks to see if it is a subtotal or a data cell,
then if its a data cell checks to see if it meets the criteria for Red Green or Amber, This criteria is based on a % varience.
However If it is a subtotal cell the RAG criteria needs to be based on the number of projects in the section / number of green projects.
So in the line below the sub totals I have used CountA to count the number of none blank cells (B17) & countif="G" (G17)to count the number of Green projects. Which all works fine, however the data cells are autopopulated and so the range will change & due to the way that the software works the formula will copy from the subtotal cell upwards automatically.
How do I make the range only include projects in this section? - Baring in mind the number of projects will change and there are several sections stacked on top of each other.
Thanks in advance
I am trying to set up an auto RAG for a speadsheet which automatically populates it's data from a database.
Currently I am using this formula / layout
A BC DE F
Project NameProject IDActual CostsLatest ForecastVariance
SavingsH344.9406.261.3AThe amber shaded cell has the formula: =IF(C16="H",IF((G17/B17)>0.79,"G",IF((G17/B17)>0.59,"A","R")),IF(SQRT(F16*F16)<10000,"G",IF(AND(E16=0,E16<>D16),"R",IF(SQRT((F16/E16)*(F16/E16))<0.05,"G",IF(SQRT((F16/E16)*(F16/E16))>0.1,"R","A")))))
Which basically checks to see if it is a subtotal or a data cell,
then if its a data cell checks to see if it meets the criteria for Red Green or Amber, This criteria is based on a % varience.
However If it is a subtotal cell the RAG criteria needs to be based on the number of projects in the section / number of green projects.
So in the line below the sub totals I have used CountA to count the number of none blank cells (B17) & countif="G" (G17)to count the number of Green projects. Which all works fine, however the data cells are autopopulated and so the range will change & due to the way that the software works the formula will copy from the subtotal cell upwards automatically.
How do I make the range only include projects in this section? - Baring in mind the number of projects will change and there are several sections stacked on top of each other.
Thanks in advance