Function with start and limits

[managingcrap]

I have 1, 2, 3, 5, 8, 9, 9, 9, and 2 on A1, B1, C1, D1, E1, F1, G1, H1, and I1.

What I want to happen is the last three cells before the first 9 hits, will result in 25 on those three cells on row 2 and that anything after the first 9 will not result to anything (or should be blank).

So, given the example above I'm looking at this
A B C D E F G H I
1 1 2 3 5 8 9 9 9 2
2 0 0 25 25 25

C2, D2, and E2 resulted to 25s, because it's the last three prior to the first 9.

You'll see that the three cells prior to the first 9 resulted to 3 25s but nothing after that first 9 hits. I just put 0s on the first two because when I saved it the first time without the zeros the three 25s moved to the left.

Thank you.

[XOR LX]

Hi,

You don't say what the cells prior to the first 25 should contain (you've put zeroes in there - is that correct, or should they too be blank?)

Regards

[managingcrap]

Oh sorry. They should be blank just like the rest of the cells after the last of the three 25s.

Thank you.

[XOR LX]

Thanks. Then, in A2 and copy across:

=IF(COUNTIF($A$1:$I$1,9)=0,"",IF(OR(MATCH(9,$A$1:$I$1,0)-COLUMNS($A:A)={1,2,3}),25,""))

Regards

[Richard Buttrey]

...or another option

Please Login or Register  to view this content.

[managingcrap]

Both worked.

XOR LX

By any chance can you explain to me what IF(OR(MATCH(9,$A$1:$I$1,0)-COLUMNS($A:A)={1,2,3}) is?

Also too, what does 0 on COUNTIF($R$36:$AO$36,24)=0 mean? When I put 1 or 1000, it doesn't do anything different but if I take it out from that equation/logic test, then the function doesn't work the way I want it too.

Thanks.

Thanks. Then, in A2 and copy across:

=IF(COUNTIF($A$1:$I$1,9)=0,"",IF(OR(MATCH(9,$A$1:$I$1,0)-COLUMNS($A:A)={1,2,3}),25,""))

Regards

[XOR LX]

Sure.

MATCH(9,$A$1:$I$1,0) will simply find the relative position of the first 9 in the range A1:I1. So, if, for example, the first 9 occurred in F1, this part of the formula would return 6.

The COLUMNS($A:A) part will give the number of the column in which the formula, when dragged across, is placed. Since the COLUMNS function returns the number of columns between two ranges, and, as, when copied to the right, this part will become successively:

COLUMNS($A:B)
COLUMNS($A:C)
COLUMNS($A:D)

...etc. (thanks to the correct use of relative referencing - the 'dollar' signs)

the result of this part will be 1,2,3,..., etc., i.e. the relative position of the column in the range, if you like.

Since you want a 25 to be placed in those cells whose relative position to this first occurrence of a 9 is either 1, 2 or 3 columns to the left, in this example (with the first 9 in F1), we would want the 25s in the 3rd, 4th and 5th columns.

This is achieved by:

IF(OR(MATCH(9,$A$1:$I$1,0)-COLUMNS($A:A)={1,2,3})

which, in this example, results in, successively, from A2 to the right:

IF(OR(6-1={1,2,3})
IF(OR(6-2={1,2,3})
IF(OR(6-3={1,2,3})
IF(OR(6-4={1,2,3})
IF(OR(6-5={1,2,3})
IF(OR(6-6={1,2,3})
IF(OR(6-7={1,2,3})
IF(OR(6-8={1,2,3})
IF(OR(6-9={1,2,3})

which is:

IF(OR(5={1,2,3})
IF(OR(4={1,2,3})
IF(OR(3={1,2,3})
IF(OR(2={1,2,3})
IF(OR(1={1,2,3})
IF(OR(0={1,2,3})
IF(OR(-1={1,2,3})
IF(OR(-2={1,2,3})
IF(OR(-3={1,2,3})

which is then:

FALSE
FALSE
TRUE
TRUE
TRUE
FALSE
FALSE
FALSE
FALSE

and so TRUE for the formulas in the 3rd, 4th and 5th columns, and FALSE for the rest, as required.

Regards

[managingcrap]

Thank you very much.