Adusting decimal places based on greatest number of places in a series

**anelson87** · 08-15-2013, 06:19 PM

Hi all,

I'm trying to figure out a way to dynamically set the number of decimal places for a cell based on the number in a given series that has the greatest number of decimals. For example if I have:

100
125.78964
120.22
133.768
178.9

the result I'm trying to get is:

100.00000
125.78964
120.22000
133.76800
178.90000

Because one of the numbers goes to 5 places, all the numbers go to 5 places. If the number with the most decimals had only two places, all of the numbers would have two places.

I have a function that counts the number of decimal places in a given number, and then a simple MAX formula to get the largest number, but am stuck on how I can change the number of decimal places based on that result.

Any help would be appreciated!

**martindwilson** · 08-15-2013, 07:02 PM

best i can come up with is this array entered formula
="0."&REPT(0,MAX(0,LEN(ROUND(A$1:$A$5-INT($A$1:$A$1:A5),8))-2)) in say d1
then with numbers in a1
in b1
=TEXT(A1,$D$1)

**wjohn13** · 05-11-2022, 01:05 PM

Martin,

I'm not sure if you are still on this forum, but your answer on here would do pretty much what I am looking for. I just don't know how to apply it to my spreadsheet. Could you explain how I woudl apply this to a range of cells?