=IF Formula Help

[D3383]

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.

 

[Saba Sabaratnam]

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]

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.

 

[Sufiyan97]

Try

=IF(AP1<0,"Theshold","")

 

[D3383]

Try

=IF(AP1<0,"Theshold","")

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

 

[Sufiyan97]

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?


Can you post sample of your data using below link?

 

[Sufiyan97]

For me it's working perfect..

Book1
	AN	AO	AP
1			0
2	Theshold		-1
3			1
Sheet1

Cell Formulas
Range	Formula
AN1:AN3	AN1	=IF(AP1<0,"Theshold","")

 

[Joe4]

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

 

[Saba Sabaratnam]

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