Welcome Guest
You last visited December 9, 2016, 4:45 am
All times shown are
Eastern Time (GMT-5:00)

# Can anyone help me with this Excel question?

Topic closed. 3 replies. Last post 6 years ago by time*treat.

 Page 1 of 1
Gent
Belgium
Member #73987
April 30, 2009
33 Posts
Offline
 Posted: January 26, 2011, 10:33 am - IP Logged

Hi Lottery Post'ers

I wonder if anyone can help me with this excel question.

In cells A1 to J1, I have 10 numbers:

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1

1  7 11 14 18 19 22 27 39 40

In cells K1 - T1, I would like to appear the numbers from 1-10 (so in this example: 1 in cell K1, and 7 in cell L1, cells M1 to T1 remain blank).

In cells U1 - AD1, I would like to appear the numbers form 11-20 (so in this example: 11 in cell U1, 14 in cell V1, 18 in cell W1 and 19 in cell X1, cells Y1 to AD1 remain blank).

Can I do this with a formula? Thank you very much in advance.

United States
Member #79057
August 26, 2009
70 Posts
Offline
 Posted: January 26, 2011, 11:04 am - IP Logged

There's probably an easier way,but I don't know it.  One way I do know of doing it is to add columns at the end - for example to do 1 in cell K1:

In cell AE1 -            =Countif(A1:J1,1)

This looks at the A1 - J1 array and counts the number of times 1 shows up.

then

In cells K1 -             =If(AE1>0,1,0)

If one does appear in the array then it puts the value of 1 in K1 - otherwise it puts in zero.

For a second example I'll do 7

In cell AK1 -                    =countif(a1:j1,7)

In cell Q1 -                     =If(AK1>0,7,0)

I'm sure there's an easier way, but I hope this helps.

Gent
Belgium
Member #73987
April 30, 2009
33 Posts
Offline
 Posted: January 27, 2011, 2:26 am - IP Logged

Thank you for the effort ACPutz, I appreciate it, but it's not what I'm looking for.

The lowest number from the group 1 to 10, whatever it is, should appear in cell K1. Not easy :-S

Anyone else?

United States
Member #13130
March 30, 2005
2171 Posts
Offline
 Posted: January 27, 2011, 4:31 am - IP Logged

(formula in cell K1) =IF(A1>=0,IF(A1<=10,A1,""),"")
(formula in cell L1) =IF(B1>=0,IF(B1<=10,B1,""),"")
(formula in cell M1) =IF(C1>=0,IF(C1<=10,C1,""),"")

(formula in cell U1) =IF(A1>10,IF(A1<=20,A1,""),"")
(formula in cell V1) =IF(B1>10,IF(B1<=20,B1,""),"")
(formula in cell W1) =IF(C1>10,IF(C1<=20,C1,""),"")

Put in the K1 and U1 cell formulas, you may be able to drag-and-drop.

In neo-conned Amerika, bank robs you.
Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

 Page 1 of 1