SOLVED

Fixing rounded answer for next formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1430016%22%20slang%3D%22en-US%22%3EFixing%20rounded%20answer%20for%20next%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430016%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I%20am%20working%20on%20a%20spreadsheet%20dealing%20with%20the%20projected%20procreation%20numbers%20of%20animals.%20(I.e.%20I%20need%20whole%20numbers%20and%20not%20rounded%20numbers.)%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20fixed%20number%20and%20then%20a%20formula%20that%20calculates%20what%20the%20number%20of%20offspring%20will%20be%20at%20the%20end-%20this%20has%20to%20be%20a%20whole%20number%20so%20the%20decimal%20points%20have%20been%20removed-%20so%20far%20so%20good.%20My%20problem%20is%20that%20I%20then%20have%20a%20formula%20that%20adds%20up%20all%20the%20offspring%20in%20their%20various%20categories.%20This%20formula%20is%20reverting%20back%20to%20the%20underlying%20fraction%20numbers%20and%20not%20working%20solely%20on%20the%20rounded%20figures.%20Does%20anyone%20know%20how%20to%20remove%20this%3F%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20problem.png%22%20style%3D%22width%3A%20757px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195584i708C783226D404E0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Excel%20problem.png%22%20alt%3D%22Excel%20problem.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1430016%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430131%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20rounded%20answer%20for%20next%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430131%22%20slang%3D%22en-US%22%3EInstead%20of%20rounding%20the%20results%20in%20the%20column%2C%20try%20using%20the%20%3DCEILING(number%2C1)%20to%20use%20the%20nearest%20whole%20number%20upward%20of%20the%20result.%20Your%20total%20should%20then%20work.%20%5BSubstitute%20'number'%20in%20the%20formula%20with%20the%20whatever%20you%20use%20to%20get%20the%20number%201%20currently%5D%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430334%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20rounded%20answer%20for%20next%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430334%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!!!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F531239%22%20target%3D%22_blank%22%3E%40Charla74%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430440%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20rounded%20answer%20for%20next%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430440%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20welcome.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1430029%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20rounded%20answer%20for%20next%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1430029%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684916%22%20target%3D%22_blank%22%3E%40Iskrem%3C%2FA%3E%26nbsp%3BCan%20you%20show%20what%20is%20in%20your%20cell%20of%20%221%22%20of%20which%20the%20underlying%20is%200.5%3F%20I%20use%20Round%20function%20and%20the%20sum%20is%202%20as%20expected.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Round.png%22%20style%3D%22width%3A%20368px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195585i9123514360F96B23%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Round.png%22%20alt%3D%22Round.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1432014%22%20slang%3D%22en-US%22%3ERe%3A%20Fixing%20rounded%20answer%20for%20next%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1432014%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20reply%20didn't%20show%20yesterday%2C%20sorry.%20The%20matter%20has%20been%20solved%20but%20in%20case%20you%20need%20to%20know%20for%20your%20own%20peace%20please%20see%20below%3A%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel%20problem%202.png%22%20style%3D%22width%3A%20742px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195824iD006BD076D59BE9D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Excel%20problem%202.png%22%20alt%3D%22Excel%20problem%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello. I am working on a spreadsheet dealing with the projected procreation numbers of animals. (I.e. I need whole numbers and not rounded numbers.) 

I have a fixed number and then a formula that calculates what the number of offspring will be at the end- this has to be a whole number so the decimal points have been removed- so far so good. My problem is that I then have a formula that adds up all the offspring in their various categories. This formula is reverting back to the underlying fraction numbers and not working solely on the rounded figures. Does anyone know how to remove this? Excel problem.png

5 Replies
Highlighted

@Iskrem Can you show what is in your cell of "1" of which the underlying is 0.5? I use Round function and the sum is 2 as expected.

Round.png

Highlighted
Best Response confirmed by Iskrem (New Contributor)
Solution
Instead of rounding the results in the column, try using the =CEILING(number,1) to use the nearest whole number upward of the result. Your total should then work. [Substitute 'number' in the formula with the whatever you use to get the number 1 currently]
Highlighted

Thank you so much!!! @Charla74 

Highlighted
You’re very welcome.
Highlighted

Hi@hynguyen 

 

Your reply didn't show yesterday, sorry. The matter has been solved but in case you need to know for your own peace please see below: Excel problem 2.png