a formula for counting a specific word in one cell "Trial" and converting it to a percentage

[khollibygolli]

in Google Sheets, In one cell A3, the amount of trials that were positive shows up ( Trial 1, Trial 2, Trial 3, Trial 4, Trial 5 or none). I would like the formula to count how many times the word "Trial" is in the cell and convert it to a percentage in B3 and copy it down through the column as new information is added.
Ex. nothing in cell = 0% / Trial 4 = 20% / Trial 1, Trial 3 = 40% / Trial 3, Trial 4, Trial 5 = 60% / Trial 1, Trial 2, Trial 3, Trial 4, Trial 5 = 100%


I don't know if it would matter, but there is another column C that lists the failed Trials, so idk if that would be needed in the formula to do the percentage

 

[DanteAmor]

I have no way to test in google sheet, but try the following:

varios 28jun2023.xlsm
	A	B
1
2	Trial 1, Trial 2, Trial 3, Trial 4, Trial 5	100%
3	Trial 1, Trial 2, Trial 3, Trial 4	80%
4	Trial 1, Trial 2, Trial 3	60%
5	Trial 1, Trial 2	40%
6	Trial 1	20%
7		0%
Hoja9

Cell Formulas
Range	Formula
B2:B7	B2	=(LEN(A2)-LEN(SUBSTITUTE(A2,"Trial","")))/LEN("Trial")/5

 

[khollibygolli]

It worked!!! Thank you so very much!

 

[khollibygolli]

I have no way to test in google sheet, but try the following:

varios 28jun2023.xlsm
	A	B
1
2	Trial 1, Trial 2, Trial 3, Trial 4, Trial 5	100%
3	Trial 1, Trial 2, Trial 3, Trial 4	80%
4	Trial 1, Trial 2, Trial 3	60%
5	Trial 1, Trial 2	40%
6	Trial 1	20%
7		0%
Hoja9

Cell Formulas
Range	Formula
B2:B7	B2	=(LEN(A2)-LEN(SUBSTITUTE(A2,"Trial","")))/LEN("Trial")/5

I have another question.
i would like to add to this formula
my data automatically updates so i would like for the formula to update with it but not show 0% all the way down the column
i actually had to change the formula to B instead of A(i gave the wrong columns) and I know I can't attach it to column B since sometimes the info is blank and needs to be 0%
But if i could attach it to whether or not column A has info in it, then the formula will show, that would work for me.

 

[DanteAmor]

i actually had to change the formula to B instead of A(i gave the wrong columns) and I know I can't attach it to column B since sometimes the info is blank and needs to be 0%
But if i could attach it to whether or not column A has info in it, then the formula will show, that would work for me.

Try this:

=IF(A2="","",(LEN(B2)-LEN(SUBSTITUTE(B2,"Trial","")))/LEN("Trial")/5)

 

[khollibygolli]

OK I changed the formula to this so that it would go all the way down and stick even with new rows added...
=ARRAYFORMULA(IF(A2:A="","",(LEN(F2:F)-LEN(SUBSTITUTE(F2:F,"Trial","")))/LEN("Trial")/5))
But now i would like to move this formula to a different sheet tab and pull that info from Column A & F on my Sheet tab called 'Sheet Responses 1'
I can't figure out how to conjoin it all i guess