need to show no decimal

[sksanjeev786]

Hi Team

I have data and I need 0 decimals in Delta column C,E,G,I (also kept the output in row 11 to 17

Please refer attached for ref

Book1
	A	B	C	D	E	F	G	H	I
1			Delta		Delta		Delta		Delta
2	Unaided Brand Awareness (First Mention)	11	-2.4	12	1.1	12	-2.3	11	1.5
3	Unaided Brand Awareness (Any Mention)	28	-2.3	28	-0.4	28	-5.1 q	28	-1.3
4	Aided Brand Awareness	45	3.1	46	13.9 p	45	1.4	45	6.8 r
5	Online Ad Awareness	21	0.2	21	19.4 p	20	0.5	21	1.8
6	Brand Favorability	30	-1	30	14.9 p	29	1.2	29	4.8
7	Consideration Intent	28	1.1	27	13.2 p	27	0.6	27	8.1 p
8	Purchase Intent	21	4.5	21	15.0 p	21	2.2	21	10.5 p
9
10
11	Unaided Brand Awareness (First Mention)	11	-2	12	1	12	-2	11	2
12	Unaided Brand Awareness (Any Mention)	28	-2	28	0	28	-5 q	28	-1
13	Aided Brand Awareness	45	3	46	14 p	45	1	45	7 r
14	Online Ad Awareness	21	0	21	19 p	20	1	21	2
15	Brand Favorability	30	-1	30	15 p	29	1	29	5
16	Consideration Intent	28	1	27	13 p	27	1	27	1
17	Purchase Intent	21	5	21	15p	21	2	21	11 p
Sheet1

 

[CraigTriesHisBest]

Do you just want to not show the decimals or do you want to remove them from the value of the cell?

If the cell has 5.1 do you want to see 5 but still have 5.1 or have the value in the cell 5?

and considering rounding
If the cell has 5.5 do you want to see 5 but still have 5.5 or have the value in the cell 5?
OR
If the cell has 5.5 do you want to see 6 but still have 5.5 or have the value in the cell 6?

EXAMPLE 1 - leave the value in the call as whatever it is but show it as rounded to the nearest whole number
Example: if a cell has =51/10 the output will be 5.1. You can set the formatting to show no decimals and it will round the number. If you go into the cell with the value you want to show as rounded press Ctrl+1 and then set it to be Number with 0 decimal places. This will keep 5.1 in the cell but show 5. If the value was 5.5 or any number below 6.5 it would show 6 but keep the exact value.

If the value is calculated and you want to actually change 5.1 to be 5 you can round it down by using one of the round functions.

There are 3 similar functions you can use:
=ROUNDDOWN(yourvalue,0) would show 5.1 as 5 (and any value 5 or above but under 6 will give a result of 5)
=ROUNDUP(yourvalue,0) would show 5.1 as 6 (or any value above 5 up to and including 6 will result in 6)
=ROUND(yourvalue,0) would show 5.1 as 5 (and any value 5 or above but under 5.5 will show as 5, any value 5.5 AND under 6.5 will result in 6)
where yourvalue is 5.1 (or the calculation that gives 5.1) and the ,0 is to say "no decimal places"

Hope this helps
Craig

 

[sksanjeev786]

Do you just want to not show the decimals or do you want to remove them from the value of the cell?

If the cell has 5.1 do you want to see 5 but still have 5.1 or have the value in the cell 5?

and considering rounding
If the cell has 5.5 do you want to see 5 but still have 5.5 or have the value in the cell 5?
OR
If the cell has 5.5 do you want to see 6 but still have 5.5 or have the value in the cell 6?

EXAMPLE 1 - leave the value in the call as whatever it is but show it as rounded to the nearest whole number
Example: if a cell has =51/10 the output will be 5.1. You can set the formatting to show no decimals and it will round the number. If you go into the cell with the value you want to show as rounded press Ctrl+1 and then set it to be Number with 0 decimal places. This will keep 5.1 in the cell but show 5. If the value was 5.5 or any number below 6.5 it would show 6 but keep the exact value.

View attachment 88665


If the value is calculated and you want to actually change 5.1 to be 5 you can round it down by using one of the round functions.

There are 3 similar functions you can use:
=ROUNDDOWN(yourvalue,0) would show 5.1 as 5 (and any value 5 or above but under 6 will give a result of 5)
=ROUNDUP(yourvalue,0) would show 5.1 as 6 (or any value above 5 up to and including 6 will result in 6)
=ROUND(yourvalue,0) would show 5.1 as 5 (and any value 5 or above but under 5.5 will show as 5, any value 5.5 AND under 6.5 will result in 6)
where yourvalue is 5.1 (or the calculation that gives 5.1) and the ,0 is to say "no decimal places"

Hope this helps
Craig

Hi Craig,

Thank you so much for your time on this.

I have tried to do the same but wherever I have 13.9 p it is not working for us can we have a VBA for this

Solution i have kept in row number 11 to 17 if we have 13.9 i need as 14 p and if we have 19.4 p i need as 19 p

Let me know if you need anything else on this.

Regards
Sanjeev

 

[sksanjeev786]

Hi Craig,

Thank you so much for your time on this.

I have tried to do the same but wherever I have 13.9 p it is not working for us can we have a VBA for this

Solution i have kept in row number 11 to 17 if we have 13.9 i need as 14 p and if we have 19.4 p i need as 19 p

Let me know if you need anything else on this.

Regards
Sanjeev

Hi Team,

Can anyone help me on this with VBA Macro