Please help me with this please thank you:)The 1st image is the Data. AG1 Microorganism2 Methanobacterium3 Bacteroidales4 Porphyromonadaceae 0.1602023615 WCHB1-056 Dehalococcoides7 Clostridiales8 Sedimentibacter9 Christensenellaceae10 CoprococcusPFAA-1PFAA-2PFAA-3No-1No-2No-30.0026863410.0033857280.0050590220.0000330.0016863410.0033726810.0033726810.008431703 0.010118044 0.0151770660.1180438450.106239460.030354132 0.042158516 0.0489038790 0.0016863410.0000250.0050590220.0016863410.0033726810.0050590220.053962901 0.062394604 0.0657672850.0016863410.0033726810.008431703 0.0050590220.0927487350.0455311970.052276560.059021922 0.059021922 0.0657672850.0016863410.0033726810.008431703 0.0050590220.0033726810.0033726810.001686341O 0.0016863410.003372681 0.005059022 0.0050590220 0.001686341 0.00505902211 Ruminococcaceae0.0067453630.00337268112 Oscillospira0.00168634113 Veillonellaceae0.2596964590.2799325460.3288364250.222596965 0.225969646 0.24957841514 Desulfovibrionaceae0.0573355820.0387858350.026981450.02529511 0.028667791 0.02866779115 Desulfovibrio0.0067453630.0016863410.0067453630.005059022 0.001686341 0.01349072516 Treponema0.0640809440.0607082630.0674536260.175379427 0.121416526 0.1332209111718 Procedure1. The data is located in a separate text file here . Copy and paste it from word into the first sheet of an exceldocument. Rename that sheet to "Data"2. If the data did not automàtically get separated into columns, use Data-Text to Columns, and separate by comma.3. Open a second sheet, rename it "Statistics"A. Column A should be titled "Microorganism". Link the microorganism names by using Paste Special - Paste Linkand populate this column.B. Column B should be titled "Mean - PFAAS". Calculate the average of PFAA 1, PFAA_2, and PFAA 3 for eachmicroorganism with Excel's built-in mean function referencing the PFAA-1, PFAA-2, and PFAA-3 columns.C. Column C should be titled "Mean - No PFAAS". Calculate the mean of the samples without PFAAS with Excel'sbuilt-in mean function referencing the No-1, No-2, and No-3 columns for each microorganism.D. Column D should be titled "Variance - PFAAS". Calculate the variance of the samples for each microorganism.This is similar to step 3b, but using Excel's built-in function for variance.E. Title Column E "Variance - No PFAAS". Calculate the variance of the samples without PFAAS for eachmicroorganism as in 3c, but using Excel's built-in function for variance.F. Create a column titled "F-value" for the ratio of (variancearge)2/ (variancesmall), as in, the larger variance alwayshas to be on the top. Hint: use an if-statement in Excel to determine which variance is larger and place it in thenumerator.4. Check if variances are considered equal or unequalA. Using an F-test (details elsewhere) we know that our F-statistic is 19. If our calculated F-value from 3f is smallerthan 19, our variance is considered equal. You may create an additional column called "Var Equal?" that displaystext "var equal" or "var unequal" by checking if the F-value is greater or less than 19.B. Create another column titled "T-test Type". Assign a value of 2 to the microorganisms with equal variance, and avalue of 3 for unequal variance.5. Calculate t-test statistic, PA. Create a column titled "P-value"B. Perform the Excel's built in function for a "t.test". We will be using 2 tails. For the "type" use the number fromstep 4b.C. Use "Highlight Cell Rules" to highlight any microorganisms that are statistically different (i.e. have a p-value fromstep 5b) less than 0.05. A P value of 0.05 or less is considered a significant difference. For example, if Treponemahas a P value of 0.0385, there is a statistically significant difference between Treponema exposed to PFAAS andTreponema without PFAAS. This means PFAAS significantly affect Treponema!6. Create plots of the average relative abundances for each microorganism, with and without PFAA. Create a stackedbar chart with "With PFAAS" and "Without PFAAS" on the x-axis and average relative abundance on the y-axis. Thismeans that there will be two columns, and each microorganism will correspond to a specific color. Place it in its ownproperly labeled worksheet. Appropriately format the plot. See Figure 1.7. Create a different type of plot of your choice with the same data. Place it in its own properly labeled worksheet, andappropriately label the plot.8. Create a new sheet in your Excel file titled "Questions". Answer each of these questions by typing the answers incells A1, A2, A3, and A4. Adjust the column width and row height so that all of the text can be seen.1. Consider the following sentence from the abstract of the paper referenced in this exercise:"Notably, there was significant repression of Dehalococcoides (8-fold decrease in abundance) coupled with acorresponding enhancement of methane-generating Archaea (a 9-fold increase) [upon exposure to PFAAS)."Note that "Archaea" refers to Methanobacterium in our data. Does your analysis support this conclusion?2. We chose a p-value of 0.05 to establish significance. What happens if this threshold is higher? Smaller? Do youthink a p-value of 0.05 truly represents significance?3. Why do you think some microorganisms would be impacted by PFAA presence while others did not change? Ifyou have no experience in microbiology, don't worry. This is an open question that researchers are still trying toanswer, so take a shot!4. Think about the two plots you generated. Which one does a better job of showing how the averages changeupon exposure to PFAAS? Can you think of a better way to show this interplay? If you have a better way, create anew plot on a new sheet.

Question

Please help me with this please thank you:)The 1st image is the Data. AG1 Microorganism2 Methanobacterium3 Bacteroidales4 Porphyromonadaceae 0.1602023615 WCHB1-056 Dehalococcoides7 Clostridiales8 Sedimentibacter9 Christensenellaceae10 CoprococcusPFAA-1PFAA-2PFAA-3No-1No-2No-30.0026863410.0033857280.0050590220.0000330.0016863410.0033726810.0033726810.008431703 0.010118044 0.0151770660.1180438450.106239460.030354132 0.042158516 0.0489038790 0.0016863410.0000250.0050590220.0016863410.0033726810.0050590220.053962901 0.062394604 0.0657672850.0016863410.0033726810.008431703 0.0050590220.0927487350.0455311970.052276560.059021922 0.059021922 0.0657672850.0016863410.0033726810.008431703 0.0050590220.0033726810.0033726810.001686341O 0.0016863410.003372681 0.005059022 0.0050590220 0.001686341 0.00505902211 Ruminococcaceae0.0067453630.00337268112 Oscillospira0.00168634113 Veillonellaceae0.2596964590.2799325460.3288364250.222596965 0.225969646 0.24957841514 Desulfovibrionaceae0.0573355820.0387858350.026981450.02529511 0.028667791 0.02866779115 Desulfovibrio0.0067453630.0016863410.0067453630.005059022 0.001686341 0.01349072516 Treponema0.0640809440.0607082630.0674536260.175379427 0.121416526 0.1332209111718 Procedure1. The data is located in a separate text file here . Copy and paste it from word into the first sheet of an exceldocument. Rename that sheet to &#34;Data&#34;2. If the data did not automàtically get separated into columns, use Data->Text to Columns, and separate by comma.3. Open a second sheet, rename it &#34;Statistics&#34;A. Column A should be titled &#34;Microorganism&#34;. Link the microorganism names by using Paste Special -> Paste Linkand populate this column.B. Column B should be titled &#34;Mean - PFAAS&#34;. Calculate the average of PFAA 1, PFAA_2, and PFAA 3 for eachmicroorganism with Excel's built-in mean function referencing the PFAA-1, PFAA-2, and PFAA-3 columns.C. Column C should be titled &#34;Mean - No PFAAS&#34;. Calculate the mean of the samples without PFAAS with Excel'sbuilt-in mean function referencing the No-1, No-2, and No-3 columns for each microorganism.D. Column D should be titled &#34;Variance - PFAAS&#34;. Calculate the variance of the samples for each microorganism.This is similar to step 3b, but using Excel's built-in function for variance.E. Title Column E &#34;Variance - No PFAAS&#34;. Calculate the variance of the samples without PFAAS for eachmicroorganism as in 3c, but using Excel's built-in function for variance.F. Create a column titled &#34;F-value&#34; for the ratio of (variancearge)2/ (variancesmall), as in, the larger variance alwayshas to be on the top. Hint: use an if-statement in Excel to determine which variance is larger and place it in thenumerator.4. Check if variances are considered equal or unequalA. Using an F-test (details elsewhere) we know that our F-statistic is 19. If our calculated F-value from 3f is smallerthan 19, our variance is considered equal. You may create an additional column called &#34;Var Equal?&#34; that displaystext &#34;var equal&#34; or &#34;var unequal&#34; by checking if the F-value is greater or less than 19.B. Create another column titled &#34;T-test Type&#34;. Assign a value of 2 to the microorganisms with equal variance, and avalue of 3 for unequal variance.5. Calculate t-test statistic, PA. Create a column titled &#34;P-value&#34;B. Perform the Excel's built in function for a &#34;t.test&#34;. We will be using 2 tails. For the &#34;type&#34; use the number fromstep 4b.C. Use &#34;Highlight Cell Rules&#34; to highlight any microorganisms that are statistically different (i.e. have a p-value fromstep 5b) less than 0.05. A P value of 0.05 or less is considered a significant difference. For example, if Treponemahas a P value of 0.0385, there is a statistically significant difference between Treponema exposed to PFAAS andTreponema without PFAAS. This means PFAAS significantly affect Treponema!6. Create plots of the average relative abundances for each microorganism, with and without PFAA. Create a stackedbar chart with &#34;With PFAAS&#34; and &#34;Without PFAAS&#34; on the x-axis and average relative abundance on the y-axis. Thismeans that there will be two columns, and each microorganism will correspond to a specific color. Place it in its ownproperly labeled worksheet. Appropriately format the plot. See Figure 1.7. Create a different type of plot of your choice with the same data. Place it in its own properly labeled worksheet, andappropriately label the plot.8. Create a new sheet in your Excel file titled &#34;Questions&#34;. Answer each of these questions by typing the answers incells A1, A2, A3, and A4. Adjust the column width and row height so that all of the text can be seen.1. Consider the following sentence from the abstract of the paper referenced in this exercise:&#34;Notably, there was significant repression of Dehalococcoides (8-fold decrease in abundance) coupled with acorresponding enhancement of methane-generating Archaea (a 9-fold increase) [upon exposure to PFAAS).&#34;Note that &#34;Archaea&#34; refers to Methanobacterium in our data. Does your analysis support this conclusion?2. We chose a p-value of 0.05 to establish significance. What happens if this threshold is higher? Smaller? Do youthink a p-value of 0.05 truly represents significance?3. Why do you think some microorganisms would be impacted by PFAA presence while others did not change? Ifyou have no experience in microbiology, don't worry. This is an open question that researchers are still trying toanswer, so take a shot!4. Think about the two plots you generated. Which one does a better job of showing how the averages changeupon exposure to PFAAS? Can you think of a better way to show this interplay? If you have a better way, create anew plot on a new sheet.

Accepted Answer

“Since you have posted a question with multiple sub-parts, we will solve first two sub-parts for…

1. The data is located in a separate text file here . Copy and paste it from word into the first sheet of an excel document. Rename that sheet to "Data" 2. If the data did not automàtically get separated into columns, use Data->Text to Columns, and separate by comma. 3. Open a second chont