Help with making whole numbers in multiple calculations

Adecon

New Member
Joined
Apr 20, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Sorry about the title but this is really difficult to explain. I hope it makes sense.

1693155757630.png


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 -

1693156170915.png


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
 
I have highlighted a cell which illustrates the issue.
It does not really illustrate the issue to me since I have no idea what results you expect in that cell, or why. Can you elaborate?

BTW, SUM in all your formulas does nothing. Get rid of it. A couple od examples:

=B6*D2/113 is exactly the same as =SUM(B6*(D2/113))

=C25*0.95 is exactly the same as =SUM(C25*0.95)
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Yes thanks, but my question was is there any way to do without adding additional columns/calculations. I guess I'll just have to hide Column F
 
Upvote 0
It does not really illustrate the issue to me since I have no idea what results you expect in that cell, or why. Can you elaborate?

BTW, SUM in all your formulas does nothing. Get rid of it. A couple od examples:

=B6*D2/113 is exactly the same as =SUM(B6*(D2/113))

=C25*0.95 is exactly the same as =SUM(C25*0.95)
Thanks Peter,
My issue is that 95% of 25 is actually 24 (23.75). Even when I set the cell format to number with no decimal place, it still calculates 95% of 24.6903 which is 23 (23.4558). I was trying to find a way to correct it without adding additional columns but it seems that may not be possible.
 
Upvote 0
=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)
 
Upvote 1
Solution

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top