Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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!

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.

Related Topics:

- Please Help me in this Formula
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Formula using range name in VBA code
- VBA or array formula?
- VBA code to add values in two cells which have formulas
- Copy down formula with vba
- cells containing formula that refer to user-defined VBA function
- VBA Code for pasting a set of formulas down a data range
- VBA variable range in a formula
- Paste Formula help
- Can Any one help me writing logic for the given formula
- Disabling Formulas, Not calculation
- please help - why this is not work (sub string in string)
- Placing VBA Code "Live" Into Another WkBook Via VBA
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Convert code from excel 2000 vba to excel 2003 vba
- writing vba code to generate vba code
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Help needed for a VBA novice with excel charting
- Newbie needs help with VBA Greek
- Needs Help on Time conversion using VBA
- very basic VBA help requested
- VBA Help With Chart Object Problem