Solved

Drag excel formular and auto increment a number

Posted on 2013-12-13
493 Views
Hi all,

I am tring to copy the below code down in my exel spread sheet so it does the following
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),1)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),2)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),3)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),4)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),5)
------------------------------------------------------------------------------
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),1)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),2)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),3)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),4)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),5)
------------------------------------------------------------------------------
(--- indicates a empty row)

I have tried to drap and drop it down so the number after Result!\$N\$2:\$N\$1000= increases each time but it just loops round!!!! any ideas how i can just copy it down???
0
Question by:runnerjp2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 92

Expert Comment

ID: 39717113
A sample worksheet would be helpful :)
0

LVL 11

Accepted Solution

Angelp1ay earned 250 total points
ID: 39717131
I would use Excel formulas to write those formulas, copy-paste the result out to notepad, and finally copy-paste back into a new column in Excel.

Round-tripping through notepad extracts the resultant text only on the way out and interprets as a formula on the way in.

See attachment for use and below code block for main concept...
``````=IF(A1<>"","=LARGE(IF(Result!\$N\$2:\$N\$1000="&A1&",Result!\$O\$2:\$O\$1000,""""),"&B1&")","")
``````
ComplexFormulaPaste.xlsx
0

LVL 14

Assisted Solution

Faustulus earned 250 total points
ID: 39721095
Basically,
``````=MOD(ROW(),6)
``````
will do the job. It will produce the numbers 1 to 5 in rows 1:5, a zero in row 6 (which is blank in your worksheet) and resume with 1 thru 5 in row 7 etc.
If your first formula isn't in row 1 you should reduce the result returned by ROW() by a number equal to the number of rows above the first formula. For example,
``````=MOD(ROW()-2,6)
``````
would be the formula if the first of your formulas is in row 3.
Here is your formula with the above idea incorporated:-
``````=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),MOD(ROW()-2,6))
``````
0

LVL 14

Expert Comment

ID: 39721110
BTW, you could use a similar system to specify the result you are looking for - the 7 in
Result!\$N\$2:\$N\$1000=7.
This formula will count from 0 upward each 6 rows.
``````=INT(ROW()/6)
``````
Again, you can adjust the result to start counting at a row other than 1 in the manner already described. Similarly, if you need to start counting with 7 this formula would do the job, =7+INT(ROW()/6). Note that this method would return the wrong result for each row number divisible by 6. That can be compensated by enlarging the formula a little, but in your case that is not necessary because precisely those rows are blank.
Here is your formula with the above thought incorporated:-
``````=LARGE(IF(Result!\$N\$2:\$N\$1000=INT((ROW()-2)/6),Result!\$O\$2:\$O\$1000,""),MOD(ROW()-2,6))
``````
0

LVL 47

Expert Comment

ID: 39808433
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses
Course of the Month7 days, 20 hours left to enroll