23.7 C
New York

How to return a column of n consecutive values in Microsoft Excel

Published:


Microsoft excel icon
Image: PixieMe/Adobe Stock

The fill deal with in Microsoft Excel accommodates most serial record necessities, and it’s one of the primary options customers find out how to use. By serial, I imply a record of consecutive values, akin to 1, 2, 3, 4 and so forth. Microsoft Excel refers to these lists as a sequence.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

As highly effective as it’s, it received’t deal with each serial record you want. Let’s suppose you want a sequence the place every serial worth is repeated n instances. If n is 5, the record could be 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3 and so forth. In this tutorial, I’ll present you ways to mix two easy Excel capabilities, ROW() and ROUNDUP(), to return a sequence of n consecutive values.

I’m utilizing Microsoft 365 on a Windows 10 64-bit system, however you possibly can person earlier variations. Excel for the online helps each capabilities.

How to use ROW() and ROUNDUP() collectively in Excel

The expression we’ll use combines Excel’s ROW() and ROUNDUP() capabilities. Let’s study a bit about these two capabilities earlier than utilizing them.

Excel’s ROW() operate returns the row quantity of a reference, utilizing the syntax ROW([reference]) the place reference is non-obligatory. When omitted, ROW() returns the row quantity of the present cell. When reference is a vertical vary, ROW() returns the row numbers in a vertical array. For instance, =ROW() entered into C2 returns 2. Whereas ROW(D2:D5) will return the values 2, 3, 4 and 5 in a column, as proven in Figure A.

Figure A

ROW() returns an array of values equal to the corresponding row quantity.

We’ll additionally use Excel’s ROUND() operate, which rounds a worth up, away from 0. This operate makes use of the next syntax ROUND(quantity, num_digits) the place Excel requires each arguments. Number will be any actual quantity or reference to a quantity.

num_digits is the quantity of digits that you really want to around the quantity. For instance, Figure B reveals this operate rounding up the quantity 2 with varied decimal values. You can change the outcomes by altering the num_digits argument, which in this case is 1.

Figure B

ROUNDUP() rounds up to the required digit.

Because num_digits is 1, ROUNDUP() returns a single decimal digit when rounding warrants. If num_digits have been 2, the operate would return two decimal digits the place warranted.

Neither of these capabilities appears to assist a record of serial values, however mixed, that’s precisely what they do.

How to return a set quantity of consecutive numbers in Excel

By combining ROW() and ROUND() with a couple of enter values, you possibly can create a very versatile consecutive numbering operate. You’ve in all probability already thought of dividing by n, which is an element of the answer, however alone, it doesn’t work, as you possibly can see in Figure C. In this case, n is 5, subsequently, 1/5 is .20. 2/5 is .40, and so forth.

Figure C

Begin by dividing ROW() by n.

This is the place ROUNDUP() comes in. Figure D reveals the outcomes of utilizing ROUNDUP() on the easy expression ROW()/n.

Figure D

Add ROUNDUP() to power decimal values to integers.

Let’s consider the expression in row 1:

=ROUNDUP(ROW()/5,0)

=ROUNDUP(1/5,0)

=ROUNDUP(0.2,0)

1

The num_digit argument, 0 will return solely integers, and the closest integer to .2, rounding up is 1. Rows 1 by 5 can even return 1. Now let’s take a look at how the operate evaluates in row 6:

=ROUNDUP(ROW()/5,0)

=ROUNDUP(6/5,0)

=ROUNDUP(1.2,0)

2

The expression works as required, nevertheless it isn’t versatile sufficient as a result of it all the time returns a set of 5 serial values. Fortunately, we are able to repair that by including enter values.

How to add enter values to make the expression extra versatile in Excel

So far, the expression returns consecutive integers in teams of 5, however what in order for you to modify this expression so n actually is n? The addition of an enter worth will make this expression versatile sufficient to deal with this request.

Figure E reveals the easy setup. C1 is the enter worth for n. When C1 is clean, nothing occurs as a result of the IFERROR() operate hides the division by 0 error. This operate isn’t crucial, however you in all probability received’t need to show a record of error values — it’s up to you.

Figure E

Include an enter worth for expression n.

By coming into 5 in C1, the expressions in E1:E10 — =IFERROR(ROUNDUP(ROW()/$C$1,0),"") — return the 2 teams of 1s and 2s we noticed earlier. Change the worth in C1 to 2, and the expression returns a record of 5 teams as proven in Figure F. If you enter 1 for n, the expression returns 1, 2, 3, 4 and so forth.

Figure F

Change the n enter worth in C1 to change the grouped record of consecutive values.

The enter worth will be any entire quantity, constructive or adverse.

This isn’t the one means you may get a record of n consecutive values, nevertheless it’s fast and straightforward.


“If you may have any Query Related This Post then right here is the Source Link

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Related articles

Recent articles