Wowwwwwww 💓💓💓💓😍😍 thank you💓💓😍

@Computergaga

3 жыл бұрын

Welcome 😊

I have been looking for this solution for a long time, thank you!

@Computergaga

3 жыл бұрын

You're welcome, Braulio.

Always great to watch, very helpful instructive and well explained. A definite 'go to' resource

@Computergaga

2 жыл бұрын

Thank you Geoff 👍😊

Genius!! Just solved an issue that will save me a lot of work!!! I no longer have to change the formula, each month, to the newest populated column. Virtual fist bump, Alan!

@Computergaga

Жыл бұрын

Thanks Sandy.

Beautiful work. Thanks for sharing

@Computergaga

5 жыл бұрын

You're very welcome Syed.

Hi Alan.. INDEX, MATCH and COUNTA to the rescue. Excellent! Your clever examples and solutions are teaching my brain how to solve many problems. Thanks and Thumbs up!

@Computergaga

5 жыл бұрын

Thank you Wayne.

Thank you! Im new with google sheets/excel. Been struggling with this formula, finally i have it

@Computergaga

3 жыл бұрын

You are welcome, Gabriel.

Hi! A very crisp video. Thanks for taking the time. Can you shed a little insight on how to use this for data that are in a single column? Fetch the last entry in column D, for example. I tried =Index(Range),1,0 and many variants to get it to work but with no luck

Thank you! it is helpful

@Computergaga

3 жыл бұрын

You're welcome, Arzu.

This is great, thank you!

@Computergaga

3 жыл бұрын

You're welcome, Rodrigo.

Thanks for the video...

@Computergaga

5 жыл бұрын

You're very welcome Tushar.

Really really helpful ...!good job!

@Computergaga

3 жыл бұрын

Thank you, Buddhika.

Works like a charm! thanks

@Computergaga

3 жыл бұрын

You're welcome!

Thank you so much for this video!!

@Computergaga

2 жыл бұрын

No problem 😊

Great .. go on

Brilliant thank u so much

@Computergaga

4 жыл бұрын

You're welcome. Thank you Ayoub.

verrryyyy helpful, thank you :)

@Computergaga

Жыл бұрын

You're welcome 👍

Very good tutorial👍🏻

@Computergaga

5 жыл бұрын

Thank you Phyllis.

This has worked well for me, thank you. Only problem is I have two cells that are coming up blank - nothing in them. They have exactly the same calculation as all the others.

It works. God bless you. Thanks 🙏

@Computergaga

2 жыл бұрын

You're welcome!

I got it! Thank you!

@Computergaga

2 жыл бұрын

You're welcome 👍

Thanks!

@Computergaga

4 жыл бұрын

You're welcome Ricardo.

Thank you so much.

@Computergaga

3 жыл бұрын

You're welcome, Mansour.

Hi! Great video and very helpful. Just the solution I was looking for. It worked on most of my data but in a few rows, it did not pick the last value and instead displayed a zero. How can I correct this, any suggestions?

Very helpful! My case is a little different, but it should work, I have a string of values in 8 columns of a row, and need to figure out the last value that is not a number and not 0. So, essentially last number > 0. I am guessing the COUNT criteria just changes.

Just what i needed right now

@Computergaga

3 жыл бұрын

Happy to help.

Brilliant explanation. Thank you!

@Computergaga

4 жыл бұрын

Thank you Per.

@computergaga Thanks for what you do! One thing about google sheets... for any array formulas like this you must add the array formula function. then you're off to the races.

@Computergaga

Жыл бұрын

Thanks, Bram.

I'm a big fan of you after watching this video

@Computergaga

4 жыл бұрын

Great! Thank you.

@dbcooper488

4 жыл бұрын

@@Computergaga Hello Can you help on this Where do I see to copy MS Excel file path ???? Normally when right button click on mouse it will see but It not showing... Please help

This is very helpful!! Question: How would you handle roll over numbers using these formulas? For example, let say Product E has rolled over after reaching 1000, thus your value for week 22/11/2018 is 153 instead of 1153. Now, you want to calculate the increase between this week and last week. How would you account for this in your formula? I'm doing something similar for meter readings and they roll over at a certain point. Thank in advance for any recommendations you can offer:)

yes okay, the formula is working now. thanks.

@Computergaga

4 жыл бұрын

Excellent!

Thanks

@Computergaga

4 жыл бұрын

You're welcome Asiri.

Great description sir, you should get 10 likes

@Computergaga

2 жыл бұрын

Thank you very much 👍

Alan, this video was really helpful. What if I want the column header (Date in your example) returned for the last, non-blank cell in each row? Do you know how I can setup the function? Thanks for your anticipated assistance.

@Computergaga

3 жыл бұрын

Great! You can use the same formula, Abimbola, but reference C2:CZ2 instead.

Hi Allan, thank you so much. I was wondering if I want the date of the last occurring numbers how do I change the formula? Because some of the date will not have data in it.

@Computergaga

4 жыл бұрын

Sure. Just edit the ranges used in the INDEX and COUNTA functions to look along the rows of dates and not the values.

Thanks for the sharing, can you please let me know if there is 0 in data & we need to find data instead 0, what would be the formula?

@airtimebill

4 жыл бұрын

I need this too??? Help

great video! was really helpful. i used the index formula to pull data for calculation. One question, how can i keep the data thats being pull for calculation to not changed when new data are being added to the table? thank you.

@Computergaga

5 жыл бұрын

Thank you. To stop it from updating when new data is added, you could to remove the formula. Select the cell(s), copy and paste values.

@wodezhu

5 жыл бұрын

Computergaga thank you for the reply. This is what we are doing now. Was hoping it can be automated to reduce human error. ☺️

@Computergaga

5 жыл бұрын

Well the formula is automated to always return the last row even when the table expands. But if you don't want this you can remove the formula. Other options could be Power Query or a macro.

Hi Allan, Good Job But I have one query if I want above mentioned Date instead of Numbers which formula is suitable can you please share Formula..

@Computergaga

5 жыл бұрын

Change the row number in the first range of the INDEX function to use row 2 instead of row 3. Everything else stays the same.

Hey Allan what is the logic of counta in the index column section that lets you capture the last value?

@Computergaga

4 жыл бұрын

The COUNTA function counts all no empty cells. So here it counted the values in row 2 returning 6 which is the last used column i.e. current week.

Thanks for the video, very helpful. Only problem I am having is that some cells in my row are empty, so it doesn't include them in the counta function, which means the counta value is less than it should be. Any way to overcome this?

@Computergaga

4 жыл бұрын

Thank you Carlos. you could run the COUNTBLANK function also and add them together.

@stephanrodriguez6587

3 жыл бұрын

@@Computergaga Thank you so much! I spent hours trying to find this resolution!

Very useful video. What if we have to choose column header of last value's of row instead of its own value. Please help with formula for the same, if you could...

@Computergaga

Жыл бұрын

You're welcome. For the last header value using this data. The formula can be: =INDEX(A2:NZ2,,COUNTA(A2:NZ2)) This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.

I tried the first one in a column, returned #REF, time for more troubleshooting!

@Computergaga

3 жыл бұрын

👍

@ibrahemjamil1004

4 ай бұрын

Just one quotation before counta

Great example! But what if I am looking for text comments entered & not numbers?

@Computergaga

Жыл бұрын

Thank you! The COUNTA function will count all non-blank cells, so text values too 😊

What if the cell before the last value is blank? Why the last value is not showing when the cell before is empty ? Can you help me with this problem please ??

SALUTE TO YOU 😻

@Computergaga

Жыл бұрын

😊

Can you please suggest same for Date? I am in need

@Computergaga

3 жыл бұрын

Depends more specifically on what you are asking, Kanishka. The technique in the video can return dates, if that is what you are asking.

Great video, but if there are blanks in the data it gums it up a bit.

@Computergaga

5 жыл бұрын

For sure. Blanks can be naughty in Excel. Using the header row for the count is more reliable, unless you have a blank column.

@vandalo7494

5 жыл бұрын

You could use the following formula to find the last value in one row =index(yourvaluecells,,match(9.9e307,yourvaluecells,1))

@osamasherwani2354

4 жыл бұрын

@@Computergaga pls share formula

Thank you for sharing. Loved the tutorial and I thought I found a solution to my problem; however, the formula is not working? It is just showing #REF! and not the last value? I tried semi colon as suggested below? that didn't work for me. I have used a name array (ID1_BEST5) in a motorsports points table which highlights the total points at the end of each rd (total of 10 rds in the championship) not highlighting ever race that makes up a round (e.g. 3 races = 1 rd), so ID1_BEST5 cells are H9, L9, P9, T9 and so on. I want/need to display each riders last points they scored for a round. Here is the formula I used from your tutorial =INDEX(ID1_BEST5,1,COUNTA(ID1_BEST5)) No idea why this is not working because your lesson made so much sense. If I copy (and link the cells) from each rd total and put them next to each other and/or name the array with a new name, it works, but this seems like a lot of extra steps? Thank you, I have been reading the net for hours/days trying to find a solution.

@Computergaga

4 жыл бұрын

Thank you Bill. I cannot tell from your formula what the problem might be though.

Is there a way to get the date of the last value in that column?

@Computergaga

Жыл бұрын

For the last header value using this data. The formula can be: =INDEX(A2:NZ2,,COUNTA(A2:NZ2)) This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.

Hi, how about if there is any empty cells in between the columns, this can't be work at all

@Computergaga

3 жыл бұрын

True, so we would use a slightly different approach. We could count the blanks and add them. Or format the range as a table and use the COLUMNS function with it.

@minhthai2804

3 жыл бұрын

@@Computergaga thanks

I want do following this how can I enter formula for this 1) When C1 is equal to D1 then store D1 value in next sheet/ in this sheet 2) again when C1 is equal to D1 then store D1 value in next sheet/ in this sheet below above value Give me the solution of this

Is there a way to do this in Power Query?

@Computergaga

2 жыл бұрын

I'm positive there is, but I'm not sure of the approach to write here. Sounds like a nice challenge Victoria.

What if the data is '0012346 in cell GE Text format?

@Computergaga

4 ай бұрын

The COUNTA function counts all non-blank cells, so will include numbers, text, and errors. Alternatively though, the COLUMNS function can be used. This function returns the total number of columns in a given range, table, or array.

What if the row has an empty cells in between the values

@Computergaga

Жыл бұрын

That would present a problem. Empty cells are rarely helpful. The best solution would be for this range to be formatted a table. Then COLUMNS can be used instead of COUNTA.

When i put in the formula=index(b4:k4,1,counta(b4:k4))a vindow pops up and tell me that there is a failure somewhere!why will it not work for me?

@Computergaga

4 жыл бұрын

Could be that you need semi colons instead of commas =INDEX(B4:K4;1;COUNTA(B4:K4))

@nielsnielsen2708

4 жыл бұрын

I have another problem, if you have the time for it. I like to retrieve values from 14 different cells in a row over a period of 14 days into one cell. Each day i put in a value in a new cell(yellow) and this new value shall replace the value from the day before and presented in the same cell(blue).The blanks cells between the yellows is formulars and can not be changed. I can not figure out to create this formula. Is there a solution for this? A1 is blue. B1, C1, E1, G1, I1, K1, M1, O1, Q1, S1, U1, W1, Y1, AA1 is all yellow

@Computergaga

4 жыл бұрын

I'm not exactly sure what you mean. It sounds like this video is exactly what you want. Retrieving the last value in the row of yellows and putting it in the blue cell.

This only works when you have the column before the data range..... correct?

@Computergaga

Жыл бұрын

No, you can return the result to anywhere.

How i can get last value in a row when is text format? Thanks

@Computergaga

4 жыл бұрын

This technique will work with text also Silvio.

@htcsilvio

4 жыл бұрын

@@Computergaga With text, this not work for me. =INDEKS(B3:NE3,1,COUNTA(B3:NE3)) not work

@Computergaga

4 жыл бұрын

Maybe you have blank cells in the range. COUNTA will count all non blank cells from B3:NE3.

@htcsilvio

4 жыл бұрын

@@Computergaga yes i have blank cells... have any other formula for my problem?

@Computergaga

4 жыл бұрын

There is a function named COUNTBLANK. To find the last row, you need to add the COUNTA and COUNTBLANK results together.

Great video but you made one mistake in calculating the difference %. Formula should be (Current-Prior)/ Prior. In this case (636-527)/527 or 20.7% difference.

@Computergaga

4 жыл бұрын

Good point David. Well spotted. Thank you for your comments on the video.

how about last value in a row

@Computergaga

2 жыл бұрын

That is what the video is showing. The same tech pique can be applied to a column though. Just adjust the ranges.

getting a 0 value..:(

TLDR: =index(range:range,1,counta(range))

@Sandeesh_Srikantam

Жыл бұрын

Wats that ?