#VALUE error in nested IF statements

[Marg]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I'm trying to use the following nested IF formula to extract a number from a text cell (an eBay listing summary which includes the currency conversion rate used, which is what I'm after), and then print that number.

=IF(FIND(A,B17),A,(IF(FIND(B,B17),B,(IF(FIND(CC,B17),CC,(IF(FIND(D,B17),D,(IF(FIND(E,B17),E,(IF(FIND(F,B17),F,(IF(FIND(G,B17),G,(IF(FIND(H,B17),H,(IF(FIND(I,B17),I," &quot))))))))))))))))

Conditional formatting, as suggested, identifies the cells so that I can then type and copy the number in the column where it needs to go in the spreadsheet, but it just seems that I ought to be able to figure out a formula which finds the number and prints in in the right place all in one swell foop.

The A, B, and C etc are named cells containing the various numbers being searched for. The formula recognizes them, it is "correct" in its syntax, but the result printed only the first number (A, or 1.06090) in the desired cells, and returned #VALUE for everything else. That was defined as:

"A value used in the formula is of the wrong data type"

Can anyone see where this is going off the rails?

Help will definitely be appreciated!

Marg

 

[Carl Witthoft]

It would help if you could describe what you really want the formula to
do. In general, nesting a dozen IF's is bad, and you can almost
certainly get the desired function using a LOOKUP function or a CHOOSE
function.

In addition, you are using IF (FIND()) in a very risky way. Remember,
FIND returns a number (or #VALUE if the string isn't found). IF
expects a logical result. You should use IF(ISNUMBER(FIND(A,B17)))...
..

You may be a lot better off, if the text in the cell is more or less
the same in every cell, using one of the text functions to strip the
text string, leaving just the number behind.

 

[Marg]

Hi, Carl,

Thanks very much. What I'm trying to do is extract the currency conversion rate from a cell which contains all the information about the sale of an item, e.g.

TITLE OF ITEM WHICH CAN BE OF VARYING LENGTH WITH WORDS AND NUMBERS;Final price: US $9.95 (Auction), final value fee of US $0.87 converted to C $0.92 at a rate of 1.05100 (as of 29-Nov-09).

I use the "1.05100" as a surrogate for converting the US price to Canadian dollars. There will be 300-400 listings per monthly billing, and usually about 8 or 9 different conversion rates.

Using the conditional formatting approach beats eyeballing each listing, and shortens the time required to extract the numbers and to paste them (accurately!) in a separate column. I just want to figure out a formula which will not only identify the cells in which a given number is found, but also to insert this number in its own column.

I hope that's clearer. I don't think a text string would work, because the preceding text varies in length. Would CHOOSE or a LOOKUP work?

Thanks,

Marg

 

[Bob Greenblatt]

Hi, Carl,

Thanks very much. What I'm trying to do is extract the currency conversion
rate from a cell which contains all the information about the sale of an item,
e.g.

TITLE OF ITEM WHICH CAN BE OF VARYING LENGTH WITH WORDS AND NUMBERS;Final
price: US $9.95 (Auction), final value fee of US $0.87 converted to C $0.92 at
a rate of 1.05100 (as of 29-Nov-09).

I use the "1.05100" as a surrogate for converting the US price to Canadian
dollars. There will be 300-400 listings per monthly billing, and usually
about 8 or 9 different conversion rates.

Using the conditional formatting approach beats eyeballing each listing, and
shortens the time required to extract the numbers and to paste them
(accurately!) in a separate column. I just want to figure out a formula which
will not only identify the cells in which a given number is found, but also to
insert this number in its own column.

I hope that's clearer. I don't think a text string would work, because the
preceding text varies in length. Would CHOOSE or a LOOKUP work?

Thanks,

Marg

Is the text above representative of the text you are searching? Can you rely
on the presence of the word ³rate² or ³(as of²? If so, assuming the text is
in A1, the formula to extract the conversion rate would be something like:
=mid(a1,search(a1,²rate of ³,1)+7,7))