=IF Formula Help

D3383

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 26, 2018
Messages
392
Office Version
  1. 365
  2. 2010
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.
1624051109257.png


Kind regards

Saba
 

D3383

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
    Threshold.png
    37.4 KB · Views: 8

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
335
Office Version
  1. 2013
Platform
  1. Windows
Try

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

D3383

New Member
Joined
Jun 18, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try

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

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

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
335
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Apr 12, 2019
Messages
335
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
What is in cell AP1?
Is it a formula?
If so, what exactly is that formula?
 

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
392
Office Version
  1. 365
  2. 2010
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.

1624315626183.png


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.

1624316039257.png


Kind regards

Saba
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top