0

I know this question looks absurd.

Here is what i want to do. i have a formula field which is a number datatype. I just want to show the value as 'N/A' if the value if a field rate_type='ABC'.

IF( Rate_Type__c == 'ABC', Quantity , 'N/A')

This gives out an error

Error: Incorrect parameter type for function 'IF()'. Expected Number, received Text

Is there a way to do this?

Update:

Is there a way we can keep the formatting of a number in Text formula field?

  • Insteaad of 'N/A' you can try -1 for same. – Tushar Sharma Jun 10 '16 at 8:48
  • What does -1 do? – Prady Jun 10 '16 at 8:56
  • it will do nothing. It is like saying 'N/A'. normally we use < 0 value to show not found – Tushar Sharma Jun 10 '16 at 9:02
  • Answer is no. I suggest instead return type why don't you just use return type text. In this case you can simply return Quantity or 'N/A' – Ratan Paul Jun 10 '16 at 9:14
  • @Ratan The only problem is that i would loose the formatting – Prady Jun 10 '16 at 9:27
1

No, the Number datatype is purely numeric. You could set a Null value or 0 but not a text string to a Number field.

The only other way is to have a formula field returning a text value.

  • Could we have the formatting of a number into Text formula field – Prady Jun 10 '16 at 8:49
  • Yes, you could calculate a number then use TEXT() to turn it into a text result for display. – Dave Humm Jun 10 '16 at 8:59
0

i think i have an answer for this

Thanks to the post https://success.salesforce.com/answers?id=90630000000gpInAAI

IF( Rate_Type__c != 'ABC', 'N/A' ,   IF(Quantity < 0, "(", "") & 
IF(ABS(Quantity ) >= 1000000, TEXT(FLOOR(ABS(Quantity ) / 1000000)) & ",", "") &
IF(ABS(Quantity ) >= 1000, RIGHT(TEXT(FLOOR(ABS(Quantity ) / 1000)), 3) & ",", "") &
RIGHT(TEXT(FLOOR(ABS(Quantity ))), 3) & "." &
IF(MOD(ABS(Quantity ) , 1) * 100 < 10, "0" & TEXT(ROUND(MOD(ABS(Quantity ) , 1), 2) * 100), TEXT(MIN(ROUND(MOD(ABS(Quantity ) , 1), 2) * 100, 99))) &
IF(Quantity < 0, ")", ""))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.