Formula to calculate a figure based on a RAG range?

[russell_piper]

Wonder if someone can help with this formula? Not sure if it is possible?

I have a threshold which is in columns H,I and J. The current monthly figure goes in the far right column (currently R) but this will be one further each month.

The score is caclulated by taking the score in R, and comparing it against the threshold in H, I and J. Two points are scored if it is red, 1 for amber and 0 for green. Column K is the score of the RAG (2,1 or 0) multiplied by the weight in column G.

Can this be done with formulas?

 

[russell_piper]

Anyone have any ideas? This one is really stumping me.

 

[pjmorris]

Hi, the simple answer is yes, but an example of your data will really help produce a solution.

Regards

 

[mole999]

have a read of this http://www.mrexcel.com/forum/excel-questions/862684-advice-please.html I needed a way to look at the last three complete columns to return a value I could RAG from, you might get something useful from there. Ricks Function worked really well for driving change arrows