Highest & lowest place value / decimal places of cell value
 

Hi! Does anybody know of a function/series of functions that could determine
the upper & lower place value of a cell value?

E.g. the highest place value used in 123.4 is the hundreds column, and the
lowest is the tenths column. Whereas the highest in 12.34 is the tens column
and the lowest is the hundreths column.

The output would probably be best if it was numerical relative to the
decimal point, as is used in the ROUND functions (number of decimal places
used), e.g.

units column = 0

tenths column = 1 (1 d.p.)
hundredths column = 2 (2.d.p)
etc...
and going the other way (with the result being a negative number, so
denoting a place value to the LEFT of the decimal point)

tens column = -1
hundreds column = -2
etc...

I'd be really grateful if anybody could solve this puzzle for me.
Many thanks in advance, Neil

Highest & lowest place value / decimal places of cell value
 

Hey, that was an interesting challenge, especially the lower but here you go:
To evaluate A1:

Upper value:
=LEN(TEXT(INT(ABS(A1)),0))-1

Lower value:
=LEN(RIGHT(TEXT(ABS(A1)-INT(ABS(A1)),"General"),LEN(TEXT(ABS(A1)-INT(ABS(A1)),"General"))-IF(ISERROR(FIND(".",TEXT(ABS(A1)-INT(ABS(A1)),"General"),1)),1,FIND(".",TEXT(ABS(A1 )-INT(ABS(A1)),"General"),1))))

HTH

Giz

"Neil Goldwasser" wrote:

Hi! Does anybody know of a function/series of functions that could determine
the upper & lower place value of a cell value?

E.g. the highest place value used in 123.4 is the hundreds column, and the
lowest is the tenths column. Whereas the highest in 12.34 is the tens column
and the lowest is the hundreths column.

The output would probably be best if it was numerical relative to the
decimal point, as is used in the ROUND functions (number of decimal places
used), e.g.

units column = 0

tenths column = 1 (1 d.p.)
hundredths column = 2 (2.d.p)
etc...
and going the other way (with the result being a negative number, so
denoting a place value to the LEFT of the decimal point)

tens column = -1
hundreds column = -2
etc...

I'd be really grateful if anybody could solve this puzzle for me.
Many thanks in advance, Neil

Highest & lowest place value / decimal places of cell value
 

That's great! Thank you very much, I certainly wouldn't have got to that one!
Many thanks for your help, Neil


"Gizmo63" wrote:

Hey, that was an interesting challenge, especially the lower but here you go:
To evaluate A1:

Upper value:
=LEN(TEXT(INT(ABS(A1)),0))-1

Lower value:
=LEN(RIGHT(TEXT(ABS(A1)-INT(ABS(A1)),"General"),LEN(TEXT(ABS(A1)-INT(ABS(A1)),"General"))-IF(ISERROR(FIND(".",TEXT(ABS(A1)-INT(ABS(A1)),"General"),1)),1,FIND(".",TEXT(ABS(A1 )-INT(ABS(A1)),"General"),1))))

HTH

Giz

"Neil Goldwasser" wrote:

Hi! Does anybody know of a function/series of functions that could determine
the upper & lower place value of a cell value?

E.g. the highest place value used in 123.4 is the hundreds column, and the
lowest is the tenths column. Whereas the highest in 12.34 is the tens column
and the lowest is the hundreths column.

The output would probably be best if it was numerical relative to the
decimal point, as is used in the ROUND functions (number of decimal places
used), e.g.

units column = 0

tenths column = 1 (1 d.p.)
hundredths column = 2 (2.d.p)
etc...
and going the other way (with the result being a negative number, so
denoting a place value to the LEFT of the decimal point)

tens column = -1
hundreds column = -2
etc...

I'd be really grateful if anybody could solve this puzzle for me.
Many thanks in advance, Neil