Auto select the bottom number from a list

[Hawkmoth 2]

Hello All - I'm actually returning to the forum after a long absence, it's good to back.

I wonder if someone can help with this problem I have.

I have a list of numbers which I'm continuously adding another number to the bottom of the list each day. If possible I would like a formula which will automatically select the bottom most number on the list then give a result based on what percentage this is of the topmost figure in the list, thanks.

hawkmoth 2

[AliGW]

Welcome back, but I have to ask why you have opened a new account?

There are instructions at the top of the page explaining how to attach your sample workbook.

[Pete_UK]

Assume you have a heading in A1, so that your first number is in A2, then you could use this formula (not in column A):

=INDEX(A:A,COUNTA(A:A))/$A$2

This assumes that there are no blanks in column A, i.e. the data is contiguous.

Hope this helps.

Pete

[Hawkmoth 2]

Hi AliGW

The system didn't recognise me, therefore I couldn't log in with my original details - I was asked to create a new account account, which I did.

Hawkmoth 2

[AliGW]

OK - maybe that's because you used a different E-mail address?

Not to worry.

[Pete_UK]

Did you see my suggested solution in Post #3? Just format the cell with the formula in as a percentage with appropriate decimal places.

Pete

[Hawkmoth 2]

Hello Pete_UK


Ok, to elaborate, I have these lists in 4 columns of numbers in columns B, C, D & E, there won't be any gaps between the numbers, and the topmost figure could be in row 3, however, not all the lists will start in row 3, some will have first entries in other rows.

Hawkmoth 2

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Pete_UK]

That's a bit different than what you said in your first post. Time to attach a sample Excel workbook so I can make more sense of what you have and what you are trying to achieve - please follow the details given in the yellow banner at the top of the screen.

Pete

[Hawkmoth 2]

Hello Pete-UK, sorry about any confusion I created, I was trying to fast track the answer.

I have attached the the worksheet containing my problem, thanks.

Hawkmoth 2

[AliGW]

You've attached a workbook in the old .xls format - but you have Excel 2016. Why?

[AliGW]

In B25 (or wherever) copied across:

=INDEX(B$3:B$24,MATCH(99^99,B$3:B$24))/INDEX(B$3:B$24,MATCH(1,SIGN(B$3:B$24),0))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Format cells as PERCENTAGE.

[Hawkmoth 2]

Hi AliGW - I've done as requested, but I'm getting a #VALUE! return.

[AliGW]

Attach your attempt - I don't have a crystal ball, sorry.

You will see from my attachment that it works on your data ... If you are trying it on different data, then you will need to adapt it accordingly.

AliGW on MS365 Insider (Windows) 32 bit

	A	B	C	D	E
1		Pair 1 released at this site on 15-09-21	Pair 2 Group release at this site on 14-09-21	Pair 3 released on 17-09-21 14 birds	Pair 4 released at this site on 21-09-21 15 birds
2		Family - yellow leg ring	Group - orange leg ring	Family - green leg ring	Family - pink leg ring
3	17-09			14
4	18-09			14
5	19-09			8
6	20-09			6
7	21-09			5	14
8	22-09			4	14
9	23-09			4	13
10	24-09			4	13
11	25-09			5	8
12	26-09			5	8
13	27-09			5	8
14	28-09		10	4	6
15	29-09	15	10	4	6
16	30-09	14	10	4	6
17	01-10	14	10	3	6
18	02-10	14	5	1	6
19	03-10	11	5	3	6
20	04-10
21	05-10
22	06-10
23	07-10
24	08-10
25		73%	50%	21%	43%

Sheet: Daily % seen

[Pete_UK]

When I open Ali's file I see that the formulae in row 25 are actually array-formulae, though Ali did not point this out. Office 365, which she is using, takes care of these automatically, but in XL2016 you will need to commit the formula in B25 using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter. Click on B25 and then press the F2 key as if to edit the formula, then use CSE. You can then copy the formula across as required.

Hope this helps.

Pete

[AliGW]

Argh! Good spot, Pete!

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[Hawkmoth 2]

Many thanks indeed to both Pete_UK & AliGW the fix above worked a treat - great job.

Hawkmoth 2

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.