I am building a formula to sum the last N entries that match a criteria on a list.
=SUMPRODUCT((A2:A23="joe")*(ROW(2:23)=LARGE((A2:A23="joe")*ROW(2:23),{1,2,3}))*B2:B23)
I have tweaked it to refer to the cell $f$2 where the last row is calculated.
=SUMPRODUCT((INDIRECT("A2:A"&$F$2)="joe")*(ROW(INDIRECT("2:"&$F$2))=LARGE((INDIRECT("A2:A"&$F$2)="joe")*ROW(INDIRECT("2:"&$F$2)),{1,2,3}))*INDIRECT("B2:B"&$F$2))
But I know that the array constant in the LARGE function in Excel is going to haunt me, people will want to change the number of entries dynamically.
Ideally, I'd like to be able to put in a ROW(INDIRECT("1:"&cell ref)) in place of {1,2,3}, however this doesn't work.
Even when I stare at it sternly.
I've tried a name range holding the constant, and that works - but cell references aren't accepted. So that doesn't get me any further along.
I've tried turning the ROW(INDIRECT("1:"&cell ref)) into a named range, but this fails with the N/A error when the LARGE function is applied to the previous ROW(INDIRECT.
Any suggestions on functions, syntax or general approach?
TIA
excel including a dynamic array of constants in a formula
-
- StarLounger
- Posts: 58
- Joined: 26 Jan 2010, 18:21
excel including a dynamic array of constants in a formula
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: excel including a dynamic array of constants in a formula
See the attached version. I created two dynamic named ranges: MyNames is A2:Am and MyValues is B2:Bm where m is the last used row.
The name you want to look for is in E7, the number of occurrences you want to count is in E8, and the sum in E9 is given by the following array formula confirmed with Ctrl+Shift+Enter. It is based on a formula by Aladin Akyurek.
=SUM(IF(ROW(MyValues)>=LARGE(IF(MyNames=E7,IF(ISNUMBER(MyValues),ROW(MyValues))),MIN(E8,COUNTIFS(MyNames,E7))),IF(MyNames=E7,IF(ISNUMBER(MyValues),MyValues))))
The name you want to look for is in E7, the number of occurrences you want to count is in E8, and the sum in E9 is given by the following array formula confirmed with Ctrl+Shift+Enter. It is based on a formula by Aladin Akyurek.
=SUM(IF(ROW(MyValues)>=LARGE(IF(MyNames=E7,IF(ISNUMBER(MyValues),ROW(MyValues))),MIN(E8,COUNTIFS(MyNames,E7))),IF(MyNames=E7,IF(ISNUMBER(MyValues),MyValues))))
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: excel including a dynamic array of constants in a formula
Which version of Excel does it need to work with?
Regards,
Rory
Rory
-
- StarLounger
- Posts: 58
- Joined: 26 Jan 2010, 18:21
Re: excel including a dynamic array of constants in a formula
Good catch Rory, I forgot to mention that - Excel 2013 and above.
-
- StarLounger
- Posts: 58
- Joined: 26 Jan 2010, 18:21
Re: excel including a dynamic array of constants in a formula
HANS you are amazing. I am going to think about this now ;)
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: excel including a dynamic array of constants in a formula
Microsoft 365 has a new function FILTER that could be used, but that won't work in Excel 2013, 2016 and 2019 (the latter in the perpetual license version of Office)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 58
- Joined: 26 Jan 2010, 18:21
Re: excel including a dynamic array of constants in a formula
Things are always more challenging when multiple versions of office are in use
I thought I'd share a couple of pieces of the formatting I used in the final spreadsheet, just in case someone is reading this thread looking for inspiration.
The actual spreadsheet is being used for data collection, the users want to use some N number of entries to establish the baseline values. That will be the label used - not "joe"
I wanted to mark where the baseline entry collection started AND which entries are being used as baseline values. So I did what you see in the attached spreadsheet. I ended up using hidden columns to accomplish this, instead of my normal preference for a range formula. Even though the BaselineStart formula works in cell, it doesn't work when used in conditional formatting. (This was in Office 365, so is it a glitch or who knows). Anyhow, I needed to get this out the door, so hidden columns it is.
Thanks again for the help.
I thought I'd share a couple of pieces of the formatting I used in the final spreadsheet, just in case someone is reading this thread looking for inspiration.
The actual spreadsheet is being used for data collection, the users want to use some N number of entries to establish the baseline values. That will be the label used - not "joe"
I wanted to mark where the baseline entry collection started AND which entries are being used as baseline values. So I did what you see in the attached spreadsheet. I ended up using hidden columns to accomplish this, instead of my normal preference for a range formula. Even though the BaselineStart formula works in cell, it doesn't work when used in conditional formatting. (This was in Office 365, so is it a glitch or who knows). Anyhow, I needed to get this out the door, so hidden columns it is.
Thanks again for the help.
You do not have the required permissions to view the files attached to this post.
-
- StarLounger
- Posts: 58
- Joined: 26 Jan 2010, 18:21
Re: excel including a dynamic array of constants in a formula
They loved it so much, that now they want to chart it...
-
- Administrator
- Posts: 78448
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: excel including a dynamic array of constants in a formula
What do they want to chart?
Best wishes,
Hans
Hans