Excel MOD Function - Easy Explanation & Practical Examples of MOD for Work

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/mod-function-file

[@bimkumsen]

Thank you Leila! appreciate all of your wisdom and resources offered so generously to the excel community! you are the inspiration for me.

[@dwarfusinterruptus9314]

Leila, I really enjoy your videos. This video is 6 years old, and it will never lose it's relevance. But, I wanted to offer a more consistent solution to the task of ensuring a value in the first row of the data labels column. At first I thought the way you did the calculation was for ease of the viewer. But, when you got to the end and offered up the IF(COUNTA .. I felt I should write and offer this.... When ever I am doing MOD or INT functions with ROW or COLUMN I always refer to the cell I am writing the formula in. This avoids the dreaded #REF error when deleting rows and columns. What I do is SUBTRACT the Row of the current cell in the MOD or INT numerator. This then means my mod result for first cell is zero, so instead of testing if the mod is 1, I change it to 0. So, if the Marker Counter (the Mod Divisor) is in cell E29 and I'm writing the formula in cell E32 this is what I do. =IF(MOD((Row(E32)-32),$E$29)=0,B32,NA()) This can be copied down and the first row will always have the first marker value. Likewise, to your specifice example, instead of using the ROW formula, since you already have a repeating sequence with the month, just use a MOD function off of the month, the value to subtract would need to be the month of the date value in the first row: MOD((Month(A32)-Month($A$32)),$E$29) Excel is so much fun. Frequently, there are many solutions to get the same results.

[@olaadeyemoabraham]

This brilliant, Leila! Your videos are always very helpful.. Thanks for all that you do.

[@sachinrv1]

Great useful function. Just amazing. I recently used this function to achieve to get completed no. of years. If the balance months are greater than 6 then consider full years +1 and if not then only completed no. of years.IF((MOD(DATEDIF(A2,B2,"M"),12))>=6,DATEDIF(A2,B2,"Y")+1,DATEDIF(A2,B2,"Y")). Leila all of your videos are so helpful to guy like me in Finance profile. They save lot of time and efforts. Cannot thank you enough !!!

[@LeilaGharani]

Thank you for sharing your formula Sachin! Very happy to hear you find the videos useful. Thank you also for your support.

[@sachinrv1]

Thanks. Even I shall be very happy to share my experience with Mr. Excel...

[@alfsilva]

Leila, I have been a fan of Excelisfun, now I am becoming also a fan of you. I have seen many of your videos and they are great. Excellent presentations. Congrats.

[@LeilaGharani]

That you Alfredo! I'm a BIG fan of Excelisfun too :) Mike is the best. I've learnt so much from him and his books. Thank you for your support.

[@syrophenikan]

FANTASTIC!!! I've always had a hard time coming up with ways to demonstrate the MOD function. You're awesome!!! Keep up the great work.

[@LeilaGharani]

Thank you. Very happy to hear that :)

[@DaNZealand]

Super!! A couple of days ago I looked for this type of function, but never found it! Thank you Leila! Very professional, as usual!

[@LeilaGharani]

Glad to help Danila!

[@Stepford]

Brilliant Leila! For the first minute or so of this video I was thinking what is the point of the MOD function, but I can now definitely see where I could make good use of this! Thanks for sharing :)

[@LeilaGharani]

That's great! Thank you Steve for your comment. I'm glad you can find some good uses for it :)

[@markwallace1393]

OUTSTANDING! Concise with relevant business examples. I've used MOD in hundreds of cells and VBA macros.. and I still learned another way of using this function to solve a problem.

[@LeilaGharani]

Glad it was useful, Mark.

[@shirishtiwari1013]

Really your example are always mind blowing . Thank you Leila Gharani

[@123rockstar2010]

@16:00 NA() will make my line disappear! So I decided to Change Chart Type to "Scatter plot" @18:15 CountA($B$3:B3)=1 is shocking to me! I used Row(a1)=1...just like @14:01 I've learned so much, thank you, Leila!

[@pmwilliams123]

Thanks!

[@LeilaGharani]

Thank you, Pete!

[@HoppiHopp]

Great video again. ☺️ Excel is so addictive. 😅

[@LeilaGharani]

Thanks! Agree with that comment.

[@josephcoon5809]

3:00 After watching your explanation, I think it may be more clearer to explain it this way: The modulo is the result of adding or subtracting the divisor to the number until you attain a result of 0 > (divisor -1). In the case of 3: 0, 1, 2. So the modulo of: -1 = -1 + 3 = 2 -26 = -26 + (3*9) = -26 + 27 = 1

[@josephcoon5809]

4:30 I just came from one of your “Custom Formatting” videos, and thought your column headers would make a great use of custom formats. This way, if the group size is larger than a single digit, you do not have to worry about changing the RIGHT() function to accommodate that last two characters. It also makes a great segue and justification for linking your “Custom Format” videos 😃

[@josephcoon5809]

17:00 If you are removing the line anyways, do you really have to set the false value of your Data Label IF() function to NA()? If you leave it as “”, you still won’t see the crash with the line turned off.

[@josephcoon5809]

19:00 That second IF() will return true of another value down the column is equal to the first value. I would use an IF() function that checks the value one cell above the dynamic value against “Sales” or a fixed reference to the cell one row above the first value. The ONLY possible time this will be true is for the first value since it is directly below the column header. Setting the IF() to check the value against a fixed reference will allow you to change the column header without breaking the sheet formulae. E39 =IF(B38=$B$38,B39,If(......)) As you fill down, B38 will increment resulting in the number value of the previous month’s Sales which will never equal the value in $B$38 which is the Text value of the column header. You can do the same with another IF() that checks the value one row below which will return the value of the last month of Sales for a label at the end of the graph.

[@josephcoon5809]

19:30 I do not use graphs much, but now I want to wedge them in wherever I can just to play with this method. Thanks again for the early Christmas gift!!! 😂

[@alphamaniac9411]

Thanks again for another useful and applicable tip!

[@tnex]

Thanks for making such complicated function easy

[@LeilaGharani]

My pleasure. It's a great function to have in your tool kit :)

[@deepakbarik1331]

Hi leila..😊 I love your all excel videos, because of explanations are greatttttt....very easy to learn.. hey thankssssss to clear my doubts for some functions..😊

[@LeilaGharani]

That's great to hear! you're welcome.

[@ExcelWithChris]

Brilliant!!! Great extra tips for charts!!

[@gnashi1]

Hi Leila, I am becoming addicted to your lectures can you imagine I spent all my weekend just watching your videos?for you information I am a CFO and I found your tips very useful in preparing my presentations to the BOD. I am now giving the charts to my secretary in order for her to prepare my power point presentations,one question, how can I get the excel workbook that you have used for this video?many thanks,Ghassan

[@LeilaGharani]

Wow! I am impressed! I am glad you can use the techniques and suggestions for your reports. This specific lecture seems to be missing the download file - I'll look for it on the weekend. I'll let you know once I attach it to the post. Thanks for your nice comment and your support.

[@LeilaGharani]

I finally found the Workbook. It's available for download: www.xelplus.com/excel-mod-function/. Enjoy :)

[@gnashi1]

Thank you Leila for sharing the excel workbook. Your helpful spirit is much appreciated from my side. You are a nice person and a real friend. Even though we haven't met before, and maybe we will not get the chance to meet during this life in person, but I am proud of your friendship. Feel free to contact me for anything you need. Again thank you Leila, thanks for everything. Ghassan Nashi Chief Financial Officer - CFO CEO Consular for financial affairs

[@LeilaGharani]

My pleasure. Thank you for the very kind words. I really appreciate that.

[@somokaelijah4769]

Thanks for these such educative tutorials

[@ExactProBi]

great video Leila, MOD is indeed confusing function !!

[@hoanna8453]

Brilliant!!! 👍👍👍 but I was wondering if MOD function can be used for weekly data?

[@ميناصلاح-ظ6و]

i didn't get thats part 14:05 , any explanation thanks

[@lavitaneja4161]

Great, leila, Excel is becoming fun.

[@SanjayKumar-yx6gc]

Really, I like all your video, explanation is very clear & understandable.

[@LeilaGharani]

Thank you. Glad to hear that Sanjay.

[@alexonuskajr.6778]

Great video on mod and future data. Is there an excel formula / function that can complete full current year of column based on most recent sales date. Just a column by date showing full current year based on sales in current year say Feb 26,2021 or any day in current year. thanks

[@Meolimo]

Un gros merci Leila ton explication est excellente :-) je comprend beaucoup mieux cette fonction

[@LeilaGharani]

You're very welcome! Glad you like it.

[@Meolimo]

An other great utilyties of the mod function is to calculate time betwen time in and time out when it goes over midnight T IN : 23:00:00 and T OUT 00:01:05 with MOD((T OUT - T IN);1) give 01:01:05 it's magic :-)

[@hattemghanoom9557]

Hi Leila Thanks for sharing this very interesting video with us. Very smart excel features that can be implemented immediately in our daily professional life. Very creative & sophisticated techniques.

[@LeilaGharani]

You're very welcome Hattem.

[@nazmik2xxx]

brilliant video, thanks for that .

[@warrenmcaleny]

Brilliant tips x thanks for solving our problems...keep up the magic.

[@LeilaGharani]

Thank you Warren, & you're very welcome.

[@njssedhu9349]

I like all your video, explanation is very clear & understandable thankssssss to you

[@LeilaGharani]

Glad you like it. You're very welcome.

[@hsusophia4248]

Thanks for the video, Leila! Very helpful.

[@LeilaGharani]

You're very welcome. Glad you find the tutorial useful.

[@vadjunzy]

Thank! Interesting things of mod function !

[@mezianeremila454]

Totally new to me, thank you Leila...

[@asadullahrozbeh6558]

Wonderful tips about MoD.

[@lisaamante5384]

Las Vegas, NV, USA: HELP! I can't get my x-axis to look like yours. I've checked all my formulas to make sure they match yours. The cell formats are correct when viewing the cells: year only appears once in the column, per year, and months are only the first letter. When I right-click on the x-axis of the chart and Select Data..., the right pane under Horizontal Axis labels shows: 2019 J, 2019 F, 2019 M, etc. (but there are no check boxes in front of them like on yours). Nonetheless, on the actual chart, it shows, (and on only one line): Jan 1900, Jan 1900, Jan 1900... where the month letters and years should be (on two lines with tick marks). Any ideas? BTW, your videos are outstanding, I so appreciate your teaching style. I never understood the business case for utilizing MOD until now. Thank you!!

[@mahamohan1]

Wonderful video with clear explanations

[@LeilaGharani]

Thank you. Glad you like it!

[@rohitkarnani0506]

Wow really interesting...Thank you Leila

[@LeilaGharani]

You're welcome 😊

[@jamesperry3837]

This is really good. Great tutorial. Thanks for sharing so much.

[@LeilaGharani]

You're welcome James.

[@navidnikpour5033]

Great explanation :) Thank you

[@withcflifetravel]

Thanks for the wonderful tips! But I am wondering how to determine the reference to put into the ROW function in Example 3?

[@nadermounir8228]

Very nice tutorial 👌

[@LeilaGharani]

Thanks a lot 😊

[@kadhiresannarayanaswamy7348]

clear and succnit . Thank you so much

[@boneson13]

Is there any way to get a recurring value consecutively for two times every alternate cycle. Let me illustrate the problem by giving you the details. I am making a roster with 4 teams. Team A comes for a morning shift on day1 and night shift on day2. Then they are on leave for the two subsequent days. Then the cycle repeats. Likewise there are 4 teams. Members of each team are distributed in two separate areas and the requirement is to rotate the members between the areas during successive cycles. Is it possible to do this using formula in excel

[@planxlsm]

12:32 Formatação Condicional - Meses no Gráfico

[@AlergicToSnow]

Great examples!

[@kamrulzoha9223]

In this video I can't see any thing except you 😍

[@javedshaikh-cm1vs]

Ma'am you are outstanding 👍

[@LeilaGharani]

Thanks Javed. Glad you like the video :)

[@festuskariuki8469]

your made me understand very easily...thank you

[@LeilaGharani]

You're very welcome.

[@tklut]

Great job, Leila.

[@LeilaGharani]

Thank you!

[@masonsimoes2718]

Hi You are great i liked how you explain...

[@stevet3331]

Thanks Leila, good stuff. Thank you.

[@LeilaGharani]

You're welcome Steve.

[@Lucream2]

Hie Leila thanks for the marvelous excel tips that you post to help us. May you please do a video on how to make a coxcomb chart?

[@adityajha1889]

Please make more vedios i like your spirit

[@hariishr]

This is really good

[@quasipseudo1]

Very good explanation.

[@LeilaGharani]

Thank you.

[@mubassirrangwala4775]

Wonderful use for mod function

[@LeilaGharani]

Glad you like it.

[@almedagroom3201]

Excellent explanation appreciated

[@mohammedkassimsalim9351]

Dear Leila =IF(MOD(ROW(A4),$E$36)=0,B39,"") when i put this formula, it works for the starting cell also

[@gireeshponnuru8661]

Nice explanation Leila..

[@LeilaGharani]

Thank you Gireesh.

[@Softwaretrain]

Dear Leila, I have a strange problem with this function. Please test this number and check the result, =MOD(28.8,9.6) It should be zero but its not. Could you please tell me cause of this problem? I think this is a bug in EXCEL.

[@LeilaGharani]

Hi - I've seen something similar before and this post from Microsoft explains it: support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel I don't think it's necessarily a bug - just how the program works with numbers. Here you can find a good explanation: bit.ly/2BlbAAq. Round function would help.

[@Softwaretrain]

Leila Gharani thanks alot then please explain about these exceptions in your teachings .

[@09shirish]

Thanks Mam !, it's very useful to me !

[@LeilaGharani]

You're very welcome.

[@mrsaharamesh]

What if 278 valu get repited in data lable with last formula

[@MuhammadUsman1]

How we can show labels on column chart, which is aggregated based on date axis? Like we have daily data, we change the base to months in format axis section.

[@LeilaGharani]

Hi Muhammad. You can change the labels to show months or year by going to axis options, units section and from the drop-down select month instead of day.

[@MuhammadUsman1]

I've done this already, all I want to know how to show value labels on these columns now?

[@LeilaGharani]

For the labels, you can create a new series and create a dynamic formula that shows the value for the date that you want to show. That formula depends on your setup. You could use the MOD function or you could use something like =if(day(date cell)=1,value,"") - This will show the value for the 1st of each month. Once you add this series to your chart (like in this tutorial), then you just have to activate the data lables for this series and hide the series from view....hope this helps.

[@MuhammadUsman1]

Leila Gharani thanks!😊

[@DirkOutdoor]

Hi Leila, very interesting with the MOD function. I tried to transform it into my German version but it seems that we can't use this function because the German versions don't have it, or do you anything more? Best wishes! Dirk

[@LeilaGharani]

Should work also in German. Check out this page: support.office.com/de-de/article/rest-funktion-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3

[@shihabshanto]

You are awsome. Could you please make a video about snake column in google sheet. I am reayy struggling about it.

[@Dev_Bartwal]

I really Love all classes You are gorgeous ♡

[@LeilaGharani]

Glad you love the classes Dev :)

[@andylenihan9738]

This is amazing, thanks Leila! How did you get the chart you moved away to be more curved than the chart you re-did? Thanks again!

[@MrDellVostro1510]

Hello Andy, Select the line and right click on it, select format data series, under the Fill & Line, scroll down to the last option and check the smoothed line option.

[@jdurvesh]

this is a good video thanks !!

[@LeilaGharani]

You're welcome Durvesh.

[@rashedb5138]

thx alot it helps to apply it in matlab too.

[@ewa4678]

It doesn't work if your number is not integer. I have function such =Mod(21,9;0,1) and it gives 0,1; Although if I multiply them by 100 so =Mod(2190;10) it gives zero. Why that happens? Is that only my excel or what?

[@123rockstar2010]

tinyurl.com/y6swra46 try using: 21.9-0.1*int(21.9/0.1) = 0 Formula: MOD(n, d) = n - d*INT(n/d) More accurate.

[@mark.reddell]

It has occurred to me after watching this video that in the parentheses for month were a says A29 there to be A29 -1 right there because the beginning quarter would be on 1/1, 4/1, 7/1, 10/1 and again for 1/1. I don't know if anybody else noticed this yet or not. Its this correct???

[@SP-bw3zp]

Good, thank you

[@TN_12_abz]

hiii leila what is the simple way of vlookup formula in excel

[@LeilaGharani]

For a simple vlookup you can check this video out: kzbin.info/www/bejne/e2jKgmCGnL6eg8U

[@mibrahimmeo]

How do I copy data from 1 destination(Your Mind) to mine

[@LeilaGharani]

You should at least do some major filtering before :)

[@mibrahimmeo]

Never.......unless the data copied :)

[@boschista]

Where we can download excel mod workbook file?

[@LeilaGharani]

I'll add the link in tomorrow --- will let you know.

[@vegetitagamerpro2912]

En Excel español es la *función "RESIDUO"*

[@LotfyKozman]

Please tell me how to generate a series of date where each date must appear 4 times in its column till the EOY, such that: 01-Jan-2017 01-Jan-2017 01-Jan-2017 01-Jan-2017 02-Jan-2017 02-Jan-2017 02-Jan-2017 02-Jan-2017 . . . 31-Dec-2017

[@LeilaGharani]

You could use this formula written in cell A2: =IF(MOD(COUNTA($A$1:A1),4)=1,A1+1,A1) In A1 you can type the starting date to be: 31-Dec-2016 and format as date.....

[@LotfyKozman]

Thanks Leila, it works perfectly

[@newrick57]

I would fall in love with Excel if you were my teacher Leila :)

[@LeilaGharani]

Well, then let me be your teacher Eric. I have a lot of videos here :)

[@newrick57]

Leila Gharani I am watching all of them with great interest and I would like to thank you and congratulate for the quality of the material you deliver.

[@hosseinhosseinpoor4845]

سپاس

[@vegetitagamerpro2912]

En español es función "RESIDUO"

[@DigitalicaEG]

Groups of 1 would've been even better! 🤔

[@dasaevents]

=MOD(9.6,3.2) why is answer 3.2 ?????????

[@Abbermist]

"0 can be divisible by 3," you almost lost me there. If you have nothing there's nothing to divide. The result you showed is 0 so i moved on. I look out for your videos - very good - thanks.

[@carlosgaytan6639]

Wait, you can pull a number out of a cell with text and use it in a formula??? The more I know, the more I realize I don't know.

[@LeilaGharani]

That's the great thing about Excel. Always something new to learn :)

[@samecken]

Had to watch the first 2:30 like 20 time for it to be clear to me. T_T

[@DigitalicaEG]

🔥🔥🔥

[@OZCamperTravels]

Had me confused until I realised your dates were m/d/yyyy not d/m/yyyy

[@johnygebbi]

Mod just means Remainder

[@Softwaretrain]

Hi Leila. It seems you are iranian, if yes please let me connect you by email since Im iranian and Im in the way that you are almost at the end of it(learning advance excel)

[@LeilaGharani]

Hi - It's great you are also a fan of Excel. You can find me on LinkedIn + Twitter. I use my email for my consulting work here...

[@QwertyCanada]

lol

[@eliseojrdelima1297]

we don't understand

[@mohammedkassimsalim9351]

Sorry Sorry , it is not

[@puchaczytacz]

Jesus Christ, Leila, love you so much, but you overengineered it. The best explanation for mod function is to tell them that we use it every day, by looking at clock face. mod(16,12)=4, then military time 1600 is 4pm.

[@adityajha1889]

🔥🔥🔥