Search:

# formula help but not vba please

Asked By: Raju    Date: Feb 07    Category: MS Office    Views: 2302

I need to look in a column of random numbers from 1 -100 and change the 1st
occurence of a number larger than 50 to 100 leaving all the other numbers
intact, bearing in mind that the minimum number greater than 50 may occur more
than once.

Example
1,7,8,8,51,51,51,78,90
would change to
1,7,8,8,100,51,51,78,90

any thoughts highly appreciated!

Share:

Are you sure we can't interest you in a VBA solution? [;)]

Okay, if your list is sorted as in your example, then try this. For
example, place the original list in a column  starting at A1. In B1 place
the formula
=IF(A1>50,100,A1)
For the rest of column B (starting with B2 and dragging down) use the
formulas
=IF(AND(A2>50,MAX(B\$1:B1)<100),100,A2)
=IF(AND(A3>50,MAX(B\$1:B2)<100),100,A3)
...and so on...

Didn't find what you were looking for? Find more on formula help but not vba please Or get search suggestion and latest updates.