Greenies08
New Member
- Joined
- Apr 4, 2011
- Messages
- 22
Hi there
I have some data that I want to pull out the 'max' and by 'who' within a defined month.
I've managed the max formula ok but struggling to build the month part into my index/match array.
Any help would be truly appreciated.
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="64" style="width: 48pt;" span="2"><colgroup><col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5595;"><tbody>
</tbody>
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>
</tbody>
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>
</tbody>
I have some data that I want to pull out the 'max' and by 'who' within a defined month.
I've managed the max formula ok but struggling to build the month part into my index/match array.
Any help would be truly appreciated.
03/01/2019 | A Smith | 1400 | 5005 | Shrub |
05/02/2019 | C David | 1400 | 5005 | Shrub |
08/01/2019 | F Carol | 1000 | 5000 | Tree |
14/01/2019 | G Williams | 1400 | 5000 | Tree |
18/01/2019 | B Smith | 750 | 5000 | Tree |
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="64" style="width: 48pt;" span="2"><colgroup><col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5595;"><tbody>
</tbody>
01/01/2019 | Arrays | |
Max Shrub | 1400 | MAX(IF((MONTH($A$1:$A$5)=MONTH($A$7))*($D$1:$D$5=5005),$C$1:$C$5)) |
Who | A Smith | IF(B$8=0,"",INDEX($B$1:$B$5,MATCH(1,(5000=$D$1:$D$5)*(B$8=$C$1:$C$5),0))) |
Max Tree | 1400 | MAX(IF((MONTH($A$1:$A$5)=MONTH($A$7))*($D$1:$D$5=5000),$C$1:$C$5)) |
Who | G Williams | IF(B$10=0,"",INDEX($B$1:$B$5,MATCH(1,(5000=$D$1:$D$5)*(B$10=$C$1:$C$5),0))) |
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>
</tbody>
01/02/2019 | ||
Max Shrub | 1400 | |
Who | A Smith | Should be C David? |
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>
</tbody>