aap sabhi logo ka bahut bahut thanky ..jo aap logo ne video uplod kiye aur sabhi logo ko ghar baithe exce ki jankari di .....aage bhi aap log aise hi video banaye aur ham logo ki padhai me help kijeye
Hi Glen, What is the formula for rounding the numbers to the nearest 9.00. For example 531.00 and would like it to be 539.00. It's not working for me with any of the formulas I have used. Thanks
Very very informative. Thank you! However I just started using Microsoft Excel for my budget I have been using OpenOffice which I believe is much more simple.
As someone just starting it might seem good enough but you are on a road too short to work up any speed... once you get to a level of proficiency in open office you will regret not having learned Excel
hi Glen thank you got to learn a lot in your video have a question in excel sheet u showed, in same XLsheet instead of % for some item I want to add flat figure like $5 to cost to get selling price without changing the % added value to other items example item A B D E H K Lhas % added to cost to get selling price, Item C,F G I J i want to add flat value $5 to cost($3) to get selling price $8
Lets pretend the cost of a cell is in cell B5, if you want to add $5 it is just =B5+5 If you want to round it, use an example from the video... if it is conditional, for instance, if less than $100 add $5, otherwise round... use an IF statement =IF(B5
Hi , great video there! However how do I round down if its 0.50? Say i wanna round down if its 200.25 (to 200.00) and round up if its 200.60 (to 201.00), how can i do that ? Please help thanks !
Awesome video really helpful. How would I round all of my prices up to the nearest 9. Example My price is 23.43 and I need it to round up to 23.49 and I need to do this for the entire price list. Thank you!
=ceiling(yourNumber * 100,50)/100 - 0.01 If we take your number in cents multiply it by 100, it turns the whole thing into dollars. Ceiling with a 50 now forces it to round up to the nearest 50 dollars dividing the result by 100 makes it rounded to the nearest 50 cents then subtract 1 penny
Hello. Yes we are talking about reordering on an inventory level. Say they sold 32 on hand have 6 it is an easy formula to subtract to get the total to reorder however what happens when you have to order by a case pack of say 24
Lets say product name is in column A Qty on hand is in Column B Quantity desired is in Column C Quantity reorder amount is in Column D (being the case quantity of 24 in your example) In Column E we will put the reorder amount formula For row 2 In Cell E2 =if(C2>B2,roundup((C2-B2)/D2,0),0) This verifies that the amount requested is higher than the amount on hand and as a result pulls the case quantity. If my case quantity is 24 and I have 6 on hand and an order comes in for 50 this part of the formula roundup((C2-B2)/D2,0) will determine that I am 44 short, divide that my 24 and round up determining that I need 2 cases minimum
Glen Munro Hi Glen Thanks for that great information. Now to take it one step farther - we also need to take into account what was sold. For example Sold 32 mens hats - had 6 on hand. We need to do a simple subtraction forumula to determine the number of hats to rebuy so that they have at least what they sold the year before. I know that we can increase this amount by a percentage if we are hoping to increase sales. Now if those hats are sold in case packs of 24, what does that forumula look like. I am very new to excel - Thanks for your help
Tammy Salvalaggio Hi Tammy, Actually developing spreadsheets like this one is what I do for a living, as well as teaching Microsoft Office. If you would like to send me your sheet to look at, or set up a web meeting if you would prefer, I may be able to find many ways to improve your document, heighten accuracy, save time, and warn you of concerns before they happen. If you would like to do that, contact me at getpumped@braindiesel.ca
***** if your number you want to round up is in B2, B3, B4... Paste this formula in C2 and copy it down. =IF(CEILING(B2*100,10)/100=B2,B2+0.09,CEILING(B2*100,10)/100-0.01) This part.. CEILING(B2*100,10)/100 takes the number in B2, multiplies it by 100, rounds it up to the next 10 and then divides if by 100 In effect, this rounds everything to the next dime BUT, it the amount was already rounded to the nearest dime, for instance, $0.70 the Ceiling formula leaves the result the same. so... IF(CEILING(B2*100,10)/100=B2 checks to see if the number is the same or different than the starting number If it is the same ($0.70 for example) B2+0.09 it adds $0.09 If the result rounded to the next dime, this part CEILING(B2*100,10)/100-0.01) does the math and subtracts a penny
Hi Is it possible if you have retail price in one column, For example, simplest way for me to explain Any figures between 17.01 and 17.50 goes to 17.45 Any figures between 17.51 to 17.99 goes to 17.95 Thanks Declan
how about if i want to set if there is any amount which is less than a specific amount, can i make a formula which automatically round up to a specific amount i wanted to set as minimum. and then while other amount which is above the minimum amount, will not be disturb.
Hello! I have this problem: I used function Average(C3:F3), the result is 5,33; in other cell i have 5,66. I want to use function countif to count how many of 5 and 6 are.I formated cells to remove decimals; excel show numbers without decimals, but in background they are still there and formula countif dont count them.What can i do? Thank you!
How would you round to the nearest 25 or 75? That is 7,029 becomes 7,025 and 7052 becomes 7,075. Thank you. To clarify I do not want multiples of 25 or multiples of 75. I want to be able to specify final two numbers are either 25 or 75. Edit: I ended up using the MOD function and some IF statements to force it to round a number up or down to the nearest 25 or 75 value. Maybe there is a better way.......
Use CEILING to round UP and FLOOR to round down... so to round up to the next 25... =CEILING(B2,25) To Round DOWN =FLOOR(B2,25) IF you want to round it to the NEAREST 25 (up or down)... =ROUND(B2*4,-2)/4
@@GlenMunrofromBrainDiesel I ended up doing what I needed to do with the MOD function. Though I will still look at what you suggested. My concern is your formulas would return values of 00 or 50 which I do not want.
@@brianl.wenninghoff7696 Cool... I misinterpreted your question. Please post your solution for the benefit of others (myself included) Here is an option that leaps to mind for me... =Floor(a2,100) + If(A2-Floor(a2,100)
If cell A1 has a value over $1... =IF(A1-INT(A1)>=.69,.99,IF(A1-INT(A1)>=.39,.69,.39))+INT(A1) I have not tested this but from scratch should work A1-INT(A1) deducts the integer from the number leaving the cents Then using if statements produces your rounding points Then adds back in the deducted dollars
If I am using excel to help do reordering for retail products. If I need to reoder 18 pcs for example, but I have to order in quantities of 24 what is the forumula Thanks
I would be happy to answer this but your question is confusing. You need to reorder 18 but you have to order in 24's... Then order 24. If you are talking about calculating reorders based on an inventory level, that is another thing entirely
Sir Brian.. Hi I'd like to ask you how will i convert hours in to amount like for example 10:38am up to 11:38am its 1 hour and it 15.00 pesos how will i do that in excel? thanks
If afraid your question doesn't make sense to me. Adding an hour is not rounding and pesos is money, not time. Either you are messing with me or you need to clarify your question
I need a formula for If the profit is morethan 100 the total should be 100. If the profit is under 100 the total should be what actually get. Eg: If Total profit is 135 crore The column for total should be show 100 only. The total profit is 98 crore the total should be show 98 crore only. Please help me
![Gucci Mane & Sexyy Red - You Don't Love Me [Official Music Video]](http://i.ytimg.com/vi/j7IBqyFKT4c/mqdefault.jpg)
aap sabhi logo ka bahut bahut thanky ..jo aap logo ne video uplod kiye aur sabhi logo ko ghar baithe exce ki jankari di .....aage bhi aap log aise hi video banaye aur ham logo ki padhai me help kijeye
Thanks, I was looking how to round off to the nearest 5,000, and this video provided the exact explanation I needed.
Glad it worked for you
Thanks, I was looking at how to floor to the nearest 25, and this video provided the exact explanation I needed.
Really helpful, i have been looking for this ceiling function thinking it was in the roundup- Thank you so much
Glad it helped you out
Great help here on the ceiling function, Thank you!
Awesome I thought I was looking for roundup but the function I really needed was ceiling thanks
Thanks alot.....this helped my work
Glad to hear it
Glad it helped. Thanks for watching.
This was exactly what i was searching for. thank you so much .
Love your video :) I needed to round as well...thank you a bunch.
Glad it helped
Thanks man. Helped a lot!
Never Knew It Before Thumbs Up Sir
Thank you
Thanks so much for this upload, keep it continue...
Hi Glen,
What is the formula for rounding the numbers to the nearest 9.00. For example 531.00 and would like it to be 539.00. It's not working for me with any of the formulas I have used. Thanks
Very very informative. Thank you! However I just started using Microsoft Excel for my budget I have been using OpenOffice which I believe is much more simple.
As someone just starting it might seem good enough but you are on a road too short to work up any speed... once you get to a level of proficiency in open office you will regret not having learned Excel
Thanks a lot Mr glen .
great video
Nice,
Helpful Thank you
No problem!
Really helpful !! Tx a lot !!
hi Glen
thank you got to learn a lot in your video have a question in excel sheet u showed, in same XLsheet instead of % for some item I want to add flat figure like $5 to cost to get selling price without changing the % added value to other items
example item A B D E H K Lhas % added to cost to get selling price, Item C,F G I J i want to add flat value $5 to cost($3) to get selling price $8
Lets pretend the cost of a cell is in cell B5, if you want to add $5 it is just =B5+5 If you want to round it, use an example from the video... if it is conditional, for instance, if less than $100 add $5, otherwise round... use an IF statement
=IF(B5
Hi , great video there! However how do I round down if its 0.50? Say i wanna round down if its 200.25 (to 200.00) and round up if its 200.60 (to 201.00), how can i do that ? Please help thanks !
=round(number you want to round,0)
The comma zero rounds to the next dollar.
The .5 logic as the division point happens automatically
Awesome video really helpful. How would I round all of my prices up to the nearest 9. Example My price is 23.43 and I need it to round up to 23.49 and I need to do this for the entire price list. Thank you!
=ceiling(yourNumber * 100,50)/100 - 0.01
If we take your number in cents multiply it by 100, it turns the whole thing into dollars.
Ceiling with a 50 now forces it to round up to the nearest 50 dollars
dividing the result by 100 makes it rounded to the nearest 50 cents
then subtract 1 penny
I Just solve a problem with this wonderful tutorial, thanks for your help
AWESOME!
Glad you think so!
Many Thanks!
you are welcome
Hello. Yes we are talking about reordering on an inventory level. Say they sold 32 on hand have 6 it is an easy formula to subtract to get the total to reorder however what happens when you have to order by a case pack of say 24
Lets say
product name is in column A
Qty on hand is in Column B
Quantity desired is in Column C
Quantity reorder amount is in Column D (being the case quantity of 24 in your example)
In Column E we will put the reorder amount formula
For row 2
In Cell E2 =if(C2>B2,roundup((C2-B2)/D2,0),0)
This verifies that the amount requested is higher than the amount on hand and as a result pulls the case quantity.
If my case quantity is 24 and I have 6 on hand and an order comes in for 50
this part of the formula
roundup((C2-B2)/D2,0)
will determine that I am 44 short, divide that my 24 and round up determining that I need 2 cases minimum
Glen Munro Hi Glen
Thanks for that great information. Now to take it one step farther - we also need to take into account what was sold.
For example
Sold 32 mens hats - had 6 on hand. We need to do a simple subtraction forumula to determine the number of hats to rebuy so that they have at least what they sold the year before. I know that we can increase this amount by a percentage if we are hoping to increase sales. Now if those hats are sold in case packs of 24, what does that forumula look like. I am very new to excel - Thanks for your help
Tammy Salvalaggio Hi Tammy,
Actually developing spreadsheets like this one is what I do for a living, as well as teaching Microsoft Office.
If you would like to send me your sheet to look at, or set up a web meeting if you would prefer, I may be able to find many ways to improve your document, heighten accuracy, save time, and warn you of concerns before they happen.
If you would like to do that, contact me at getpumped@braindiesel.ca
How about correcting to N significant figure? Would that be possible in excel?
Hello Glen, Thanks for the video. How would I round up the change to the next .09? Example: 0.22 becomes 0.29, 0.75 becomes 0.79, 0.11 becomes 0.19.
***** if your number you want to round up is in B2, B3, B4...
Paste this formula in C2 and copy it down.
=IF(CEILING(B2*100,10)/100=B2,B2+0.09,CEILING(B2*100,10)/100-0.01)
This part.. CEILING(B2*100,10)/100
takes the number in B2, multiplies it by 100, rounds it up to the next 10 and then divides if by 100
In effect, this rounds everything to the next dime
BUT, it the amount was already rounded to the nearest dime, for instance, $0.70 the Ceiling formula leaves the result the same.
so...
IF(CEILING(B2*100,10)/100=B2
checks to see if the number is the same or different than the starting number
If it is the same ($0.70 for example)
B2+0.09
it adds $0.09
If the result rounded to the next dime, this part
CEILING(B2*100,10)/100-0.01)
does the math and subtracts a penny
Hi
Is it possible if you have retail price in one column, For example, simplest way for me to explain
Any figures between 17.01 and 17.50 goes to 17.45
Any figures between 17.51 to 17.99 goes to 17.95
Thanks
Declan
If price is in A2, in cell B2 try =CEILING(ROUND(A2,0),0.5)-0.05
I have not tried this... not plugged in... but I think will solve it.
Brain Diesel Hi, seems to round everything to .95
how about if i want to set if there is any amount which is less than a specific amount, can i make a formula which automatically round up to a specific amount i wanted to set as minimum.
and then while other amount which is above the minimum amount, will not be disturb.
=if (cell with value < target amount, round function, cell with value)
Let me know if that makes sense
@@GlenMunrofromBrainDiesel is it
=IF(i13
@@timmylu2255 Looks about right
Thanks Alot for Information.
Hello! I have this problem: I used function Average(C3:F3), the result is 5,33; in other cell i have 5,66. I want to use function countif to count how many of 5 and 6 are.I formated cells to remove decimals; excel show numbers without decimals, but in background they are still there and formula countif dont count them.What can i do? Thank you!
=round(Average(C3:F3),0)
Now every number is a 5 or a 6
Then do a simple Countif
Brain Diesel Thanks so much
How would you round to the nearest 25 or 75?
That is 7,029 becomes 7,025 and 7052 becomes 7,075. Thank you. To clarify I do not want multiples of 25 or multiples of 75. I want to be able to specify final two numbers are either 25 or 75.
Edit: I ended up using the MOD function and some IF statements to force it to round a number up or down to the nearest 25 or 75 value. Maybe there is a better way.......
Use CEILING to round UP and FLOOR to round down... so to round up to the next 25... =CEILING(B2,25)
To Round DOWN
=FLOOR(B2,25)
IF you want to round it to the NEAREST 25 (up or down)...
=ROUND(B2*4,-2)/4
@@GlenMunrofromBrainDiesel I ended up doing what I needed to do with the MOD function. Though I will still look at what you suggested. My concern is your formulas would return values of 00 or 50 which I do not want.
@@brianl.wenninghoff7696 Cool... I misinterpreted your question.
Please post your solution for the benefit of others (myself included)
Here is an option that leaps to mind for me...
=Floor(a2,100) + If(A2-Floor(a2,100)
@@GlenMunrofromBrainDiesel Your formula is pretty good. Here is how my brain solved the same problem.
=IF(ABS(MOD(A2,100)-25)
@@brianl.wenninghoff7696 Interesting approach
thanks, very useful
I need to round the Cents, could you help me understand how to do the following: If Cost =0.99 & =0.39 & =0.69 &
Are all of your prices absolutely less than $1 or are you just trying to round the pennies to those fixed points so that $14.40 would be $14.69
If cell A1 has a value over $1... =IF(A1-INT(A1)>=.69,.99,IF(A1-INT(A1)>=.39,.69,.39))+INT(A1)
I have not tested this but from scratch should work
A1-INT(A1) deducts the integer from the number leaving the cents
Then using if statements produces your rounding points
Then adds back in the deducted dollars
What about changing a decimal number to the next whole number. (e.g. 3.14 --> 4)
Let 3.14 be in cell A2. Then =ceiling(A2, 1) will round 3.14 to 4.
If I am using excel to help do reordering for retail products. If I need to reoder 18 pcs for example, but I have to order in quantities of 24 what is the forumula Thanks
I would be happy to answer this but your question is confusing.
You need to reorder 18 but you have to order in 24's... Then order 24.
If you are talking about calculating reorders based on an inventory level, that is another thing entirely
Thank you very much sir :)
Sir Brian.. Hi I'd like to ask you how will i convert hours in to amount like for example 10:38am up to 11:38am its 1 hour and it 15.00 pesos how will i do that in excel? thanks
If afraid your question doesn't make sense to me. Adding an hour is not rounding and pesos is money, not time.
Either you are messing with me or you need to clarify your question
when i mulltiply 2 nos the answers is 4.267 but i want only 4.26 but when i drop dgits after decimal it goes to 4.27 how to stop rounding of 2nd digit
+Anees Qureshi instead of using ROUND, use ROUNDDOWN
As in
=ROUNDDOWN(number you want rounded,2)
=ROUNDDOWN(4.267,2) results in 4.26
Sir two cell add & third cell devide ans round off mein kaise laate hain
Thanks dear!!!
How could we adjust excel to show 2 decimal as a parameter from excel options
Hi, I am sorry, I am not understanding your question
If you want it rounded to the nearest cent
=ROUND(your number,2)
Select the cell or cells you want > Right click > Format Cells... > Select the number tab > select Number in side bar > set the decimal places
Incredible~
Thanks for the round function
thanks dear
Thanks
My pleasure
I need a formula for
If the profit is morethan 100 the total should be 100. If the profit is under 100 the total should be what actually get.
Eg: If Total profit is 135 crore
The column for total should be show 100 only.
The total profit is 98 crore the total should be show 98 crore only.
Please help me
Sorry.. just seeing this.
Assuming the Profit is in A2, this formula in B2 (or another cell) should solve it...
=IF(A2>100,100,A2)
@@GlenMunrofromBrainDiesel Thanks bro
@@sumesholassery1947 Happy to help
👍
O_O
ROUNDING SHOULDNT TAKE 6 mins
Raabert Ellis I went to your profile to learn how it is done but... nothing... shocker.
#thoseWhoDontDoCriticizeHuh