Hello:
Please refer to attached file.
I have data from cell E5 down as shown.
I need formula in cell E1 to give the last row # which is greater then 0
In this case it will be 29
Let me know if you have any questions.
Thanks.
R
Hello:
Please refer to attached file.
I have data from cell E5 down as shown.
I need formula in cell E1 to give the last row # which is greater then 0
In this case it will be 29
Let me know if you have any questions.
Thanks.
R
Here is one way to do it, confirm with Ctrl Shift Enter:
Formula:Please Login or Register to view this content.
If you want the actual cell reference then:
Formula:Please Login or Register to view this content.
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
Hello ron:
Thanks a lot
R
Hello Ron:
Seems like it is giving wrong result.
Please refer to attached file.
Let me know if you have any questions.
Thanks.
R
Last edited by rizmomin; 04-26-2018 at 06:33 PM.
Hey, it's because it is taking those zeros as text. Convert them to numbers. If you must have them like that in text form then let me think of another way.
Secondly, because your using row 1 for you formula and the array range in the formula is starting at row 2, kindly change the part of the formula that has "+1" to +2:
Formula:Please Login or Register to view this content.
Hello Ron:
Yes some of them will be as TEXT, and so really would need the formula to accommodate.
Thanks
Riz
Try this
Formula:Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Here you go, you don't need to change the zeros to numbers:
Formula:Please Login or Register to view this content.
If you insist to put it on E1 (confirmed both formulas as array):
Formula:Please Login or Register to view this content.
EDIT: Sorry, you'll end up in trouble if any value in the range in a NUMBER ZERO. Let me see what else you can use.... Don't use the 2 formulas I just typed
Last edited by ron2k_1; 04-26-2018 at 06:54 PM.
Hello ALkey:
I need row # instead of actual cell values
Thanks
R
Sorry,
Formula:Please Login or Register to view this content.
Put this in E1:
=MATCH(LOOKUP(9E+99,E2:E100),E2:E100,0)+1
Hope this helps.
Pete
Hello ron & Alkey:
Please refer to attached file.
Still does not give correct answer.
Let me know if you have any questions.
Thanks.
It worked fine for me but try this
Formula:Please Login or Register to view this content.
Hello:
Please refer to attached file.
Look at your formula in cell H10
Let me know if you have any questions.
Thanks.
The formula I gave you in Post #10 returns value 36. I don't a see the problem there.
Wouldn't the answer be 28 in that file? (i.e. number greater than zero)
Pete
You want formula to return 28
Formula:Please Login or Register to view this content.
Hello:
I am sorry but i need the last row# with not zero, greater then zero.
In earlier attached sheet, last non-zero value is 4614.41 and thats the row# in need.
R
But that's row 28.
"Who's on first?"
Dave
Riz, at least one of us maybe drunk and it' not me
The formula form post 17 returns row 28. Row 28=4614.41
End of story.
Hello Alkey and others:
Now i got it right, just to clear up below formula works
Once again thanks to allPlease Login or Register to view this content.
Riz
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks