Hi All,
This is my first post so aplogies if I have messed up anything. I have searched for answers but can't find something sprecifically addressing this issue so if someone can help me out with this it would be much appreciated.
First, here is a sample dataset:
[TABLE="class: grid, width: 300, align: left"]
<TBODY>[TR]
[TD]1/1/2014[/TD]
[TD]31/1/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/2/2014[/TD]
[TD]28/2/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/3/2014[/TD]
[TD]31/3/2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/4/2014[/TD]
[TD]30/4/2014[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]1/5/2014[/TD]
[TD]31/5/2014[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/6/2014[/TD]
[TD]30/6/2014[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1/7/2014[/TD]
[TD]31/7/2014[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1/8/2014[/TD]
[TD]31/8/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1/9/2014[/TD]
[TD]30/9/2014[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1/10/2014[/TD]
[TD]31/10/2014[/TD]
[TD]10[/TD]
[/TR]
</TBODY>[/TABLE]
There is one non-numeric value in the series. I would like to find the maximum numeric value of the third column for any two dates I choose. If all of the values were numeric then I could simply use:
=SUMPRODUCT( MAX( (A1:A10>=StartDate) * (B1:B10<=EndDate) * C1:C10 )
where StartDate and EndDate are dates.
This works. But it does not work if there is a non-numeric value.
Generally, I can work around non-numeric values in a sumproduct with something like:
=SUMPRODUCT( --(A1:A10>=StartDate) , --(B1:B10<=EndDate) , C1:C10 )
This one obviously coerces the true/false into 1/0 but importantly it will not work with an asterisk - it needs commas separating the arrrays.
So - I can use sumproduct with non-numeric values and I can use the Sumproduct/Max for numeric values but I can't seem to do both - sumproduct/max with non-numeric values. If anyone can show me what I am missing it would be great. For exmaple, what formula would let me find the max of column C for dates between (and including) 1/1/2014 and 31/7/2014 but looking up the full range of data.
Note - please don't tell me it can be done with an array formula. I specifically want an answer that does not rely on an array formulae - if there is one.
Thanks in advance.
This is my first post so aplogies if I have messed up anything. I have searched for answers but can't find something sprecifically addressing this issue so if someone can help me out with this it would be much appreciated.
First, here is a sample dataset:
[TABLE="class: grid, width: 300, align: left"]
<TBODY>[TR]
[TD]1/1/2014[/TD]
[TD]31/1/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/2/2014[/TD]
[TD]28/2/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/3/2014[/TD]
[TD]31/3/2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/4/2014[/TD]
[TD]30/4/2014[/TD]
[TD]Black[/TD]
[/TR]
[TR]
[TD]1/5/2014[/TD]
[TD]31/5/2014[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/6/2014[/TD]
[TD]30/6/2014[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1/7/2014[/TD]
[TD]31/7/2014[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1/8/2014[/TD]
[TD]31/8/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1/9/2014[/TD]
[TD]30/9/2014[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1/10/2014[/TD]
[TD]31/10/2014[/TD]
[TD]10[/TD]
[/TR]
</TBODY>[/TABLE]
There is one non-numeric value in the series. I would like to find the maximum numeric value of the third column for any two dates I choose. If all of the values were numeric then I could simply use:
=SUMPRODUCT( MAX( (A1:A10>=StartDate) * (B1:B10<=EndDate) * C1:C10 )
where StartDate and EndDate are dates.
This works. But it does not work if there is a non-numeric value.
Generally, I can work around non-numeric values in a sumproduct with something like:
=SUMPRODUCT( --(A1:A10>=StartDate) , --(B1:B10<=EndDate) , C1:C10 )
This one obviously coerces the true/false into 1/0 but importantly it will not work with an asterisk - it needs commas separating the arrrays.
So - I can use sumproduct with non-numeric values and I can use the Sumproduct/Max for numeric values but I can't seem to do both - sumproduct/max with non-numeric values. If anyone can show me what I am missing it would be great. For exmaple, what formula would let me find the max of column C for dates between (and including) 1/1/2014 and 31/7/2014 but looking up the full range of data.
Note - please don't tell me it can be done with an array formula. I specifically want an answer that does not rely on an array formulae - if there is one.
Thanks in advance.