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

1. ## 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. ## 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. ## Re: How do i display for every 12 whole numbers and anything less comes after the point

Originally Posted by cizwiz
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. ## 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. ## Re: How do i display for every 12 whole numbers and anything less comes after the point

Originally Posted by cizwiz
As a example take 26 i want excell to show this as 2.2
Originally Posted by Pete_UK
=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. ## Re: How do i display for every 12 whole numbers and anything less comes after the point

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

7. ## 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. ## Re: How do i display for every 12 whole numbers and anything less comes after the point

Ah yes. Missed that part.

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