Picking Highest numbers out of List

JBF · #1 03-07-2018, 06:36 PM

Hi all,
I'm stuck trying to figure out how to pickout the highest numbers from a list.

Say I have a list of numbers in column A, Starting at A1 and going down up to A500. looking like this:

1
2
3
4
5

0
1
2
3
4
5
6
7
0
1
2
0
0
1
2
3
0

The sequential numbers can go up to any number. Zero is when the list starts over at 1 and is random. I would like to have the highest numbers, the numbers just before the zeros added up and placed in a cell. there could be any number of zeros, and there could be back to back zeros and the sequential numbers could go up to 50, 75 100 or more.

Does this make sense?

thanks for help...
JBF

ArviLaanemets · #2 03-08-2018, 12:11 AM

Let's say, you numbers are in range A2:A21.
Into second column enter the formula (p.e. into cell B2)

Code:

=(SUM($A3)=0)

NB! The formula refers to next row in column A!
Copy the formula down into range B2:B21.

Into any cell where you want the end result, enter the formula

Code:

=SUMIF($B$2:$B$21,TRUE,$A$2:$A$21)

JBF · #3 03-08-2018, 04:29 PM

Quote:

Originally Posted by ArviLaanemets

Let's say, you numbers are in range A2:A21.
Into second column enter the formula (p.e. into cell B2)

Code:

=(SUM($A3)=0)

NB! The formula refers to next row in column A!
Copy the formula down into range B2:B21.

Into any cell where you want the end result, enter the formula

Code:

=SUMIF($B$2:$B$21,TRUE,$A$2:$A$21)

ArviLaanemets,
Thanks for the post. Your formula works, but I guess I should have been more specific... your method while it works, really won't work for what I need, it will clutter up she sheet making it confusing.

I have attached a sample spreadsheet to illustrate. My numbers go from 1 to 75 across the top starting at B1. picks start at A6 and will go down many rows... 100 or so.

is there a way to accomplish the same results that your formula returned, but a different way or with a Macro?

Thanks again for you efforts.
JBF

ArviLaanemets · #4 03-09-2018, 01:47 AM

In attachment are 2 variants of calculation

ArviLaanemets · #5 03-09-2018, 02:03 AM

There was a typo in formulas in previous post attachment. Here is correct one.

JBF · #6 03-09-2018, 03:28 PM

Quote:

Originally Posted by ArviLaanemets

There was a typo in formulas in previous post attachment. Here is correct one.

Ok, that works. Thank you for helping me with this!!

JBF · #7 03-10-2018, 06:35 PM

Quote:

Originally Posted by ArviLaanemets

There was a typo in formulas in previous post attachment. Here is correct one.

ArviLaanemets,
Do you think you could come up with a way where on Sheet1 instead of the numbers counting up or increment before the zero hits, just having the highest number returned.

I have updated the Random spreadsheet and uploaded it. I added sheet4, basically a copy of sheet1, with modifications and details. I outlined what I would like on that sheet.. I hope it makes sense to you..

Thank you for your efforts.

JBF

ArviLaanemets · #8 03-12-2018, 01:14 AM

Probably with another hidden sheet - yes. But I don't have a clue how you get numbers into in your table on Sheet4 or what they mean!

To get only last number before 0 returned is easy. Look at attached version. (I renamed sheets, removed variant with empty cells equaled with 0's, and defined Tables on all sheets. With Tables defined, on hidden sheets ranges with prepared formulas will be easier to find, and on Main sheet formulas are added automatically whenever you add a new pick.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Picking value from a certain cell under certain conditions	kao	Excel	1	09-07-2017 09:34 AM
Average of highest 7 out of nine quizzes	wallace990	Excel	2	05-16-2017 01:51 PM
Fetch highest number	am_gup123	Excel	2	03-23-2017 01:56 AM
Highest & lowest Numbers	ibrahimaa	Excel	3	04-23-2012 07:52 PM
Page number picking up heading numbering?!	Ulodesk	Word	0	09-24-2009 01:56 PM