0

I have data in the format (Observed value, Number of observations) and would like to convert it to an array of values. For example, if I had (Value = 12, Number of observations = 3), I would like to use built-in formulas to create 3 cells each containing the number 12. Is this possible? This is an example of what I'm trying to do. Thanks!

P W
  • 3
  • 1
  • If you don't want to use VBA then you'll have to do it manually. The only thing which might facilitate your manual work (which I can currently think of) would be to `UNPIVOT` the table. There are plenty of examples and solutions on the web [including StackOverflow](http://stackoverflow.com/questions/tagged/unpivot%20excel?mode=all). The following solution might work for you: http://stackoverflow.com/questions/33507842/change-data-in-vertical-table-to-individual-rows – Ralph Jan 31 '17 at 00:48

1 Answers1

1

If you're willing to accept empty string instead of blank then, assuming your first column of inputs are in A1:A2, fill your output range for column A starting from A4 with:

=IF(ROW()-ROW(A$4)<A$2,A$1,"")

You can avoid the reference to the top cell by using:

=IF(ROW()-MIN(ROW())<A$2,A$1,"")

Make it an array formula (Ctrl+Shift+Enter) in the output range of column A.

You can copy either of these to the right to support additional columns.

Jon Davies
  • 487
  • 4
  • 11
  • 1
    Why an array formula? This modified formula from your `=IF(ROW()-ROW($F$1) – nightcrawler23 Jan 31 '17 at 01:36
  • You're absolutely right. I've edited my answer to reflect what you've used, and my original intention which removes the need to reference the first cell of the output range. – Jon Davies Jan 31 '17 at 09:39