three number ratio and cell formatting

[CJJC70]

I'm trying to find the ratio between three very small numbers:

Mass of Nitrogen D1 = 1.00x10-4
Mass of Phosphorus E1 = 2.04x10-6
Mass of Chlorophyll F1 = 7.71x10-6
Total mass H1 = D1 + E1 + F1 = 1.10x10-4


I'm attempting to show whole numbers, but I'm not sure if it is feasible.

The first formula I tried was =D1/GCD(D1:F1)&":"&E1/GCD(D1:F1)&":"&F1(D1:F1) resulted in a #DIV/0! error

The second formula I tried was =(D1/H1)&":"&(E1/H1)&":"&(F1/H1)

resulted in 0.91115137010819:0.0185874879694367:0.0702611409797442

Is there a way to format the cell to where nitrogen is equal to 1 [ex. 1:10:7]?

 

[Rijnsent]

Hi CJJC,
that is probably not something cell formatting could solve, you'd need some functions.

If you want Nitrogen to be 1, you'll have to divide by that number, not by the total mass, and you could use ROUND for a better readable result:
=ROUND(D1/D1,2)&":"&ROUND(E1/D1,2)&":"&ROUND(F1/D1,2)

Or alternatively you could do:
=ROUND(D1/MIN($D$1:$F$1),1)&":"&ROUND(E1/MIN($D$1:$F$1),2)&":"&ROUND(F1/MIN($D$1:$F$1),1), which would lead to [49:1:3.8], the smallest part always being 1.

Hope that works,

Koen

 

[CJJC70]

Oh my goodness! Yes, that worked. Thanks a million (and a billion) .