Quick MOD question

[mahmed1]

How can i get the 1st number after the decimal point using the mod function

i.e

42.5
22.5
40
42.2
23.1
29.5

so

5
5
0
2
1
5

If i do =mod(G10:G15,1) i get {0.5;0.5;0;0.200000000000003;0.100000000000001;0.5}

I just need the first digit after decinal point however if it is 0 then get 0)

Thank you

 

[Andrew Poulsom]

Try:

=ROUND(MOD(G10,1),1)

 

[mahmed1]

Try:

=ROUND(MOD(G10,1),1)

Hi thank you

how can i display just the number after the decimal i.e

=ROUND(MOD(G10,1),1 will give

<TABLE style="WIDTH: 75pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=100><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=100 align=right>0.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.5</TD></TR></TBODY></TABLE>

but i want to display

5
5
0
2
1
5


Thanks

 

[Andrew Poulsom]

Like this?

=ROUND(MOD(G10,1),1)*10

 

[mahmed1]

Like this?

=ROUND(MOD(G10,1),1)*10

Aaaaarrrrhhhhh thank you

 

[mahmed1]

Like this?

=ROUND(MOD(G10,1),1)*10

Hi,

Using this formula how can i convert this into a number using formula? i have tried text???,"0.00" but i can't seem to get it to work

I have also tried +0 at the end

=LEFT($G$10:$G$15,2)+0&"."& VLOOKUP(ROUND(MOD($G$10:$G$15,1),1)*10,$N$9:$O$14,2,0)

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 7021" width=192><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 144pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=192>=42.833333333333333</TD></TR></TBODY></TABLE>

 

[Andrew Poulsom]

Is it?

=(LEFT($G$10:$G$15,2)&"."& VLOOKUP(ROUND(MOD($G$10:$G$15,1),1)*10,$N$9:$O$14,2,0))+0

Why are you using an array formula there?

 

[mahmed1]

Is it?

=(LEFT($G$10:$G$15,2)&"."& VLOOKUP(ROUND(MOD($G$10:$G$15,1),1)*10,$N$9:$O$14,2,0))+0

Why are you using an array formula there?

Hi Andrew,

I just realised that i dont need to enter as Array

I modified to

=(LEFT(G10,2)&"."& VLOOKUP(ROUND(MOD(G10,1),1)*10,$N$9:$O$14,2,0))+0

and copied down

Thank you ever so much

 

[Haldoug]

Why do you have to use Mod? Try =((A1-INT(A1))*10)