+ Reply to Thread
Results 1 to 8 of 8

How do i display for every 12 whole numbers and anything less comes after the point

  1. #1
    Registered User
    Join Date
    02-19-2020
    Location
    uk
    MS-Off Ver
    2010
    Posts
    5

    How do i display for every 12 whole numbers and anything less comes after the point

    Hi guys

    I am sorry for my poor explanation in advance I'm not very good at descrbing what i want to happen.

    Basically i am looking for a way for excell to display a 1 for every 12 whole numbers and anything after that a point.

    As a example take 26 i want excell to show this as 2.2 I hope this makes sense again sorry for the poor explanation.

    TIA

    Dan

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,511

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    A single example is not much use to show likely variations in your data - what would you expect for the number 23 ?

    I would suggest that you try this in B1:

    =INT(A1/12)+MOD(A1,12)/100

    where the decimal values will show a leading zero for remainders less than 10.

    Hope this helps.

    Pete

    EDIT: the formula assumes that your number is in cell A1

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    Quote Originally Posted by cizwiz View Post
    i am looking for a way for excell to display a 1 for every 12 whole numbers and anything after that a point. As a example take 26 i want excell to show this as 2.2
    If A1 contains 26, is that simply =A1/12 formatted as Number with 1 decimal places?

    But beware: the cell value is still the exact result of the division. In other words, =A1/12 is actually 2.16666666666667, even if it appears to be 2.2 due to formatting.

    You did say "anything" after the decimal point ("a point" [sic]).

    But if you want the actual value to be 2.2, perhaps you want =ROUND(A1/12,1) formatted as Number with 1 decimal places or as General.

  4. #4
    Registered User
    Join Date
    02-19-2020
    Location
    uk
    MS-Off Ver
    2010
    Posts
    5

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    Thanks for your response mate it was close to what i was looking for ill try to explain further



    intial cost 100
    per month Cumulative
    year 0 -100 -100
    year 1 20 -80
    year 2 60 -20
    year 3 40 20


    Years 2
    Remainder 6


    I have some simple maths above to work out how many years it would take to get your money back on a initial 100 investment
    My remainder formula is : =(the amount it takes after year 2 to breakeven / the total amount earnt that month) * 12
    =(20/40)*12

    what i would like to do which may be a bit petty however would be very handy is to enter the amount of years into the formula
    like follows

    =2+((20/40)*12)

    and this display as 2.6

    Idm if this was be written as

    =24+((20/40)*12)

    Im sorry this is a tad unnecessary but the formatting would help with a lot of my workings in the future if this is not clear
    just shout and i will try to re explain

    TIA
    Dan

  5. #5
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    Quote Originally Posted by cizwiz View Post
    As a example take 26 i want excell to show this as 2.2
    Quote Originally Posted by Pete_UK View Post
    =INT(A1/12)+MOD(A1,12)/100
    When A1 is 26, that results in 2.02, not 2.2 .

    No matter. After Dan's follow-up response, it's not clear to me that either way is even remotely close to what he needs. I'm happy to step out and let you deal with the self-described "poor explanation". Good luck!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,625

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    Similar to Pete's
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Sorry but I do not understand what you are wanting in the other examples.
    Dave

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,511

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    But if the remainder is 10 or 11 after dividing by 12 (e.g. for the numbers 22 or 23) then dividing that by 10 will give spurious results - that's why I suggested dividing by 100, to retain the leading zero for remainders which are less than 10.

    Pete

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,625

    Re: How do i display for every 12 whole numbers and anything less comes after the point

    Ah yes. Missed that part.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Text box display with 2 decimal point.
    By hkbhansali in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-17-2017, 11:48 AM
  2. Display X number of formulas from any given point along a row
    By jeremylittman in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 04:19 PM
  3. Formatting a cell to display a space at mid point of value
    By mbender in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2013, 05:27 AM
  4. Display only the number from left of decimal point.
    By arthurz11 in forum Excel General
    Replies: 9
    Last Post: 10-29-2011, 03:20 AM
  5. Display the last point's data label in series with VBA
    By cesna123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2010, 11:13 AM
  6. If 0, don't display point on graph
    By Captain Black in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2008, 12:22 AM
  7. [SOLVED] Display a digit before or after the decimal point in another cell
    By Robert Monks in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-03-2005, 05:25 AM

Tags for this Thread

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