# IF Statement and Negative Figures

#### AliGeeJay

##### New Member
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
ABCDE
1HeldUsedBalanceThreshold %Status
2871 Good
34201420100Good
4510-5 Check
51015-5 Monitor
Status
Cell Formulas
RangeFormula
D2:D5D2=IF(AND(C2/A2*100>10%,A2>100),C2/A2*100,"")

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
ABCDE
1HeldUsedBalanceThreshold %Status
2871 Good
34201420100Good
4510-5-100Check
51015-5-50Monitor
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=IF(OR(C2<0,AND(C2/A2*100>10%,A2>100)),C2/A2*100,"")

That has worked, many thanks @etaf

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

Replies
8
Views
347
Replies
6
Views
200
Replies
7
Views
85
Replies
1
Views
214
Replies
7
Views
302

1,203,681
Messages
6,056,714
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back