Formula - Stumped

[Qcann]

Hi,

I just cannot figure this one out. I know it's simple. Multiple if statements ??

See attached.

The numbers highlighted in yellow have been manually entered. This is the output I am looking for. Looking for a formula that will return "1" when the cell in column "A" = 1, otherwise if the cell in column "A" is greater than 1 that number is to be returned, and if the cell = 0, then it is to return the cell prior that is greater than 1.

So.... B3 would return 2 and cells b1 & b22 would return 3.

This is only a snippet. The numbers in column A can go up to 20 in some cases.

Thanks.

 

[Haz]

=IF(A2=0, INDEX($A:$A, MAX(($A$2:A2>1)*ROW($A$2:A2))), MAX(1, A2))

Enter the formula in cell B2 and confirm with CTRL+SHIFT+ENTER, copy and paste for the other cells

 

[Peter Bartholomew]

This is essentially a 'fill down' operation, though in that case blank might be more common than 0. I have used names, array formula and a volatile function though if one chose to do so any could be substituted by direct referencing, relative references and the OFFSET function is used for clarity rather than any necessity.
= IF( highest, highest, previous.output )
where 'previous.output' is defined to refer to
= OFFSET( output, -1, 0 )

Note: the first use of 'highest' treats 0 as FALSE and any other number as TRUE.

 

[Qcann]

Thanks Haz... This seems to work perfectly. It wasn't so simple after all.

Thanks you as well Peter.

 

[Qcann]

Sorry Haz. I take that back. It does not work. Can you please take a look at your code again ?

Peter - Although your output is correct, I cannot seem to replicate it when filling down with more data.

 

[bosco_yip]

Or..............

try this non-array formula

In D2, copied down :

=IF(A2=0,1/LOOKUP(2,1/A$1:A1),A2)

Regards
Bosco

 

[Qcann]

Or..............

try this non-array formula

In D2, copied down :

=IF(A2=0,1/LOOKUP(2,1/A$1:A1),A2)

Regards
Bosco

The formula is beyond me - but it works !

Many thanks.

 

[Peter Bartholomew]

Qcann
The array formulas cannot be extended simply by filling down. You can extend the formatting that way but the formula itself needs to be committed with the entire range selected and using Ctrl+Shift+Enter. Just about as inconvenient as Microsoft could make it!

The blue column is an alternative that uses relative referencing to produce terms of an array one by one. That version can be filled. The key reference is 'above' that refers to the cell immediately above the formula cell (as a relative reference).

 

[Peter Bartholomew]

This is the first time I have used direct referencing in 5 or more years! [I have been known to describe it as an 'abomination'; a large part of the reason that many IT professionals view Excel with utter contempt!]

The formula is very simple because the output column accumulates the result you need and the lookup to the original number in column A is not needed; a copy can be found in column B immediately above the active cell.

The formula is
= IF(A2, A2, B1)
and fill down.

Bosco's formula might be a little easier to understand if the result was referenced rather than calculated as the reciprocal of the lookup vector.

=IF(A2=0,LOOKUP(2,1/A$1:A1,A:A),A2)

 

[Haz]

Sorry Haz. I take that back. It does not work. Can you please take a look at your code again ?

Peter - Although your output is correct, I cannot seem to replicate it when filling down with more data.

bosco's formula already does it perfectly, but here it is