Excellent. Maybe, if you have enough latin followers, in little corner of the video, you should write the function name in spanish. Actually, I understand, but you can increase the scope of the channel. i like a lot the fast explanation. thank so much 🙏👏👍
This is a great tutorial. However, I'm having a huge issue with the "Ending Balance" formula. When I type in "SUM", it wants me to add more information within the formula. If I simply close to brackets, I get "#NAME" error. I've been trying for 3 hours to fix this. But I just can't figure out what I'm doing wrong. My formula mimics yours to the letter. What am I doing wrong???
It is likely that your version of excel doesn't support such 'eta reduced lambda' functions (eg, using SUM as a function argument). In these cases, you can use LAMBDA(a,b,SUM(a+b)) instead of SUM as that argument, and it should provide the desired results. Hope it helps!
I am doing the Scan formula exactly as shown (=SCAN(0,B10#,SUM)) and all I can get in the scan column is "#NAME?" I cannot figure out what I am doing incorrectly. After doing some internet research, I replaced the word SUM with LAMBDA(a,b,SUM(a+b) and then it worked perfectly. Does this mean that I have a different or older version of excel? Or really, what does this indicate? Thanks.
@@ciaucia156 Same here - currently if you F1 on =SCAN it tells you to use =LAMBDA. Yet again this is still in Beta or Insiders only availability and it will be several months before this is in "Live / General" release Excel. Wish all experts would qualify such post with "This is available only in Beta / Insider Excel versions and may therefore not be available to you yet".
perfect, please create a dynamic amortization table with Interest only payment option. it would be great help. Thanks in advance Eg. Loan Amount 1,200,000 Loan Rate 12% Annually Loan Term 20 Interest Only Payment Period 10 Extra payment 3000 Following option are required in column 0 Payment No. 1 Opening Balance. 2 Principal. 3 Schudel Monthly Payment 4 Interest. 5 cumulative Interest. 6 Extra Payment. 7 Ending Balance.
At the time of this video, the SCAN function is in the process of being rolled out to Excel 365 users. It won't be retroactively added to perpetual license versions of Excel such as Excel for Windows 2016, 2019, and so on. So, the #Name? error most likely means your version of Excel doesn't have the SCAN function or doesn't support eta reduced lambdas. You may be able to replace the SUM argument with the LAMBDA equivalent.
Love it! How do you factor in extra monthly payments when using PPMT and IPMT while still keeping the spill ranges and dynamic arrays?
Great explanation. How do you add an Extra Principal Payment column using Dynamic Arrays? I'm getting Circular References. Thanks.
This is excellent and thank you very much for this video! Short, sweet and to the point.
Glad you enjoyed it!
This is really cool! However unfortunately you can’t use it within a table, is there anyway set this up to where you can also use filter feature?
Excellent. Maybe, if you have enough latin followers, in little corner of the video, you should write the function name in spanish. Actually, I understand, but you can increase the scope of the channel. i like a lot the fast explanation. thank so much 🙏👏👍
I loved your approaches
This is a great tutorial. However, I'm having a huge issue with the "Ending Balance" formula. When I type in "SUM", it wants me to add more information within the formula. If I simply close to brackets, I get "#NAME" error. I've been trying for 3 hours to fix this. But I just can't figure out what I'm doing wrong. My formula mimics yours to the letter. What am I doing wrong???
It is likely that your version of excel doesn't support such 'eta reduced lambda' functions (eg, using SUM as a function argument). In these cases, you can use LAMBDA(a,b,SUM(a+b)) instead of SUM as that argument, and it should provide the desired results. Hope it helps!
@@ExcelU That would make sense.
I am doing the Scan formula exactly as shown (=SCAN(0,B10#,SUM)) and all I can get in the scan column is "#NAME?" I cannot figure out what I am doing incorrectly. After doing some internet research, I replaced the word SUM with LAMBDA(a,b,SUM(a+b) and then it worked perfectly. Does this mean that I have a different or older version of excel? Or really, what does this indicate?
Thanks.
it happens to me too.
form of "eta reduced lambda" is used here (currently available only in beta chanell).
btw, you could use just LAMBDA(a,b,a+b)
Thanks, yes, I replaced with the shortened formula and it still worked fine.@@ciaucia156
@@ciaucia156 Same here - currently if you F1 on =SCAN it tells you to use =LAMBDA. Yet again this is still in Beta or Insiders only availability and it will be several months before this is in "Live / General" release Excel. Wish all experts would qualify such post with "This is available only in Beta / Insider Excel versions and may therefore not be available to you yet".
I have that same problem, but using LAMBDA just returns #REF. :(
why am i coming back with a #NAME? for the running balance???
very good
perfect, please create a dynamic amortization table with Interest only payment option. it would be great help. Thanks in advance
Eg. Loan Amount 1,200,000
Loan Rate 12% Annually
Loan Term 20
Interest Only Payment Period 10
Extra payment 3000
Following option are required in column
0 Payment No. 1 Opening Balance. 2 Principal. 3 Schudel Monthly Payment 4 Interest. 5 cumulative Interest. 6 Extra Payment. 7 Ending Balance.
Tried to replicate your amortisation spreadsheet but my version of Excel does not support the SCAN function. Is the SCAN function still in beta mode?
Yes ... in other words SCAN is not fully rolled out at this time to 365 subscribers.
show value error, please help
I can't get the scan function to work. I enter =SCAN(0,the array,sum) and I get #Name?
At the time of this video, the SCAN function is in the process of being rolled out to Excel 365 users. It won't be retroactively added to perpetual license versions of Excel such as Excel for Windows 2016, 2019, and so on. So, the #Name? error most likely means your version of Excel doesn't have the SCAN function or doesn't support eta reduced lambdas. You may be able to replace the SUM argument with the LAMBDA equivalent.
in the function syntax, use LAMBDA(a,b,a+b), it will work