+ Reply to Thread
Results 1 to 3 of 3

Highest & lowest place value / decimal places of cell value

  1. #1
    Neil Goldwasser
    Guest

    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

  2. #2
    Gizmo63
    Guest

    RE: 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


  3. #3
    Neil Goldwasser
    Guest

    RE: 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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1