IF Statement and Negative Figures

[AliGeeJay]

I am having issues with an IF statement and cells that contain negative figure.

The IF statement I have at the moment in Column D is: =IF(AND(C3/A3*100>10%,A3>100),C3/A3*100,"")

When the formula comes to cell - for example C5 - with a negative figure, I need the formula to return the negative figure instead of a blank cell. I have tried putting C5 instead of the "" for Value_if_FALSE

The other challenge I have is that if C5 is a negative figure, but cell E5 says monitor, then it needs to carry out the IF statement above.

I hope my ramblings make sense as I have been trying to sort this one out for over a day now, and getting a brain ache from trawling the internet looking for a solution to this.

Sample.xlsx
	A	B	C	D	E
1	Held	Used	Balance	Threshold %	Status
2	8	7	1		Good
3	420	1	420	100	Good
4	5	10	-5		Check
5	10	15	-5		Monitor
Status

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(C2/A2*100>10%,A2>100),C2/A2*100,"")

 

[etaf]

you could add an OR to your AND
=IF(OR(C2<0,AND(C2/A2*100>10%,A2>100)),C2/A2*100,"")

The other challenge I have is that if C5 is a negative figure, but cell E5 says monitor, then it needs to carry out the IF statement above.

Does that mean IF C5 is negative AND E5 is monitor , then you want a blank Cell
as the IF
AND(C2/A2*100>10%,A2>100)
will be false

Book4
	A	B	C	D	E
1	Held	Used	Balance	Threshold %	Status
2	8	7	1		Good
3	420	1	420	100	Good
4	5	10	-5	-100	Check
5	10	15	-5	-50	Monitor
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=IF(OR(C2<0,AND(C2/A2*100>10%,A2>100)),C2/A2*100,"")

 

[AliGeeJay]

That has worked, many thanks @etaf

 

[etaf]

your welcome
BUT what about
Does that mean IF C5 is negative AND E5 is monitor , then you want a blank Cell
as the IF
AND(C2/A2*100>10%,A2>100)
will be false