Rasec Malkic
Reputation: 671
How to print consecutive numbers for cells that contain numbers?
I have numbers and empty values in column A. I'm trying to get a formula (Excel 365) to show consecutive numbers each time a number appears and show "EMPTY" if there is no value in column A.
I've tried 2 formulas, with the outputs as shown in image below.
In B1: =IF(ISNUMBER(A1),ROW(A1),"EMPTY"), and copy down.
In C1: =IF(ISNUMBER(A1:A10),ROW(A1),"EMPTY")
My expected output would be as in column D.
Thanks for any help with a formula to get this.
Upvotes: 0
Views: 71
Answers (2)
Sunny
Reputation: 98
Like this ? if u need something le me know, thanks :)
=IFERROR(AGGREGATE(15,6,A1:A9,ROW(A1)),"EMPTY") **and copy down
Upvotes: 0
Spectral Instance
Reputation: 2494
With MS365 you can use
=BYROW(A1:A10,LAMBDA(rng,IF(ISNUMBER(rng),COUNT($A$1:rng),"EMPTY")))
as a spill formula:
(the Excel 2019 approach would have been
=IF(ISNUMBER(A1),COUNT($A$1:A1),"EMPTY")
and copied down)
Upvotes: 2