Hi, when I FOLLOW YOUR EXACT STEPS all my values in the G Column return 73,335. Can someone please tell me what I am doing wrong if I am following her exact steps ?
Leila, you are the best tutor I have ever listened too. All your presentations makes things look simple and flawless and they are very easy to understand. You are truly my MVP when it comes to Excel and Power BI
Leila, you are the best teacher ever! I just currently found your channel, and already using some new features you showed me. THANK YOU! You are brilliant!
I don't have a background in finances, but I manage my own budget in Excel. The other day I was wondering how I could make these kinds of projections. This is a great video! Easy to understand and informative. Thank you!
Thank you for adding some much-needed explanation and clarity to this element of Excel. A video is worth 1,000,000 (1,000²) words. The example helps demonstrate what's going on. Too bad the real world doesn't work quite so easily. "Yes, I need my interest rate to be twice as much to achieve my goals." and BOOM! The interest rate is double. The most controllable piece of the equation is the monthly contribution. But that's a completely different conversation. 😀 Hmm, now that might be a slightly better example with a double lesson: the data table and the contribution amount.
Hi Leila, this is a great tutorial! I was hoping you could offer some guidance on a couple of questions. Q1: is there a way to dynamically change the size of the datatable? I'm thinking with Array Formulas or if the output can be dynamically changed to an Excel table. Q2: Is there a way to "consume" the data table that gets created? For example with PowerQuery, Python in Excel. Thanks!
I never used this function but now we have a clue. Miss Leila thanks a lot for your excellent video. And thanks to the video editor for that remarkable job
Haven't used Data Table before, very interesting video .. to make it clearer, perhaps a macro button can be created to refresh the data instead of pressing F9 ... the button will make clear that the calculation isn't automatic. 😉
Yes terribly unintuitive when choosing the row and column input cell(s) considering they exist in the base formula. Using the first value in the column or row in the table being created would make more sense to me, but I guess doing it the way they are doing it allows the purpose of the numbers in the row or columns in the destination table be defined as they relate to the base formula. If so, the dialog to choose the inputs could still be made more intuitive so that the relationship being made is more prominently displayed and evident.
I can't believe u make this video no longer after I request it! Idk did u make it because I request it or not, whatever it is, thank u so much! Now I can apply this feature
Hi, and thanks for another good video Used the technique in a lot in a job some years ago, so thanks again for the reminder. Also I find that the datatable is useful combined with Dsum or Dget formula, but yes the spreadsheet can be a bit slow
Probably as some kind of overview I would use data table. Otherwise I would create a drop down selection/ slider for year and interest, to keep it simple and user friendly.
Hi Leila.. I recently upgraded to the new windows and straightaway I have a big problem with excel, I work with multiple workbooks all the time but the new windows version doesn’t show the workbooks separately on the taskbar.. there doesn’t seem to be an easy way to get around this. I’d be interested to know if this is a problem for you or other users..
I'm new to Excel, but I feel that having the formula =FV($B3/12,C$2*12,-200) and autofilling the table would have been way faster and convenient. I'm not sure if what-if feature isn't that helpful or it is just a bad example taken here.
Do you know how to automatically generate a PDF file name, that is numeric and is plus one number from the last PDF file name and then the next PDF file you save is plus one number form that last file etc?
Does this also work if the input cell, on which I want to run a scenario, isnt inside the final formula? Let's say on cell A1 I enter projected GDP growth rate. Then over thousands of cells, using all kinds of other inputs and calculations, I crunch numbers and then finally I have some cashflows. My end calculation is NPV of those cashflows. So my NPV formula doesn't include "GDP growth rate" as an input. Will your method still work? Is excel smart enough to figure out using several dozen precedent arrows that I had used "GDP growth rate" projection as an input way back when?
Hello Mam, I Hope you are fine! Mam, Please guide me how i can do that,, if a Call is equal to "OK" than 2 Coloum is Multiply eachother and if same Cell is equal to "Not OK" than theay 2 coloum is divide. how i can ho this please gide me waiting for your responce
Nice video but one remark from me. Dividing the interest rate by 12 is not totally accurate. it should be (1+interest rate)^(1/2)-1. (12 is the number of months in a year as the interest rate is annual. At 4% level it does not make a big difference, but the higher the annual rate the bigger the difference if you divide by 12. Nonetheless, this is very minor, I really like your videos (especially the more complex ones)
Dear Madam here you are explaining me all the things in office 365 but unfortunately I have 2016 version office I want to install office 365 in my PC for free but I am not able to do this just because of I don't know from which website I download and update office into 365 please help me and tell me the solution of my my question.
Leila, there is a really neat way to use data tables, eg to run a set of scenarios with many variable parameters. To do this, 1. set up the scenarios, one per row, with the parameters in as many columns as you want alongside. 2. Create a scenario selection cell that will look up all the parameters for a given scenario, using the scenarios number as a row offset, ie 1 will pick the items in the first scenario row. In this way, you can select a whole scenario's worth of inputs by changing one cell. 3. Create a data table with the inputs down the left being the sequence 1,2,3,4.... for the number of scenarios. In the columns alongside, put the values you want to store for each scenario. Now run the data table and it will process all the scenarios and produce a set of results for each. So in this way, you can change many variables using a data table, which is extremely useful.
Thank you Leila! I had used Data Tables long back and your video was a much needed refresher to revise and start using them again where needed. Great! 🙂
So nice to return to my standard early morning routine, 'Thursday's With Leila', i.e. hot coffee, biscotti and another insightful training video ... and I know exactly where I will employ this technique. Thank you ... thank you .... thank you. Sooooooooo nice to have you back ... 😍😍😍😍
Data tables never made sense to me. I always default to formulas. The only time they are useful to me is when I’m doing a bunch of simulations using the Solver add-in.
Help! I can't find a video for my challenge... I am trying to find where 2 columns are compared (after being exported from a data base into excel) a specific value appears alone and its unique identifiers (basically, A is the only value with identifier X). Example of the two columns: 1234 A 2341 B 2341 A 4123 A 4123 A 4321 D The result I would like is a 2-column list displaying only the identifiers that A is the only value and discards the rest that A shares with other values or doesn't appear: 1234 A 4123 A 4123 A Thank you so much in advance!
Great video… very well explained! Out of every command on the ribbon this is the one that I don’t really understand the use case for, seems like it can be achieved with absolute & mixed referencing.
Hi David - I see the main benefit of this to be for complex calculations where you have multiple inputs and steps to get to the end result. Then it wouldn't be so easy to replicate with formulas.
@@LeilaGharani and more important, the use of data table for monte carlos simulations (it's a nice manner to force excel to repeatedly calculate the rand fonction)
Great video!! Alternatives: FV is also dynamic: =FV(SEQUENCE(17,,0.03,0.005)/12,20*12,-200) =FV(SEQUENCE(17,,0.03,0.005)/12,SEQUENCE(,7,4,4)*12,-200) Or an universal approach, an user friendly custom-made lambda helper function, for any function fn(x,y) WIDT(r,c,fn,[nm]) What If Data Table Lamda Helper Function. Arguments; r: rows vector(vert) , c: clms vector(horiz) , fn: lambda helper function , [nm]: results name (top left cell) =LAMBDA(r, c, fn, [nm], LET( w, IF(ISOMITTED(r), 1, ROWS(r)), l, IF(ISOMITTED(c), 1, COLUMNS(c)), m, MAKEARRAY(w, l, LAMBDA(x, y, fn(INDEX(r, x), INDEX(c, y)))), HSTACK(VSTACK(IF(ISOMITTED(nm), "Wht if", nm), r), VSTACK(c, m)) ) ) We can call: =WIDT(SEQUENCE(17,,0.03,0.005),20,LAMBDA(x,y,FV(x/12,y*12,-200))) for first first scenario =WIDT(SEQUENCE(17,,0.03,0.005),SEQUENCE(,7,4,4),LAMBDA(x,y,FV(x/12,y*12,-200))) for 2nd scenario The function lists the results with rows/clm headers. ✌
Hi, im trying to figure out how to make a column of data. say, I have a cell that changes in amount daily and I want to make a column and take the value of the original cell every month. ie, Jan 1st the first cell, then Feb 2nd the cell below, etc. thanks, can somebody help me.
Subscribing to this channel is definitely a great investment, and free too! Very nicely explained. I have been using Excel for 25 years, but never used Data Tables.
Hello Leila, I am your big fan, but one video I am still missing, how to integrate monthly comments to reports in dynamic way based not on Power BI, but as a solution for Powerpivot, which is totally forgotten in my opinion on TH-cam 🤗thanks a lot!
Thank you for explaining it wonderfully. Data table was not working so I had to go to options > formula and turn of the Automatic from either manual or automatic except from tables options
Dear experts, seeking help....... Is it possible to insert data in second column automatically by insterting data in first collumn? For Example, If I write BD in 1st collumn, code (2) in 2nd collumn will be inserted automatically. Country code BD 2 CN 5 IN 6 US 10 MY 15 SG 11
I think it needs to be pointed out that a yearly interest rate of 4% is not the same as 4%/12 compounded monthly(which is what 4%/12 is here). 4% compounded monthly is actually a APY of 4.07%. In order to have a 4% yearly interest rate, you would need to use 0.3273% compounded monthly, not 0.3333% that you used.
Good Day Ms. Leila. Can you make some videos where the returning value should be the closest value. I've seen a lot of xlookup examples but it just shows 1 criteria only. xlookup(0,Abs(range)-value,, 0) this works but i have 3 criteria as lookup value.
Am I right to say that the What-If Analysis seems to be equivalent to doing the formula but with absolute references? That's how I've always done done it for two-variable analyses.
Grab the file I used in the video from here 👉 pages.xelplus.com/what-if-file
thanks i was quite worry for my project
Hi, when I FOLLOW YOUR EXACT STEPS all my values in the G Column return 73,335. Can someone please tell me what I am doing wrong if I am following her exact steps ?
Leila, you are the best tutor I have ever listened too. All your presentations makes things look simple and flawless and they are very easy to understand. You are truly my MVP when it comes to Excel and Power BI
100% agree
Leila, you are the best teacher ever! I just currently found your channel, and already using some new features you showed me. THANK YOU! You are brilliant!
Great to have you here!
@@LeilaGharani you have an imposter fan
I don't have a background in finances, but I manage my own budget in Excel. The other day I was wondering how I could make these kinds of projections. This is a great video! Easy to understand and informative. Thank you!
Every time when i have any query I have also found the most suitable solution from your channel. Good Job,
Thank you for adding some much-needed explanation and clarity to this element of Excel. A video is worth 1,000,000 (1,000²) words. The example helps demonstrate what's going on.
Too bad the real world doesn't work quite so easily. "Yes, I need my interest rate to be twice as much to achieve my goals." and BOOM! The interest rate is double. The most controllable piece of the equation is the monthly contribution. But that's a completely different conversation. 😀 Hmm, now that might be a slightly better example with a double lesson: the data table and the contribution amount.
Hi Leila, this is a great tutorial! I was hoping you could offer some guidance on a couple of questions. Q1: is there a way to dynamically change the size of the datatable? I'm thinking with Array Formulas or if the output can be dynamically changed to an Excel table. Q2: Is there a way to "consume" the data table that gets created? For example with PowerQuery, Python in Excel. Thanks!
Wow ! Couldn’t have asked for a better explanation. Kudos !
Ms Leila, you're my favorite Excel Teacher.
Love you, from Malaysia
A very big Thank you Leila. Your video Your contents and Your clear pronunciation though you are not a native English speaker. Really awesome!
Hello Leila. I had used this table way back in 2003 when I used to teach MS Office. Thanks anyways. Good day.
Never tried this. Wow! Just Wow!!
Hope it will come in handy.
I never used this function but now we have a clue. Miss Leila thanks a lot for your excellent video. And thanks to the video editor for that remarkable job
I really appreciate to your video after seeing this I don't have any query related to this topic
Leila I follow your channel always and get more information, learn a lot God bless you more!! Thank you for your class👋👋👋👋👋
Mysterious but cool Excel feature. Thanks for sharing!
How about we celebrate getting 2M subs!!! Well deserved! 😎
Thank you!
Haven't used Data Table before, very interesting video .. to make it clearer, perhaps a macro button can be created to refresh the data instead of pressing F9 ... the button will make clear that the calculation isn't automatic. 😉
ABSOLUTELY AMAZING... SEEING THESE TRICKS IS LIKE A MAGIC FOR ME.
Great very useful information 👍 Thanks
Very nice explanation.
You are a great teacher. Thank you.
That was great. Thank you!
I use data tables to do quick and dirty monte carlo simulations. 🙂
Thank you for reviving…🙏🏻
This could have use cases for sure, but it certainly is not very intuitive. Thanks for sharing Leila.
Yes terribly unintuitive when choosing the row and column input cell(s) considering they exist in the base formula. Using the first value in the column or row in the table being created would make more sense to me, but I guess doing it the way they are doing it allows the purpose of the numbers in the row or columns in the destination table be defined as they relate to the base formula. If so, the dialog to choose the inputs could still be made more intuitive so that the relationship being made is more prominently displayed and evident.
Not intuitive at all. Thanks for watching, Matt!
I can't believe u make this video no longer after I request it! Idk did u make it because I request it or not, whatever it is, thank u so much! Now I can apply this feature
Hope it is helpful :)
Thank you Leila.
Thanks 👍..
The custom format is looovely 💰💘
This is sooooo brilliant! Thank you a lot... 💡👍👍👍👍🙂
Thank You!!
very clear, thanks
Glad you liked it!
Thanks! I'd never have figured that out for myself!
I've always done that the long way round in the past.
Glad it was helpful.
This is great, can you make a video on the use of Oracle Crystal Ball spreadsheet-based application for predictive modeling, forecasting, simulation
Very informative madam
Топовая связка, работает на ура!
Beautifully done!
Thank you so much for sharing
Hi, and thanks for another good video
Used the technique in a lot in a job some years ago, so thanks again for the reminder.
Also I find that the datatable is useful combined with Dsum or Dget formula, but yes the spreadsheet can be a bit slow
Great video Leila , thank you . That would be nice for calculating if you buy a house or go for holiday 😅
Great video, thank you!! Would appreciate more Power BI stuff when you can. Thanks again!!
Sure. Any special topic in Power BI you would like to see?
@@LeilaGharani Do you have any plans to cover Tableau?
No plans for Tableau.
Excellent work 😍
I seem to have the same value when I use the table column in the "What if analysis", how do I rectify this mistake?
Great analysis video 👌
Thanks
this was nice info. Please make video on Solver please
This is really good indeed.
Probably as some kind of overview I would use data table. Otherwise I would create a drop down selection/ slider for year and interest, to keep it simple and user friendly.
Can data table take or return text values? Or only numbers?
For example I want to do the same formula for multiple categories, will it take it?
Hey, need to ask for tool to use, so that I can see responses taken in pdf to excel file log
Hi Leila.. I recently upgraded to the new windows and straightaway I have a big problem with excel, I work with multiple workbooks all the time but the new windows version doesn’t show the workbooks separately on the taskbar.. there doesn’t seem to be an easy way to get around this. I’d be interested to know if this is a problem for you or other users..
I'm new to Excel, but I feel that having the formula =FV($B3/12,C$2*12,-200) and autofilling the table would have been way faster and convenient. I'm not sure if what-if feature isn't that helpful or it is just a bad example taken here.
Not sure I see the value nor purpose of using the data table function when dynamic reference can perform the same task.
help me please, I don't have the data analysis option
Wow 👍
Do you know how to automatically generate a PDF file name, that is numeric and is plus one number from the last PDF file name and then the next PDF file you save is plus one number form that last file etc?
Does this also work if the input cell, on which I want to run a scenario, isnt inside the final formula? Let's say on cell A1 I enter projected GDP growth rate. Then over thousands of cells, using all kinds of other inputs and calculations, I crunch numbers and then finally I have some cashflows. My end calculation is NPV of those cashflows. So my NPV formula doesn't include "GDP growth rate" as an input. Will your method still work? Is excel smart enough to figure out using several dozen precedent arrows that I had used "GDP growth rate" projection as an input way back when?
Very cool. It looks like it works, provided your input cells for scenarios arent formulas themselves or arent references to other cells.
Hello Mam,
I Hope you are fine!
Mam, Please guide me how i can do that,, if a Call is equal to "OK" than 2 Coloum is Multiply eachother and if same Cell is equal to "Not OK" than theay 2 coloum is divide. how i can ho this please gide me waiting for your responce
Is it possible to have a 3d data table that would also show results for different monthly contributions?
The way to do this is by making a list of scenarios, each with multiple variables, then having the data table switch between them.
@@orenmarnin can you link a video?
Rolling interest ?
Hi Leila. I dont know if somebody commented before, but in finance it is called “sensibility analysis”.
👍
Sensibility? No it isn't! It's a sensitivity table.
Nice video but one remark from me. Dividing the interest rate by 12 is not totally accurate. it should be (1+interest rate)^(1/2)-1. (12 is the number of months in a year as the interest rate is annual. At 4% level it does not make a big difference, but the higher the annual rate the bigger the difference if you divide by 12. Nonetheless, this is very minor, I really like your videos (especially the more complex ones)
Dear Madam here you are explaining me all the things in office 365 but unfortunately I have 2016 version office I want to install office 365 in my PC for free but I am not able to do this just because of I don't know from which website I download and update office into 365 please help me and tell me the solution of my my question.
mashallah
Windows. Emojis. 🤯
Leila, there is a really neat way to use data tables, eg to run a set of scenarios with many variable parameters. To do this, 1. set up the scenarios, one per row, with the parameters in as many columns as you want alongside. 2. Create a scenario selection cell that will look up all the parameters for a given scenario, using the scenarios number as a row offset, ie 1 will pick the items in the first scenario row. In this way, you can select a whole scenario's worth of inputs by changing one cell. 3. Create a data table with the inputs down the left being the sequence 1,2,3,4.... for the number of scenarios. In the columns alongside, put the values you want to store for each scenario. Now run the data table and it will process all the scenarios and produce a set of results for each. So in this way, you can change many variables using a data table, which is extremely useful.
Very nice! Thanks for sharing your solution.
I would love to see this translated in a video demonstrations. Sounds VERY useful. Please Leila make it happen!
@@LeilaGharani hi
No idea how to execute the idea but that is exactly what I need.
Leila, it would be neat to have a video to demonstrate what’s being discussed here!
A super model doing math, incredible 😊
Leila: What is it and what is it good for?
Me: (the only response I thought of) "Absolutely nothing, say it again"
Love it, Leila! Another great, simple, and more importantly, useful, tutorial!
Thank you Leila! I had used Data Tables long back and your video was a much needed refresher to revise and start using them again where needed. Great! 🙂
It's a very overlooked yet sometimes quite helpful little feature. Glad you enjoyed the refresher!
Any benfit over dragging a formula? This method actually seems slower.
For this particular scenario, IMO it is much less intuitive and more difficult that using a relative formula. Otherwise interesting and informative.
Would be helpful if you could explain please. I would be interested to learn of this alternative approach. Thank you
So nice to return to my standard early morning routine, 'Thursday's With Leila', i.e. hot coffee, biscotti and another insightful training video ... and I know exactly where I will employ this technique. Thank you ... thank you .... thank you.
Sooooooooo nice to have you back ... 😍😍😍😍
Glad you enjoyed our Thursday date :) Biscotti is a good choice!
This is like some Marvel's what if stuff...
Data tables never made sense to me. I always default to formulas. The only time they are useful to me is when I’m doing a bunch of simulations using the Solver add-in.
Very very intersting mam, thank you so much.
Is there a way to include the 3rd dimension of the amount in this? Or is it too complicated for excel?
Help! I can't find a video for my challenge... I am trying to find where 2 columns are compared (after being exported from a data base into excel) a specific value appears alone and its unique identifiers (basically, A is the only value with identifier X).
Example of the two columns:
1234 A
2341 B
2341 A
4123 A
4123 A
4321 D
The result I would like is a 2-column list displaying only the identifiers that A is the only value and discards the rest that A shares with other values or doesn't appear:
1234 A
4123 A
4123 A
Thank you so much in advance!
Great video… very well explained! Out of every command on the ribbon this is the one that I don’t really understand the use case for, seems like it can be achieved with absolute & mixed referencing.
Hi David - I see the main benefit of this to be for complex calculations where you have multiple inputs and steps to get to the end result. Then it wouldn't be so easy to replicate with formulas.
@@LeilaGharani and more important, the use of data table for monte carlos simulations (it's a nice manner to force excel to repeatedly calculate the rand fonction)
Great video!!
Alternatives: FV is also dynamic:
=FV(SEQUENCE(17,,0.03,0.005)/12,20*12,-200)
=FV(SEQUENCE(17,,0.03,0.005)/12,SEQUENCE(,7,4,4)*12,-200)
Or an universal approach, an user friendly custom-made lambda helper function, for any function fn(x,y)
WIDT(r,c,fn,[nm]) What If Data Table Lamda Helper Function. Arguments; r: rows vector(vert) , c: clms vector(horiz) , fn: lambda helper function , [nm]: results name (top left cell)
=LAMBDA(r, c, fn, [nm],
LET(
w, IF(ISOMITTED(r), 1, ROWS(r)),
l, IF(ISOMITTED(c), 1, COLUMNS(c)),
m, MAKEARRAY(w, l, LAMBDA(x, y, fn(INDEX(r, x), INDEX(c, y)))),
HSTACK(VSTACK(IF(ISOMITTED(nm), "Wht if", nm), r), VSTACK(c, m))
)
)
We can call:
=WIDT(SEQUENCE(17,,0.03,0.005),20,LAMBDA(x,y,FV(x/12,y*12,-200))) for first first scenario
=WIDT(SEQUENCE(17,,0.03,0.005),SEQUENCE(,7,4,4),LAMBDA(x,y,FV(x/12,y*12,-200))) for 2nd scenario
The function lists the results with rows/clm headers. ✌
Other scenario : r: vertical array, years array =SEQUENCE(10,,4,3) (10 years 4;7;10;...;31 )
c: horiz array , mnthy pymt =SEQUENCE(,10,200,30) (10 dif mnth pymnt 200,230,.....470)
Single cell function called:
=WIDT(SEQUENCE(10,,4,3),SEQUENCE(,10,200,30),LAMBDA(x,y,FV(0.04/12,x*12,-y)))
I almost cried reading the textbook. Thanks you 😊❤
Smooth segue, like this segue to our sponsor Tunnel Bear!
U r cute 🥰
Hi, im trying to figure out how to make a column of data. say, I have a cell that changes in amount daily and I want to make a column and take the value of the original cell every month. ie, Jan 1st the first cell, then Feb 2nd the cell below, etc. thanks, can somebody help me.
Subscribing to this channel is definitely a great investment, and free too!
Very nicely explained. I have been using Excel for 25 years, but never used Data Tables.
I am not getting it. Can't we just make the intetest rates relative and the year and monthly payment absolute references? Fot the first one. 🙄🙄😘😢
Hi, when I FOLLOW YOUR EXACT STEPS all my values in the G Column return 73,335
Hello Leila, I am your big fan, but one video I am still missing, how to integrate monthly comments to reports in dynamic way based not on Power BI, but as a solution for Powerpivot, which is totally forgotten in my opinion on TH-cam 🤗thanks a lot!
Thank you for explaining it wonderfully.
Data table was not working so I had to go to options > formula and turn of the Automatic from either manual or automatic except from tables options
Dear experts,
seeking help.......
Is it possible to insert data in second column automatically by insterting data in first collumn? For Example, If I write BD in 1st collumn, code (2) in 2nd collumn will be inserted automatically.
Country code
BD 2
CN 5
IN 6
US 10
MY 15
SG 11
I think it needs to be pointed out that a yearly interest rate of 4% is not the same as 4%/12 compounded monthly(which is what 4%/12 is here). 4% compounded monthly is actually a APY of 4.07%. In order to have a 4% yearly interest rate, you would need to use 0.3273% compounded monthly, not 0.3333% that you used.
Good Day Ms. Leila. Can you make some videos where the returning value should be the closest value. I've seen a lot of xlookup examples but it just shows 1 criteria only.
xlookup(0,Abs(range)-value,, 0) this works but i have 3 criteria as lookup value.
Thank you so much. A great explanation indeed.
When I drag the percentage to down keep copying the 3% How can I drag the number from the same Colum and percentage increases?
Leila. I want analysis of two month salary oct & nov tell me format
Thanks for creating such a amazing vedio on excel, your vedio is very helpuf for me
I can't believe how unintuitive this tool is. I use it only sometimes and I always get confused what and how should I mark.
Am I right to say that the What-If Analysis seems to be equivalent to doing the formula but with absolute references? That's how I've always done done it for two-variable analyses.
All the values are 0 :( tried the format and everything like the tutorial but still just zeros