👉 Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!
Thank you! I recently started experiencing problems when comparing my workbooks and they wouldn't match. Is there a way to set it in excel without having to use the round formula? like in Excel ==>Options...?
Not that I know of...and I suspect you can't set rounding globally as it would cause all sorts of issues for people that didn't know the setting was on. You can use the round function inside lookup and reference formulas to compare things though. Sometimes this can avoid actually having to round the numbers first.
I have question, how to convert the number below in excel? Q1: 45 years 7 months 6 days =46 Q2: 45 years 0 month 3 days = 45 Can make the video as the explanation?
Have a look at my video on rounding time ruclips.net/video/9n0cp-Xj3hc/видео.html You should be able to work out the principles from this. To round to years you can divide dates by 365, round to no decimals, and then multiply the result back by 365.
The one problem with using ROUND to solve all balancing issues is that it will not work if you are using it on a balance sheet and the assets side for instance has more numbers that get rounded up versus the liabilities & equity side. For example, if you use ROUND with zero digits for each number and lets say your actual assets numbers are 55.6, 27.7, and 32.51 for a total of 115.81. If you use ROUND with 0 digits on those 3 numbers excel will return the numbers 56, 28, and 33, and when you sum them they will add to 117 as total assets. Now assume you have 3 numbers as your liabilities & equity that are 44.49, 31.45, and 39.87 before rounding that total 115.81 - same as the unrounded assets # does above. If you now use ROUND again on these numbers with zero digits excel will return 44, 31 and 40. Summing those rounded numbers will yield 115. So now your summed numbers are saying you have total assets of 117 (rounded) versus total liabilities & equity of 115 (rounded). Since they do not match you are forced to plug a balancing number of 2 somewhere.
You are right there are definitely some issues with rounding numbers, and your balance sheet example is exactly one of those times. Thanks for your comment, I'm sure others will find it useful too 👍
Any way of combining or breaking out items on one or other side of the balance sheet so the rounded numbers add to the same. i.e. 2.6 rounds to 3, but changing it to 1.3+1.3 rounds to 1+1=2
Isn't it annoying when you're interrupted by someone pointed out your numbers don't add up, when you know it's only a rounding difference. The Excel function 'ROUND' can be used to ensure that columns of numbers add to what is shown on the report therefore avoiding this issue.
Reduce your working hours with my personal coaching. Details at ml.up4excel.com/htcapp04 - Offer Ends 19th August 2022
I have been frustrated about this for years and never could figure out how to fix it. THANK YOU FOR MAKING MY LIFE EASIER!!!
Glad I could help Vidae!
Thank you!!!! I was off by 30 cents because one number was supposed to be rounded to a whole number with no decimals, you saved me from a headache!
Glad to help Diana. Thanks for the comment 👍
first minutes saved my life!
Thanks for the feedback Joel...I hope you weren't really going to die without this knowledge though 😬👍
This round function solved a problem I was having. Thanks so much!
Glad to help 👍
Thanks this clarifies my doubt
Glad to hear that 👍 John
Thank you
So there is no formula to update the total to only add what is visible and not what is hidden also and then round?
You can use Subtotal(109,[Range]) to add only visible cells, would that help? John
Thank you!
Glad I could help you 👍
👉 Up4Excel Downloads Library: »» ml.up4excel.com/library
✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!
Thank you! I recently started experiencing problems when comparing my workbooks and they wouldn't match. Is there a way to set it in excel without having to use the round formula? like in Excel ==>Options...?
Not that I know of...and I suspect you can't set rounding globally as it would cause all sorts of issues for people that didn't know the setting was on. You can use the round function inside lookup and reference formulas to compare things though. Sometimes this can avoid actually having to round the numbers first.
@@Up4Excel tks so much yeah thats what I'm doing. thank you for responding. Have an amazing day!
I have question, how to convert the number below in excel?
Q1: 45 years 7 months 6 days =46
Q2: 45 years 0 month 3 days = 45
Can make the video as the explanation?
Have a look at my video on rounding time ruclips.net/video/9n0cp-Xj3hc/видео.html
You should be able to work out the principles from this. To round to years you can divide dates by 365, round to no decimals, and then multiply the result back by 365.
On which Balance sheet or Income statement have you seen Rounding item? None.
You're right it's most likely inappropriate on these financial statements. I guess summary reports are the main place you might require rounding.
The one problem with using ROUND to solve all balancing issues is that it will not work if you are using it on a balance sheet and the assets side for instance has more numbers that get rounded up versus the liabilities & equity side.
For example, if you use ROUND with zero digits for each number and lets say your actual assets numbers are 55.6, 27.7, and 32.51 for a total of 115.81. If you use ROUND with 0 digits on those 3 numbers excel will return the numbers 56, 28, and 33, and when you sum them they will add to 117 as total assets.
Now assume you have 3 numbers as your liabilities & equity that are 44.49, 31.45, and 39.87 before rounding that total 115.81 - same as the unrounded assets # does above. If you now use ROUND again on these numbers with zero digits excel will return 44, 31 and 40. Summing those rounded numbers will yield 115. So now your summed numbers are saying you have total assets of 117 (rounded) versus total liabilities & equity of 115 (rounded). Since they do not match you are forced to plug a balancing number of 2 somewhere.
You are right there are definitely some issues with rounding numbers, and your balance sheet example is exactly one of those times. Thanks for your comment, I'm sure others will find it useful too 👍
@@Up4Excel This is my problem. Working on a solution now. Any ideas are appreciated.
Any way of combining or breaking out items on one or other side of the balance sheet so the rounded numbers add to the same. i.e. 2.6 rounds to 3, but changing it to 1.3+1.3 rounds to 1+1=2
Isn't it annoying when you're interrupted by someone pointed out your numbers don't add up, when you know it's only a rounding difference. The Excel function 'ROUND' can be used to ensure that columns of numbers add to what is shown on the report therefore avoiding this issue.