Over functions don't work now. Help

[Steve Cohen]

I have a statement that looks at the text entries in Column G and if it says
Risking takes the dollar amount that follows Risking and enters it in the
same Row in Column A and if it says To Win takes the dollar amount that
follows To Win and enters it in the same Row in Column B.

This is working fine, but now that there are no actual numbera in colums A
or B any function that I have that refers to those cells will not work,
because it is not seeing the actual dollor amount it is looking at a
statement that is referring to a text cell.

For example another woorksheet has a function in it that is the following.
=SUM(SUMIF('Bet Jamaica'!F:F,{"*BET*"},'Bet Jamaica'!A:A))

This refers to colum F on the Bet Jamaica worksheet and if the cell displays
BET then it looks at that cell in colum A and pulls the amount bet.

How can this be corrected?

 

[Katherine Coombs]

Hi Steve,

You haven't posted the formulae that are contained in columns A and B, which
would be helpful. I'll take a stab and suggest changing the formula in
column A such that if Column G says "Risking" then it takes the dollar
amount that follows Risking, otherwise it's equal to "0". Use a similar
function in Column B, but for "To Win". That way, there's always a
numerical amount in the cells in Columns A and B.

HTH,
Katherine

 

[Arvi Laanemets]

Hi

Probably the formulas in columns A and B (MID()?) are returning string
values. Embedd them into VALUE() function to get numbers returned instead.

 

[Steve Cohen]

Katherine;

The formulas are

in A1 enter
=IF(ISERROR(SEARCH("risking",$G1)),"",MID(G1,SEARCH("risking",$G1)+8,SEARCH(
"to win",$G1)-SEARCH("risking",$G1)-8))

in B1 enter
=LEFT(K1,FIND(" ",K1))

and in C1 enter
=MID(G1,SEARCH("to win",$G1)+7,LEN(G1))

The only problem I had with this, and it was minor, was that if thee was no
entry in G the B & K would have a #VALUE in them.

Like I said this was minor and I could live with it because colums A B & K
were hidden.

Thanks;

Steve

 

[Arvi Laanemets]

Hi


=IF(OR(G1="",ISERROR(SEARCH("risking",$G1))),"",VALUE(MID(G1,SEARCH("risking
",$G1)+8,SEARCH("to win",$G1)-SEARCH("risking",$G1)-8)))
=VALUE(LEFT(K1,FIND(" ",K1)))
=IF(G1="","",VALUE(MID(G1,SEARCH("to win",$G1)+7,LEN(G1))))

 

[Steve Cohen]

Arvi;

I tried what you have below and now I am getting a #VALUE in colums A & B.

Steve

 

[Arvi Laanemets]

Hi

Can you give some examples of data in column G?
I tested it with string 'risking 9 to win 999' - 9 and 999 are returned. But
I'm getting #VALUE error when the string is p.e. 'risking9 to win999' or
'risking 9 to win 999' (a double space between 'to' and 'win' in last one).
The parts of string you are searching for MUST BE EXACT SAME!

 

[Steve Cohen]

Arvi;

Thanks; your suggestion worked. I just needed to step away from it for a
while and attack it again with a clear head.

Also the VALUE needed to be left out of =VALUE(LEFT(K1,FIND(" ",K1)))
It works great like this =LEFT(K1,FIND(" ",K1))

Thanks;

Steve