Help with making whole numbers in multiple calculations

[Adecon]

Sorry about the title but this is really difficult to explain. I hope it makes sense.

B & D are products of a calculation of A with another cell. C is B*0.95 and E is D*0.95. I need B & D to be whole numbers for the purpose of the calculation in C & E. If I format B & D to a number with 0 decimal places, the calculation still thinks it has decimal places -

when in fact, 25.0 x 0.95 = 23.75 (24).

Am I being stupid (don't answer that!) or can this be done in some way without the need to add extra columns with additional staging calculations?

Thanks in advance

 

[Peter_SSs]

=ROUND(SUM(E6*0.95),0) - this seems to work perfectly. Thanks Sanjay

As I indicated before, SUM does nothing in your formulas (other than make Excel do an unnecessary extra calculation)
This does exactly the same thing without the unnecessary SUM calculation
=ROUND(E6*0.95,0)

 

[SanjayGMusafir]

when in fact, 25.0 x 0.95 = 23.75 (24).

I also face such complex situation where you need to know exact number yet any round UP/down distorst results

The solution is that in display (format) you choose zero decimal places and in background excel woll multiply with decimal figures only. This will serve both purposes for you.

Hope it helps

 

[Fluff]

How about

=ROUND(B2,0)*0.95

 

[SanjayGMusafir]

Actually my XL2BB is giving problem so I can only add screenshot to help you understand

It's a very long calculation, I have just shown you small part of it.

Any change in decimals can deviate end results by huge numbers. so found the solution I suggested you and I'm using.

 

[Adecon]

I also face such complex situation where you need to know exact number yet any round UP/down distorst results

The solution is that in display (format) you choose zero decimal places and in background excel woll multiply with decimal figures only. This will serve both purposes for you.

Hope it helps

Hi Sanjay, I think you're suggesting using format cells>number>0 decimal places - correct? If so, this does not work for me. When multiplying that by 0.95 (95%), I get 23 and not 24 as it seems to still take the full number. I think the "0 decimal places" is simply a mask rather than an actual rounding.

 

[Adecon]

How about

=ROUND(B2,0)*0.95

Hi Fluff,, Thanks for your reply but If I put replace C [=SUM(B2*0.95)] with your suggestion, the product is "0". Could this be because B is a calculated cell?

 

[Peter_SSs]

B & D are products of a calculation of A with another cell. ..... I need B & D to be whole numbers

Please show us the calculations that you currently have for B & D and tell us what is in the "another cell"

XL2BB would be very helpful for the above request.

 

[Adecon]

Handicaps 230827 test.xlsx
	A	B	C	D	E	F
2	Course Handicap Index		Whites	131
3			Yellows	124
4	Name	Handicap	Course Handicap (Whites)	Playing Handicap (Whites)	Course Handicap (Yellows)	Playing Handicap (Yellows)
5
6		21.9	25	24	24	23
7		42.5	49	47	47	44
8		9.5	11	10	10	10
9		21.0	24	23	23	22
10		26.2	30	29	29	27
11		27.3	32	30	30	28
12		22.5	26	25	25	23
13		21.3	25	23	23	22
14		19.6	23	22	22	20
15		12.5	14	14	14	13
16		21.0	24	23	23	22
17		22.9	27	25	25	24
18		28.9	34	32	32	30
19		16.3	19	18	18	17
20		16.7	19	18	18	17
21		26.5	31	29	29	28
22		15.5	18	17	17	16
23		20.8	24	23	23	22
24		39.4	46	43	43	41
25		18.3	21	20	20	19
26		7.6	9	8	8	8
27		8.5	10	9	9	9
28		21.0	24	23	23	22
29		13.9	16	15	15	14
Sheet1

Cell Formulas
Range	Formula
C6	C6	=SUM(B6*(D2/113))
D6:D29,F6:F29	D6	=SUM(C6*0.95)
E6	E6	=SUM(B6*(D3/113))
C7	C7	=SUM(B7*(D2/113))
E7	E7	=SUM(B7*(D3/113))
C8	C8	=SUM(B8*(D2/113))
E8	E8	=SUM(B8*(D3/113))
C9	C9	=SUM(B9*(D2/113))
E9	E9	=SUM(B9*(D3/113))
C10	C10	=SUM(B10*(D2/113))
E10	E10	=SUM(B10*(D3/113))
C11	C11	=SUM(B11*(D2/113))
E11	E11	=SUM(B11*(D3/113))
C12	C12	=SUM(B12*(D2/113))
E12	E12	=SUM(B12*(D3/113))
C13	C13	=SUM(B13*(D2/113))
E13	E13	=SUM(B13*(D3/113))
C14	C14	=SUM(B14*(D2/113))
E14	E14	=SUM(B14*(D3/113))
C15	C15	=SUM(B15*(D2/113))
E15	E15	=SUM(B15*(D3/113))
C16	C16	=SUM(B16*(D2/113))
E16	E16	=SUM(B16*(D3/113))
C17	C17	=SUM(B17*(D2/113))
E17	E17	=SUM(B17*(D3/113))
C18	C18	=SUM(B18*(D2/113))
E18	E18	=SUM(B18*(D3/113))
C19	C19	=SUM(B19*(D2/113))
E19	E19	=SUM(B19*(D3/113))
C20	C20	=SUM(B20*(D2/113))
E20	E20	=SUM(B20*(D3/113))
C21	C21	=SUM(B21*(D2/113))
E21	E21	=SUM(B21*(D3/113))
C22	C22	=SUM(B22*(D2/113))
E22	E22	=SUM(B22*(D3/113))
C23	C23	=SUM(B23*(D2/113))
E23	E23	=SUM(B23*(D3/113))
C24	C24	=SUM(B24*(D2/113))
E24	E24	=SUM(B24*(D3/113))
C25	C25	=SUM(B25*(D2/113))
E25	E25	=SUM(B25*(D3/113))
C26	C26	=SUM(B26*(D2/113))
E26	E26	=SUM(B26*(D3/113))
C27	C27	=SUM(B27*(D2/113))
E27	E27	=SUM(B27*(D3/113))
C28	C28	=SUM(B28*(D2/113))
E28	E28	=SUM(B28*(D3/113))
C29	C29	=SUM(B29*(D2/113))
E29	E29	=SUM(B29*(D3/113))

 

[Adecon]

It's a golf handicap calculator for our golf group. I have highlighted a cell which illustrates the issue.