Entering edit mode

12 weeks ago

sankadinesh
▴
20

I want to highlight cells/remove rows, which contain less than 5 counts in all the samples. How to do this ?

https://www.dropbox.com/s/96pw1vldjar2biv/sample.xlsx?dl=0

Regards, Dinesh

In example file, OTU 8 and OTU 9 values are wrong between right and left data. To filter rows with less than 5 in any one of the sample (for the example excel sheet), do following

`=IF(COUNTIF(B2:F2,"<5")<5,1,0)`

Example sheet has 5 samples (columns) and 10 OTUs (rows)

Hi there, Thanks a lot. Your suggestion worked like a charm. Can you please tell me what does 5,1,0 mean in the formula '=IF(COUNTIF(B2:F2,"<=5")<5,1,0)'

Regards, Dinesh

`=IF(COUNTIF(B2:F2,"<5")<5,1,0)`

is a combination of two formulas.`IF`

condition works like this:`IF (condition, value if condition is true, value if condition is not true)`

. In above case, IF condition validates output from`countif`

. If output from`countif`

less than 5, then value is 1, if not, it not it's 0.`countif`

counts number of cells with cell value less than 5. In this context,`countif`

outputs number of samples (columns) with less than 5 reads, for that OTU (row).Logic is for any given OTU, count of samples with less than 5 reads, should not be equal to number of samples (in this case 5 - used in

`IF`

condition). If they are equal, all the samples for that OTU, have less than 5 reads.HI there, One quick question, I want to highlight

OTUs that contain less than <5 count across the samples.If one OTU has more than 5 counts in one sample and 0 in all other samples, I want to keep that OTU, .I am attaching the modified excel again with original data. The formula given has worked for the sample dataset but not for large data. Is there any reason ? If so, any alternatives can you please suggest. Thanks for spending your valuable time.https://www.dropbox.com/s/96pw1vldjar2biv/sample_modified.xlsx?dl=0

Regards, Dinesh

Samples are 42 and example set has 5. You have used the same formula (used for 5 sample data), for a 42 sample data. For 42 samples, formula would be:

`=IF(COUNTIF(B2:AQ2,"<5")<42,1,0)`

.I have added formula and conditionally formatted with color here: https://docs.google.com/spreadsheets/d/1EpmEyPopj7T2ndDmH0e-lze6PaPyXcnl/edit?usp=sharing&ouid=116988544834045239536&rtpof=true&sd=true. (download the file, do not open with google sheets)