weiging average vs concentration

Can you please help me how to create graph lie the following


Y axis is: weighted average
X axis: Percent reduction change at optical density 450 nm

I have uploaded an excel file, %reduction was already calculated (X axis), not sure or would like to learn how to calculate Weighted Average

Greatly appreciate your help

Not much to go on. I would expect to:

1) Enter/calculate the precent reduction change in one column.
2) Enter/calculate the weighted average in the adjacent column (to the right).
3) Select the 2 columns and insert an XY scatter chart.
4) Any formatting I want to apply.

Usually, I expect a weighted average to be calculated with something like =SUMPRODUCT(values,weights)/SUM(weights), but I don't see any data in your sample sheet that is identified as "values" or "weights". If you help us understand how you want to calculate the weighted average for these data, we should be able to help translate that sequence of calculations into an Excel formula.

From PM

I thank you so much for your response, I am not sure how I can explain, I am looking similar to figure 2 of the above article

referencing an article from the journal Clinical and Diagnostic Laboratory Immunology How similar to Figure 2? Figure 2 is a basic semi-log plot (in Excel, a standard XY scatter chart with one axis formatted to have a logarithmic scale).

Their chart also includes a trendline for which they calculate r. I don't see where they describe how they performed the regression or calculated r, so I would assume it is a basic, linear least squares regression (semi-log plot, so I expect that the regression equation is log(y)=mx+b or y=antilog(b)antilog(mx)). Excel can easily perform these kinds of regressions using the LINEST() function or the chart's trendline feature.

Are you also intending to calculate weighted average the same way they do (described in the Data Analysis paragraph of the Materials and Methods section)? They are calculating WA as =10^(SUMPRODUCT(percent reductions OD,LOG10(NaSCN concentrations))/SUM(percent reductions OD)) where I am assuming their antilog is base 10. If you are intending to use this same WA calculation, I don't see where you are storing your percent reduction calculations nor your LOG10(NaSCN) calculations. If you are not intending to use the same weighted average, what are you intending to use?

In your sample data, the SCN concentration is always 1.5 (normal?). When the SCN concentration is constant, the percent reduction sums cancel, and the WA formula returns the SCN concentration. Using this formula for WA, it appears that you would need to have data at multiple SCN concentrations in order to get a meaningful calculation. I don't know molecular biology/immunology, so I can't speak very well to the design of such an experiment, but it looks like the authors of the paper would have had multiple OD450 values at multiple (they mention 8) NaSCN concentrations, and it appears they put that information into the WA formula.

At this point, we are straying out of Excel related questions into immunology/molecular biology/biochemistry experiment design and analysis questions, and I'm not sure how much help I can be. I don't know if any of our other regulars here have experience with these experiments that could be more helpful. On the off chance that someone here can be more helpful, if you are allowed to post it publicly, you might consider attaching the link to the Clinical and Diagnostic Laboratory Immunology that you shared with me in the PM. Someone with more expertise in that field might be able to make better sense of the experiments being described in the paper than I can.

Your latest file does not include a chart, so I cannot know what you tried or why it is not similar to their chart.

Assuming the WA calculation in column AA is correct (and it looks correct to me, though I would have used SUM() instead of AVERAGE() in columns H and Y), then it appears like you should just need to choose a % reduction at one of the concentrations (columns I:L) to use for the X values and the WA column (column AA) for the Y values and insert a scatter chart. The paper used NaSCN concentration of 0.15 M for their concentration. Your study does not appear to include a 0.15 M concentration. I don't understand how they chose which concentration to focus on (seems related to Fig 1, but I did not follow their explanation). You would need to perform a similar analysis (or whatever procedure you want to use to choose the reference concentration). Then select that column (for example J4:J35 if you choose 1 M), press ctrl and select AA4:AA35 -> Insert XY scatter chart. Format the Y axis so it uses a logarithmic scale and you should have a similar chart to their Fig 2.

Is that what you are looking for?