See how to create sequential numbers in formulas using the ROWS function. See the advantages of using this ROWS function method over other methods. See 4 different formula examples of this trick. See the ROWS, ROW, and IF functions in various formulas. Incrementing Numbers in Formulas. ROWS COLUMNS functions.
Excel Magic Trick #203: Sequential Numbers in Formulas ROWS function
Written by admin on February 11th, 2012 in Formula 1.
Tags: #203, Excel, Formulas, function, Magic, Numbers, ROWS, Sequential, Trick
Cheers? mate!
i hav a better? idea, how about the same method if u try with COUNTA instead of ROWS,
the advantage is, if the intermediate rows are left blank the counting will start from the next non-blank cell on-wards,even if we delete some data s? from in between the counting will automatically re arranged…
@TheGovardhanreddy , You are welcome!?
THANK YOU?
You can try? this video:
Excel Magic Trick 407: Amortization Table W Variable Rate
It has a bunch of Amortization tricks but no formula to hide formula if years go to 10 from 25.
Maybe sometime in the future I’ll do one.
You? need to search for and watch this video title:
excelisfun Search for Excel Videos, Playlists & Download Workbooks
This video shows how to find the videos you want.
e.g if you enter 25 years for payment the amortization table will only go up to 25 years however if you enter 5 years the amortization table will only go to 5 years and the rest of the cell will be blank.? I find your videos awesome and easy to understand. Any chance of doing an in depth Amortization table with extra payments etc..
See manage loans with an amortization table to understand the video i am relating to (3 parts). The video is good but get confusing half way
Many Thanks
I have seen an amportizationtable done by someone else on youtube where they have done some formula to indicate how long it will take to pay off the amount if they made extra payments (Not fixed amount). The table updates to how much interest will be paid and? also how many years it will be cleared in. A formula was also done to have the cells blank if the years exceed
Watch this video:
Highline Excel Class 07: VLOOKUP function formula 7 Examples
start at the 18:40? minute mark
Always use ROWS or COLUMNS instead of ROW or COLUMN (as seen in video)
Dear Excellfun,
I have a problem with the ROW/COLUMN function when trying to use it in Vlookup or in Hlookup.
It? always gives an #REF error.
I dont want to change the row number or column number whenever I want to copy the formula to next cells.
Can you please show me how to use Row/Column again in my below example for Vlookup & Hlookup. Thanks.
AgeCareer
Tim40Teacher
Mike20Student
Julie30Doctor
Mary25Model
Dear marshajt7634,
You are? welcome!
–excelisfun
thank? you!
Dear SnookMouth,
I am glad that this helps! Many other formulas in the videos I have posted use this trick? to increment numbers!
–excelisfun
Really helps. Thank you so? much!
Dear mwunlv1,
I am glad it was helpful!
ROWS(A$3:A3) is? more robust than ROW(A3)!
–excelisfun
Great video!? Thank you very much.