Array MAX Formula Problem 1

[Chance1234]

I have a range of Data called m_data which starts in C3 and is defined by the following

=OFFSET(Main!$C$3,0,0,COUNTA(Main!$C$3:$C$65000),1)

I then have m_data_step1 and m_data_step_m1 which are defined by the following respectivly

=OFFSET(Main!$C$3,0,0,COUNTA(Main!$C$3:$C$65000)-1,1)
=OFFSET(Main!$C$4,0,0,COUNTA(Main!$C$3:$C$65000)-1,1)

I Have an array formula as follows

{=MAX(m_data_step1-m_data_step_m1)}


All Fine up to this point.

However, I have some constraints i wish to introduce to m_data that the values have to be above or below a certain value.

These are ranges

s_high & s_low

So i have tried

{=MAXA((IF(m_data_step1<=s_high,IF(m_data_step1>=s_low,m_data_step1)))-(IF(m_data_step_m1<=s_high,IF(m_data_step1>=s_low,m_data_step1))))}

But..

If the value before does not match the criteria , it produces false. So the Max turns out higher than expected. What I want it to do is go back to the previous row where there is a value.


For example

If you copy the following into C3

64
73
63
59
70
60
55
68
71
74
60
69
59
61
57
70


Then Define the ranges as above

s_high and slow can go anywhere

the first formula

{=MAX(m_data_step1-m_data_step_m1)}

returns 13 which is correct

I have set s_high and s_low to

70 & 59

=MAXA((IF(m_data_step1<=s_high,IF(m_data_step1>=s_low,m_data_step1)))-(IF(m_data_step_m1<=s_high,IF(m_data_step1>=s_low,m_data_step1))))

returns 69

when it should return

9


Chance,

F, G + 3MSTG

 

[AnotherHiggins]

Given your data,

{=MAX(m_data_step1-m_data_step_m1)} returns 13, not 14 for me

and
{=MAXA((IF(m_data_step1<=s_high, IF(m_data_step1>=s_low, m_data_step1)))-(IF(m_data_step_m1<=s_high, IF(m_data_step1>=s_low, m_data_step1))))} returns 68, not 69.

But I see what you're getting at.

The problem is this sequence in C10 and C11:
[tab][red]68
[tab]71[/red]

The 68 is between high and low, so it evaluates to 68. But the 71 is not between the high and low, so it evaluates to FALSE.

68 - FALSE = 68 - 0 = 68.

That isn't really a solution, but at least that explains why it doesn't work....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Chance1234]

Yeap, Im trying to think; there must be a way to maybe use the offset or index functiosn to get back to the last cell.

Chance,

F, G + 3MSTG

 

[combo]

The function needs to be modified: add values for FALSE conditions and modify ranges in second part (hope that this was your intention):
{=MAXA((IF(m_data_step1<=s_high,IF(m_data_step1>=s_low,m_data_step1,s_low),s_high))-(IF(m_data_step_m1<=s_high,IF(m_data_step_m1>=s_low,m_data_step_m1,s_low),s_high)))}

combo

 

[Chance1234]

That might actually work for my purposes, thanks


Chance,

F, G + 3MSTG