Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

formula help but not vba please

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

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: 

 

1 Answer Found

 
Answer #1    Answered By: Damian Jones     Answered On: Feb 07

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.




Tagged:      

 
 
 
 

Related Post