how to have existing formula change by entering numbers into two separate cells

[drooperman]

I was wondering if someone could please help me. I have a sheet with numbers data that I am calculating through various formulas such as sums and ranges and averages and sort functions etc. My problem at the moment is that I have for instance in Column Y a set of numbers that is the main Numbers references that I entered in manually such as 1 to 10 or 4 to 19 or 19 to 29 etc. this list varies on different sheets and will never change after I have entered them the first time, as they are the main numbers. then I have the data in column Z that will change as I enter new figures into the chosen cells on other sheets. lets say for instance on sheet one these main numbers in column Y are 1 to 10. Next to each of these 1 to 10 numbers in column Z is the results of my sorting or range or xlookup formulas that I have asked for that is delivered to me. Now out of this data in column Z (next to the numbers 1 to 10), I currently have a sort formula that sorts this data into top 3 numbers for me. this sort formula is looking throughout all the 1 to 10 numbers and their accompanying data in column Z to find the top 3 numbers and then shows me the top three numbers out of 1 to 10 with its accompanying data next to it. [=SORT(FILTER(V197:W205,W197:W205>=LARGE(W197:W205, 5)), 2, -1)] The Problem I face is that sometimes this sort formula gotta be between main numbers 2 and 8 or 1 and 7 or between 3 and 9, it doesn't stay always just 1 to 10, which then requires me to manually edit the formula every time which is a lengthy process since there are over 40 columns of such data. Is there a way that I can make this formula work by me entering the start number (2) and end number (8) into two different cells which will tell this Sort formula where to start and end the top 3 numbers search? thanks in advance for all your efforts.

 

[drooperman]

That example looks a little more like what I was expecting.
However, why do you have 45 ranked in the top section when it does not meet the "top 4 results" restriction?
If you do want that ranked, why does it get ranked 5? And what if there was more than one value not included in the "top n results"? How would you rank multiples of those?

BTW, you don't need those separate From/To values, you just need to use >= and <=

=AVERAGEIFS(Z2:Z11,Y2:Y11,">=" &W21,Y2:Y11,"<=" &AA21)

Hi Peter,

thanks for that. All of these is used on a job card service system that we created to Analyse and assess a client's business service over the past 25 years. the client has been using excel for as long as it existed and we even entered 5 more years of manual written job cards into the spreadsheet database. I created this sheet as a XL2BB sharable template to be able to find solutions to each problem i found while trying to analyse and assess this clients data which I then combined these problems into one sheet template to show you what I am struggling with.

The sheet that I am showing you is for example purposes. if I had to show you a snippet of the real sheet then nothing will make sense.

the rank section on this sheet is NOT related to the top 4 results. It will only work with the FROM and TO box sections and will never be affected by the top 4 section.

The rank section is the data set of how often these specific numbers occurred in which priority specifically hence why I need it to change with the FROM and TO numbers.

I need to Rank numbers ranging as per the FROM and TO box specifically. I am not sure how to deal with this problem though.

I am not sure on how to deal with the multiples...

what you reckon?

 

[Peter_SSs]

Could we do something like this?
Columns AC:AD could be hidden if you want.

drooperman.xlsm
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1
2	RANK OF DATA SET									37	0			41	6
3										38	0			42	7
4										39	0			43	8
5										40	0			44	2
6										41	3			45	1
7										42	2
8										43	1
9										44	4
10										45	5
11										46	0
12											3	AVERAGE:
13
14
15
16
17
18
19						ENTER FROM RANGE & TO RANGE:
20								From:				To:
21								41				45
22
23								Top Results:
24								4
25
26				DATA SET:						37	3
27										38	9
28										39	5
29										40	4
30										41	6
31										42	7
32										43	8
33										44	2
34										45	1
35										46	10
36
37
38				DATA SET SORT						43	8
39										42	7
40										41	6
41										44	2
42
Sheet1

Cell Formulas
Range	Formula
AC2:AD6	AC2	=LET(r,INDEX(Y$26:Z$35,SEQUENCE(AA$21-W$21+1,,MATCH(W$21,Y$26:Y$35,0)),{1,2}),r)
Y3:Y11,Y27:Y35	Y3	=Y2+1
Z2:Z11	Z2	=IFNA(RANK(VLOOKUP(Y2,AC$2#,2,0),INDEX(AC$2#,0,2)),0)
Z12	Z12	=AVERAGEIFS(Z2:Z11,Z2:Z11,"<>0")
Y38:Z41	Y38	=INDEX(SORT(FILTER(Y26:Z35,(Y26:Y35>=W21)*(Y26:Y35<=AA21),""),2,-1),SEQUENCE(W24),{1,2})
Dynamic array formulas.

 

[drooperman]

Could we do something like this?
Columns AC:AD could be hidden if you want.

We can, then I will have to formulate a =IF( statement for the Z column to show whichever number appears in the AD column next to its corresponding numbers in Z column. so yes this is a solution we can hide the AC:AD column or grey it out.

But I noticed that the numbers from your AC:AD does not match the rank numbers from my Z column, which means your formula is at the moment still ranking the 41 to 45 against all the numbers from 37 to 46.. is there a way around that so it only ranks the given FROM and TO numbers against each other?

 

[Peter_SSs]

You have lost me!
In post #19 you showed me what you wanted in the top section - at least that is what I have been asking for all along (expected results & layout)

My suggestion in post #22 produced these results:

I fail to see where they are different.

 

[drooperman]

You have lost me!
In post #19 you showed me what you wanted in the top section - at least that is what I have been asking for all along (expected results & layout)

oh boy.. apologies for that Peter. I only looked at the AC:AD columns and not the Z column.

My mistake. yes then that's it that's the final solution. PERFECT..!

Thanks for that. You're truly my hero. Thanks for Everything.

I am going to doing some editing and testing everything in the real sheets and see what results I get. Very Exciting...

Thanks

 

[Peter_SSs]

oh boy.. apologies for that Peter. I only looked at the AC:AD columns and not the Z column.

Phew! - I'm glad it was as simple as that! ?

You're welcome. Hope it works in the "real world"! ?