Xcel formula question

Reply #6

Griz — Mon, 02 Jul 2007 00:51:16 GMT

Bud,

Yup, your formulas did the trick... many thanks for your help.. will let you know when (and I most likey will - I suck at these formulas) need assistance again. tks again

Griz

Reply #5

budward — Mon, 02 Jul 2007 00:39:34 GMT

I guess I should mention that once the formulas are set up, you can highlight and drag the cells where ever you want them on your spreadsheet

Bud

Reply #4

budward — Sun, 01 Jul 2007 21:34:22 GMT

Also looking for a nother formula which would then total up the groups, and return the following:

2 in group 1, 2 in group 2 and 1 in group 3.

In Cell L1 enter 1

In Cell M1 enter 2

In Cell N1 enter 3

In Cell G1 enter Group1, In Cell G2 enter =COUNTIF(A2:E2,$L$1)

In Cell H1 enter Group 2, In Cell H2 enter =COUNTIF(A2:E2,$M$1)

In Cell I1 enter Group 3, In Cell I2 enter =COUNTIF(A2:E2,$N$1)

You should have this now

15222644Group1Group 2Group 3123

11223221

Reply #3

budward — Sun, 01 Jul 2007 21:16:04 GMT

Enter =MATCH(A1,{0;19;38},1) in Cell A2 and copy over to G2.

That will give you this

15222644

11223

Enter =IF(ISODD(A1), O , E ) in Cell A3 and copy over to G3

and you will get this

15222644

11223

OOEEE

If you want to combine them in A4

enter =CONCATENATE(A2,A3) in A4 and copy over to G4

and you will get this

15222644

11223

OOEEE

1O1O2E2E3E

Does this look like what you want?

Bud

Reply #2

Griz — Sun, 01 Jul 2007 13:26:09 GMT

CARBOB,

Thanks for your reply... while it's not exactly what I was looking for, I do like what your formula does and will put it to use...

Here's what I was looking for originally,

the lotto results enterd into cells A1 to E1 as below:

1 5 22 26 44

Then formula's to return either G1, G2 or G3 in cells A2 to E2 based on what

group the number in corresponding number above is in

G1 is 1-18, G2 is 19-37 and G3 is 38-56

Also interested in a formula which wou... [ More ]

Reply #1

CARBOB — Sun, 01 Jul 2007 11:58:25 GMT

Is this what you're looking for?

15222644221

Enter the formula as an Array, Ctrl-Shift-Enter. Change the bold range to your sheet!

=(COUNTIF($W18:$AA18, <19 ) COUNTIF($W18:$AA18, <38 )-COUNTIF($W18:$AA18, <19 ) COUNTIF($W18:$AA18, >37 ))+0

Hope this helps

Xcel formula question

Griz — Sun, 01 Jul 2007 03:07:46 GMT

Hey everyone,

Need some help from Excel experts out there...

here's my problem:

Say I have the results from a lottery, 1, 5, 22, 26, 44, mega ball 7

Now, say for example, I have number group 1-18 as group 1, 19-37 as group 2 and

38 - 56 as group 3. I need forumlas which would filter out the lotto results, 1 is group1, 5 is group 1, 22 group 2, 26 group2, 44 group3.

Also looking for a nother formula which would then total up the groups, and return the following:

2 in grou... [ More ]