A multi-scale investigation into the diagnostic potential of the HCN/HCO^+ ratio for active galactic nucleus and starburst activity in nearby galaxies