# =IF Formula Help

#### D3383

##### New Member
Hi All,

Hope you all well and safe,

I have a query I am hoping you can assist with, I want to create a formula that will show text based on the value of another cell, which is formatted with a custom number as such £#,##0.00;[Red]-£#,##0.00;-

Basically, I need to show the following text " Threshold" if the custom value is -£, example being A1= Theshold if B1 has the -£ value in it.

I hope that makes sense and thank you for your assistance in advance.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Saba Sabaratnam

##### Active Member
I am not sure if I understand your requirement 100%. However, you could get use the following approach if it solves your problem.

Enter the following formula in B4 and copy it down

=IF(A4>\$B\$1,"Thershold","")

I made your threshold value as variable so that you can change it from, say, 1000 to 1200 without adjusting the above formula.

Kind regards

Saba

#### D3383

##### New Member
Thanks Saba,

I probably didn't explain my query clearly, hopefully the attached picture will help.

I have a formula in column AP which calculates the NI annual cost, based on a number of assumptions.

If these assumptions are not meet, the value is shown as a negative value, i.e -£378.65, when this happens, I would like column AN to auto input the text Threshold.

#### Attachments

• Threshold.png
37.4 KB · Views: 8

#### Sufiyan97

##### Active Member
Try

Excel Formula:
``=IF(AP1<0,"Theshold","")``

#### D3383

##### New Member

Try

Excel Formula:
``=IF(AP1<0,"Theshold","")``

Thanks, I've tried that but keep getting the circular references prompt.

#### Sufiyan97

##### Active Member
If these assumptions are not meet, the value is shown as a negative value, i.e -£378.65, when this happens, I would like column AN to auto input the text Threshold.
In which cell will you have negative values and in which cell you want result as Theresold?

#### Sufiyan97

##### Active Member

For me it's working perfect..

Book1
ANAOAP
1 0
2Theshold-1
3 1
Sheet1
Cell Formulas
RangeFormula
AN1:AN3AN1=IF(AP1<0,"Theshold","")

#### Joe4

What is in cell AP1?
Is it a formula?
If so, what exactly is that formula?

#### Saba Sabaratnam

##### Active Member
Hi,

The solution from A Durfani should work.

A circular reference refers to a formula, that visits its own or another cell more than once in its chain of calculations, creating an infinite loop. It means Cells in AN are used by formulas in AP. For example, AN1 is used by formula in AP1.

You could check and remove it if the reference is not needed. Or if it does not affect your calculation, you could allow circular reference by If circular reference. Please see below.

Also I noticed a dropdown list in your picture in column AN. If so, allow formula in it (probably you have already have it) by unchecking the Show Error check box as shown below.

Kind regards

Saba

Replies
0
Views
45
Replies
4
Views
138
Replies
3
Views
104
Replies
7
Views
82
Replies
3
Views
271

1,136,797
Messages
5,677,789
Members
419,720
Latest member
kurman

### 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.

### Which adblocker are you using?

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

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