I have a table of multiple ratios in Excel, e.g. 3:1, 4:1, 16:4, etc. How do I work out the average ratio from these ratios?

When I setup a ratio formula e.g. =(D14/E14)&":"&(E14/E14) it returns the ratio for the numbers queried in this example of cells D14 & E14. I'm getting stuck though working out the average of all the ratios returned. I can't query the ratio cells and get the average ratio. If that makes sense to anyone!


closed as off-topic by JMoravitz, Parcly Taxel, Andrei, user99914, Claude Leibovici Oct 20 '17 at 4:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question is not about mathematics, within the scope defined in the help center." – JMoravitz, Andrei, Claude Leibovici
If this question can be reworded to fit the rules in the help center, please edit the question.

  • $\begingroup$ Before you worry about how to compute it you should think about what you want to compute. If you have $3:1$ and $4:1$ do you want the result to be $7:2$? Maybe there are more events at $3:1$ and it would be better to represent it as $9:3$ and the result should be $13:4$? If you have $1:1$ and $8:7$ what result do you want? I don't believe there is a reasonable answer without having the background population data. $\endgroup$ – Ross Millikan Oct 20 '17 at 3:26
  • $\begingroup$ Uhh I made an original mistake in saying that one of the ratios was 16:4. This was a dumb mistake on my part because all the ratios are :1 e.g. 4:1, 20:1, 5:1 etc and they always will be to :1 that ratio part never changes so my apologies for that. $\endgroup$ – Chug Oct 20 '17 at 3:50
  • $\begingroup$ You can certainly average the larger numbers then. Whether that makes sense depends on the application. Whether you should use an arithmetic, geometric, or harmonic mean also depends on the application. $\endgroup$ – Ross Millikan Oct 20 '17 at 3:54
  • $\begingroup$ How to extract the numbers from a text entry of 4:1 in Excel is well off topic here. $\endgroup$ – Ross Millikan Oct 20 '17 at 3:54
  • $\begingroup$ It's not a text entry, it's a number entry I'm actually probably trying to do a weighted ratio average formula the more I think about it because it has to do with weight of Honey and chemical markers which I am working out a ratio for 2 of the the chemical markers. So I have the weight of drums of honey and the 2 chemical markers of the honey. I need the ratio of the chemical markers per drum but for the total drums I need the weighted average ratio for those markers (at least that's what I think I need?) $\endgroup$ – Chug Oct 20 '17 at 4:06

Browse other questions tagged or ask your own question.