I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.
1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00
Any help will be greatly appreciated!
It would be simplest probably to use a helper column, e.g. in C5 copied down use this formula:
=SUM(B1:B5)<100
Then you can use a formula like this to get the first column A value of the group (in this case 22)
=INDEX(A1:A30,MATCH(TRUE,C1:C30,0)-4)
Audere est facere
Or,
with DLL's logic in a single Array Formula*,
=INDEX(A1:A100,MATCH(TRUE,SUMIF(OFFSET(B1,ROW(B1:INDEX(B1:B100,COUNT(B1:B100)-4))-ROW(B1),,5),"<>0")<100,0))
*: confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Just one very minor thing with Haseeb A's formula, change the -4 to -5 and that will give the 5 highest values <100. Combine that with Dll's logic before.
These additions will give the addresses in column A that meet the criteria specified. Enter with Ctrl + Shift +Enter:
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks