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 Code for pasting a set of formulas down a data range
- cells containing formula that refer to user-defined VBA function
- VBA variable range in a formula
- VBA or array formula?
- VBA code to add values in two cells which have formulas
- Copy down formula with vba
- VBA Formula prob
- Getting a formula to copy from inside VBA
- Formula using range name in VBA code
- 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)
- 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
- writing vba code to generate vba code
- Placing VBA Code "Live" Into Another WkBook Via VBA
- VBA Help With Chart Object Problem
- wondering if I could get some macro/vba help
- description/help with own VBA functions in Excel
- Help with VBA function checksum
- VBA- HELP NEEDED TO LEARN VBA