Changing string of character into a formula

[Wark]

Hello,

I am currently working on a file from the Census 2000 which consists on a list of the most popular surname in the US, and the distribution of their race (6 different output (columns)) (roughly 150.000 data lines). Unfortunately, when the probability is very small, the cell contains a "(S)".

For the continuation of my work, I need to change those (S) -that are let's say randomly dispatched all the way through- by a probability. I want to compute this probability to be (1-(sum of cells that doesn't contain (S)in the row))/(numbers of cells that contain (S) in the row).

By example, if we have for a given name the following distribution (columns) : 1,21% (S) 93,31% (S) 4,45% 0,85%
I would like, for this row, to change the (S) by (1-(0.9331+0.0121+0.0445+0.0085))/2 = 0.0009

Since the (S) could be in any row, and there could be from 1 to 5 of them in each, I have absolutely no clue how to compute a formula to apply to this huge data file.

Thank you very much for your help

Regards.

[p24leclerc]

I don't think we can come up with a formula to do this. A VBA macro would be the way to go. Is this a possible solution for you?

[Wark]

I haven't used VBA macros since college and it was very basic, but google is my friend, so that could work !

I believe I have to create some kind of loop that check row by row for (S) values, then count the number of values in the row, then apply the formula and replace?

not sure how to do that...

thank you

[gak67]

For each row you could have =(1-SUMIF(A1:F1,"<>S"))/COUNTIF(A1:F1,"S"). Assuming that formula was in G1:G150000 you would then need to recreate the data, maybe in column H, so in H1 you would have =IF(A1="S",$G1,A1). Drag that across to column M (you said there were 6 columns) and down (all 150k data lines). Then copy, paste special, values over the original data and delete columns H-M. Adjust columns to suit your actual data.

[Wark]

Thank you very much, it worked !
I just had to change =(1-SUM(B2:G2))/COUNTIF(B2:G2,"(S)") instead of =(1-SUMIF(A1:F1,"(S)"))/COUNTIF(A1:F1,"(S)")

don't really know why it didn't work at first, but I got it done. Thank you