One quick question, when adding two values, such as regular gross pay and overtime pay to calculate total gross pay, Is there a preferred convention between using sum() and “+”?
@@chrisgray8774 if there are only two things being added, it does not matter. However, there are differences between the two: SUM ignores text, + does not ignore text and will yield an error. If you use number+number+number and then insert a column between the numbers, the + will not "see" the inserted column. SUM(range) will "see" a new column if it is inserted between the first and last cell in the range. if you have many numbers next to each other, the + takes much longer to create a formula than SUM. All of these characteristics have pros and cons based on the scenario.
I love Excel too!!! And I am glad that you do too! Thanks for the support with your comment, Thumbs Up and Sub : ) Please support the cause on each vid you watch : )
MEDIAN function as a substitute for the IF function was a pretty clever use. Last time I remember looking for the median was in middle school (no pun intended).
Sal A, glad this MEDAIN trick was clever for you. Over the past 10 years here at RUclips, I have pulled this out a few times... Always a great trick when there is a hurdle and three different amounts : )
ExcelIsFun I’m more watching your videos about array formulas .... but I like all your videos and love your work ...but I will go back and watch them. Since I think I can help that lady with some formulas where we can prepare a sheet that fixes the time and upload the corrected rounded time to ADP website instead dropping her eyes on the keyboard every Monday
Hi Mike I have two questions 1- Do a version Office 365 Enterprise E5 with additional things for a version 365 pro plus in excel or no 2- Is microsoft see len&lenb two functions or function have two versions
I have no idea any more because Microsoft keeps changing things... You might try contacting Microsoft by phone or online. I actually had to phone them this morning and a random guy I got from India did a really good job helping me, which has not always been the case.
I am trying to figure out a way to use various equations in a single cell based on a unit selection. For example: if I have columns for length, width, and height, and I select cubic feet as my unit, it's easy enough to make a formula that multiplies the three columns. But say if I select cubic yards as the unit, is there a way to automatically divide the answer by 27 instead of manually changing the formula any time I change units.
ExcelIsFun my apologies. I've been liking some of your older videos but I simply commented on your latest one since it had the best chance of having my comment noticed. And thank you for your videos. They have actually helped me to earn a job.
As for your question, you would have to use IF or VLOOKUP Function, but in either case there has to be a single to the formula that can trigger IF or VLOOKUP. For example, there would have to be a cell that has the word cubic. If that is the only option, then a formula like: =IF(Cell="Cubic",CellWithNumber/27,CellWithNumber/1)
I don't mean to pick you out and point this out, but so many humans do gain knowledge for free from this channel, but do not help the channel with Thumbs Up and comments (helps in search, then view count, then ad revenue). So I do appreciate the small effort when people do : )
Do your best to write a formula that will automatically calculate total price for any amount of units placed in C12. or any Unit Quantity Price per unit 1 to 10 20 11 to 19 18 20 to 49 16 50 to 100 13 101 to 200 12
total# of Units (your formula ) Total Price (your formula here)
Siddie Orakzai hello! Separate the first column into 2 columns. Use the lookup function =LOOKUP(« product », « 2 columns with unit quantity », « price per unit ») This function just evaluates whether your product is within each segment and returns the right price
I AM TRYING TO FIGURE OUT THE SUBJECT CODES WITH TWO DIFFERENT MEDIUMS USING WITH COUNTIF FORMULA , BUT NOT WORKING, WILL YOU PLEASE EXPLAIN IT HOW TO FIGURE OUT THE SUBJECT CODES WITH TWO DIFFERENT MEDIUMS
Sir, can you please make a video on "How to create a sorted (A-to-Z) searchable drop-down list in Excel like Google?" I need it urgently. Please teach me this tricks.
I have many formulas videos about how to do this, but they are too hard. It is better to do it with Power Query. Formula: ruclips.net/video/3u8VHTvSNE4/видео.html Power Query: ruclips.net/video/3ICk356kEZo/видео.html
Sorry Sir, but this is not the one I need. I know dynamic data validation with array formulae. I learnt it from you. But, this time what I need is "Dynamic sorted (A-to-Z) searchable data validation list in excel like Google". I'm posting a screenshot of my query on your google plus and twitter account. Please make a video on this topic.
ExcelIsFun ADP is that company that we use to prepare our payroll, we have a hand punch in machine in the factory that collects the time for 100 workers ... that same lady would continue also using the type writer if I didn’t replace it with an Excel form that prints on a pre-printed check form ...
Thank you, Sir. Great wrap up. This is helpful for me to practice the lessons I've learned without skipping any.
You are welcome for the wrap up!!!!
One quick question, when adding two values, such as regular gross pay and overtime pay to calculate total gross pay, Is there a preferred convention between using sum() and “+”?
@@chrisgray8774 if there are only two things being added, it does not matter. However, there are differences between the two: SUM ignores text, + does not ignore text and will yield an error. If you use number+number+number and then insert a column between the numbers, the + will not "see" the inserted column. SUM(range) will "see" a new column if it is inserted between the first and last cell in the range. if you have many numbers next to each other, the + takes much longer to create a formula than SUM. All of these characteristics have pros and cons based on the scenario.
OMG you are an amazing teacher i promise any one sees this video can do payroll taxes. thank you Mr. Mike
You are welcome. Ismaili!!!!
You're my favorite RUclips man! I love excel. 😍
I love Excel too!!! And I am glad that you do too! Thanks for the support with your comment, Thumbs Up and Sub : ) Please support the cause on each vid you watch : )
Me Too!
Amazing simply amazing, this is so useful. Thank you sir
How are you Sir? I have always been your fan. Your contribution to teach me excel is endless. God blesses you for your novel work.
I am glad that the videos help : ) Thank you for giving your Sub, and leaving a Thumbs Up and small comment on each vid to help support : )
Hi sir YODA for me too You're my favorite RUclips man for learning things about Excel, i love what you'r doing man
Glad you love it! Thanks for your comments and Thumbs Ups!!!
Thanks Mike for this EXCELlent video
Youa re welcome! Thanks for the awesome support : )
MEDIAN function as a substitute for the IF function was a pretty clever use. Last time I remember looking for the median was in middle school (no pun intended).
Sal A, glad this MEDAIN trick was clever for you. Over the past 10 years here at RUclips, I have pulled this out a few times... Always a great trick when there is a hurdle and three different amounts : )
Wow all in One
Glad you like it, Sudhakar!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Awesome video Mike!
Glad you like it, Chris! Thanks for your support : ) : )
Thanks!
You are welcome, clifford!
Calculating time.... Thank You!!
You are welcome! Thanks for your support : )
Oh.... mod and mround are beautiful formulas
Did you watch the earlier videos in this Busn Math Class to see a more full description of the MOD and MROUND? But, yes, that are awesome!
ExcelIsFun I’m more watching your videos about array formulas .... but I like all your videos and love your work ...but I will go back and watch them. Since I think I can help that lady with some formulas where we can prepare a sheet that fixes the time and upload the corrected rounded time to ADP website instead dropping her eyes on the keyboard every Monday
Hi Mike
I have two questions
1-
Do a version Office 365 Enterprise E5
with additional things for a version 365 pro plus in excel or no
2-
Is microsoft see len&lenb
two functions
or function have two versions
I have no idea any more because Microsoft keeps changing things... You might try contacting Microsoft by phone or online. I actually had to phone them this morning and a random guy I got from India did a really good job helping me, which has not always been the case.
thank you
Awesome!!!
Glad you like it!
Thanks Mike :)
You are welcome, John!
good working
Glad you like it! Thanks for the consistent support : )
I am trying to figure out a way to use various equations in a single cell based on a unit selection. For example: if I have columns for length, width, and height, and I select cubic feet as my unit, it's easy enough to make a formula that multiplies the three columns. But say if I select cubic yards as the unit, is there a way to automatically divide the answer by 27 instead of manually changing the formula any time I change units.
No Like, no thanks you - and now you want me to answer your question!?!? It seems like the equation of rights and obligations is not in balance.
ExcelIsFun my apologies. I've been liking some of your older videos but I simply commented on your latest one since it had the best chance of having my comment noticed. And thank you for your videos. They have actually helped me to earn a job.
As for your question, you would have to use IF or VLOOKUP Function, but in either case there has to be a single to the formula that can trigger IF or VLOOKUP. For example, there would have to be a cell that has the word cubic. If that is the only option, then a formula like: =IF(Cell="Cubic",CellWithNumber/27,CellWithNumber/1)
I don't mean to pick you out and point this out, but so many humans do gain knowledge for free from this channel, but do not help the channel with Thumbs Up and comments (helps in search, then view count, then ad revenue). So I do appreciate the small effort when people do : )
ExcelIsFun thank you so much
Do your best to write a formula that will automatically calculate total price for any amount of units placed in C12. or any
Unit Quantity Price per unit
1 to 10 20
11 to 19 18
20 to 49 16
50 to 100 13
101 to 200 12
total# of Units (your formula )
Total Price (your formula here)
Siddie Orakzai hello!
Separate the first column into 2 columns. Use the lookup function =LOOKUP(« product », « 2 columns with unit quantity », « price per unit »)
This function just evaluates whether your product is within each segment and returns the right price
Lovely :)
Glad it was lovely : )
I AM TRYING TO FIGURE OUT THE SUBJECT CODES WITH TWO DIFFERENT MEDIUMS USING WITH COUNTIF FORMULA , BUT NOT WORKING, WILL YOU PLEASE EXPLAIN IT HOW TO FIGURE OUT THE SUBJECT CODES WITH TWO DIFFERENT MEDIUMS
For back and forth dailog to get Excel solutions, try this great Excel question site: mrexcel.com/forum
Sir, can you please make a video on "How to create a sorted (A-to-Z) searchable drop-down list in Excel like Google?" I need it urgently. Please teach me this tricks.
I have many formulas videos about how to do this, but they are too hard. It is better to do it with Power Query.
Formula: ruclips.net/video/3u8VHTvSNE4/видео.html
Power Query: ruclips.net/video/3ICk356kEZo/видео.html
Sorry Sir, but this is not the one I need. I know dynamic data validation with array formulae. I learnt it from you. But, this time what I need is "Dynamic sorted (A-to-Z) searchable data validation list in excel like Google". I'm posting a screenshot of my query on your google plus and twitter account. Please make a video on this topic.
this video is difficult😭, lots of formulas, is well explained, but a lot
Ya, sorry about that. It is a lot... : (
My HR lady spends 3 hours on Mondays rounding the time in ADP...
It amazes me how many people in the world still does calculating tasks in inefficient ways... But, please, tell me, what does ADP mean?
ExcelIsFun ADP is that company that we use to prepare our payroll, we have a hand punch in machine in the factory that collects the time for 100 workers ... that same lady would continue also using the type writer if I didn’t replace it with an Excel form that prints on a pre-printed check form ...
All the employees are not rounded to .05minutes...
What do you mean? MROUND was used on all daily times.