Jan 27 2021 11:51 PM
Hello All
I am pretty new to excel and i got a problem i cant seem to figure out no matter how many posts about it i have read.
Ultimately i want to make excel show me the last number in a column that is not 0.
But before i go there i wanted it to show me the last number in the column.
But no matter what i try and do. And how many guides and posts i read/watch. I get the same error all the time.
This is a screenshot of the error i always get no matter what formula i use LOOKUP/OFFSET/INDEX
If anyone can guide me in the right direction i would appreciate it.
Thanks in advance
Marc
Jan 28 2021 12:31 AM
Use
=LOOKUP(9.99999999999999E+307,G:G)
You can use a finite range instead of the entire column, of course.
The last non-zero number is
=LOOKUP(2,1/(G:G<>0),G:G)
Jan 28 2021 12:41 AM
Thanks for the quick reply.
What im experiencing seems to be something else. Even when i past those formulas they produce the same error as on the picture.
Jan 28 2021 12:47 AM
SolutionDo you use comma as decimal separator (e.g. three-and-a-half is 3,5)?
If so, you should use semi-colons instead of commas in the formulas:
=LOOKUP(9,99999999999999E+307;G9:G28)
and
=LOOKUP(2;1/(G:G<>0);G:G)
Jan 28 2021 12:51 AM
Jan 28 2021 12:47 AM
SolutionDo you use comma as decimal separator (e.g. three-and-a-half is 3,5)?
If so, you should use semi-colons instead of commas in the formulas:
=LOOKUP(9,99999999999999E+307;G9:G28)
and
=LOOKUP(2;1/(G:G<>0);G:G)