Please help with creating a formula

[Fernandez30]

Can somebody please help me creating a formula with the below conditions, I have also attached the excel file I am working on. I did create one on my own but it doesnt work =IF(AND(J5>25%,J9<=80%),"RED"),IF(AND(J5>=25%,J9>90%),"Amber"),IF(AND(J5>=25%,J9>=90%),"Green")

Below are the conditions I need to work with and I have also attached the excel sheet.

RED​	Inflow number higher than Threshold number by 25% and if SLA < 80%, this it should be "RED"
AMBER​	Inflow number higher than Threshold but less than 25% and SLA< 90% and >=80%, then Amber
GREEN​	Inflow number less than or equal to Threshold number and SLA>= 90%, then Green​

 

[p45cal]

The way I do it if it gets complicated is to use baby steps; in the attached, on Sheet1 (2) I've scrubbed the irrelevant data and formulae.
In column C I've used words to describe what that row is supposed to show.
For RED in rows 14 & 15 I've put simple formuale to show TRUE/FALSE according to your conditions.
In row 16 another simple formula to put them together and in row 17, the formula in row 16 translated to look directly at the source data.

I've done similar for AMBER and GREEN below that.

Then in row 33, I've put together a simple formula to give you a final colour, and in row 34, that formula translated only to look at the source data (that way once you're satisfied it's giving the correct results you'll be able to delete ALL the rows 14:33).

Now it's up to you to adjust the simple formulae to give correct results before translating to the single bigger formula, step by step.

 

[Fernandez30]

The way I do it if it gets complicated is to use baby steps; in the attached, on Sheet1 (2) I've scrubbed the irrelevant data and formulae.
In column C I've used words to describe what that row is supposed to show.
For RED in rows 14 & 15 I've put simple formuale to show TRUE/FALSE according to your conditions.
In row 16 another simple formula to put them together and in row 17, the formula in row 16 translated to look directly at the source data.

I've done similar for AMBER and GREEN below that.

Then in row 33, I've put together a simple formula to give you a final colour, and in row 34, that formula translated only to look at the source data (that way once you're satisfied it's giving the correct results you'll be able to delete ALL the rows 14:33).

Now it's up to you to adjust the simple formulae to give correct results before translating to the single bigger formula, step by step.

I just cant thank you enough for helping me with this, from the bottom of my heart a big THANK YOU
I would like to understand what is 1.25 that you have multiplied (inflow>thresh*1.25). If its is 25% shouldnt it be 0.25.

 

[p45cal]

25% would be one quarter of the threshold.
I guessed you meant 25% above the threshold - was I wrong?

If the threshold was 100, would 25 be 25% above it?
I thought you were interested in it going 25% (25) above 100, aka 125 aka 125% aka 1.25

 

[p45cal]

Your reply seems to have disappeared.
If the threshhold is simply 25%, not 125% then make the necessary alteration to the simple formula in rows 14 and 20 and cascade that through the other formulae.
I don't understand this, because the logic which contributes to making it red (inflow > 25% of the threshold) can also make it green (inflow <= threshold):
Column K, your data:
Threshold is 16, inflow is 16.
25% of threshold is 4.
inflow (15) > 4 => red
inflow is <= threshold => green.

Anyway, you can see how using the small steps to get things right leads to getting the final formula right.

 

[Fernandez30]

Affirmative!
You are correct in saying that it has to be 1.25.

Thanks a ton for all your advice and help. Truly appreciate it.