I am trying to write an “IF” statement in an Adagio Fx worksheet that would look at the account cell and utilize the text within the account cell to create a calculation -the worksheet is a smartsheet, and when it is rolled down the accounts show up in the format
4000- 30_500
The leading spaces are created within Adagio Fx, and the account code consists of the Account # (4000), the department mask (30) and the sub account code (500).
I want the IF statement to make a calculation based on the dept. mask (ie 30, 20, 10) – in the calculation, D27 = base amount, E8 is a fixed conversion rate, and B27 is where the account code show above is.
I’ve tried getting this calculation in a cell on the rolled up sheet as follows;
IF(ISERROR(FIND(“30_”,B27,1))==”TRUE”,D27*E$8,D27)
And also
IF(ISERROR(FIND(“30_???”,B27,1))==”TRUE”,D27*E$8,D27)
In all cases, when the worksheet rolls down, although there are “30_??? Accounts, it does not perform the calculation required, which is to multiply the base (d27) times the exchange rate (E8), or if not dept 30, then to return the base amount in the cell.
I can insert a new column, and use a text string such as aaaabbb30_cccc, and then a text string aaaabbbbcccc (no 30) and re-reference the formula to these test names, and the formula then works.
Can anyone help me as to why the formula cannot find the “30” accounts in the account code? I have also tried ensuring that the account code column was formatted as text so that the formula “30” should find it. I am wondering if it is an issue related to the roll-down on the sheet.

Hello Carole,

The ISERROR() function returns TRUE() or FALSE() depending if the argument passed to it returns an error condition. It does not return the text "TRUE". The =IF(cond,DoIfTrue,DoIfFalse) statement expects a condition that returns TRUE() or FALSE() to test.

The formula you want needs to be written:

Your example is a bit complicated but

IF(ISERROR(FIND(“30_”,B27,1))==”TRUE”,D27*E$8,D27)

what I see is the "TRUE" is not correct and the 1 following B27 is suspect.

Try:

IF(ISERROR(FIND(“30_”,B27,1))==TRUE(),D27*E$8,D27)

or

IF(ISERROR(FIND(“30_”,B27,0))==TRUE(),D27*E$8,D27)

as the first character position of B27 is 0 not 1

Thanks Andrew I will try rewriting the formula and will test it on the worksheet. As always love the quick response in the forum.