dynamic row cell counting

[l14mha]

Hi guy's,

if anyone can help me out i would really apprecicate it.

i need to find the number of cells between the smallest and largest values in an array (between 2004:2010) per row. this number will be used as a divider to calc a rate per year.

the divider column indicates the value required. i cant work out how to make the array dynamic.

i thought that it could look like count(small(A1:A7,1):large(A1:A7,1))-1 but it did noy not pick up small(A1:A7,1):large(A1:A7,1) as an array.

for example

2004, 2005, 2006, 2007, 2008, 2009, 2010, Divider,
0, 0.3, 0.6, 0, 0, 0, 0, 1,
0.5, 0.5, 0, 0, 0.7, 0, 0, 3,
0, 0.3, 0, 0, 0, 0.5, 0, 4,
0.1, 0, 0, 0, 0.2, 0, 0, 4,
0, 0, 0.9, 0, 1, 0, 0, 2,
0, 0.2, 0, 0, 0, 0.8, 0, 4,
0, 0, 0, 0.6, 0, 0, 1, 3,

sorry i can't seem to insert a table "," indicates next cell.

help would be fantastic, thanks in advance,

liam.

[DonkeyOte]

Posting a sample file will help more than trying to embed a table.

Your reference row 1 but table implies this is Year header ?

Assuming the intention is to determine Column of Max and the Column of the Min and subtract the latter from the former then:

H2: =MATCH(MAX(A2:G2),A2:G2,0)-MATCH(MIN(A2:G2),A2:G2,0)

of course if there is only one value result would be 0 and use of this value as divisor would lead to #DIV/0!

If the data points are always sorted in Ascending order then another alternative would be to use MATCH(9.99E+307,A2:G2,0) instead of MATCH(MAX...)

[l14mha]

thank you very much.. that works a treat.

the only bug is that 0,0,0.5,0.5,0,0,0.8 returns 4 and not 3. this is due to picking up on the left most 0.5 value and not the right.

can this be resolved?

thanks liam

[DonkeyOte]

If you have 0's in there then the MIN approach wouldn't work anyway as I see it.

Do you have negative values ?

Assuming

a) no negatives

b) zeroes to be ignored

c) last instance of MIN/MAX to be used

then

H2:
=LOOKUP(2,1/(A2:G2=MAX(A2:G2)),COLUMN(A2:G2))-LOOKUP(2,1/(A2:G2=SMALL(A2:G2,1+COUNTIF(A2:G2,0)),COLUMN(A2:G2))

[l14mha]

thank you very much,

i removed the 0's from my existing spread sheet and slightly modified your formula taking out the count if statement. they work well together now.

SOLVED.