Question

[chipgreenfield]

I am trying to organize data. Right now it is sequenced like this.

Customer Number Payment
1 50
1 60
1 50
2 100
2 150
2 50
2 75
3 150
3 60

I am trying to seperate each customer number so the ones that hav
atleast one payment above 100 will be seperated from the ones that d
not. So in the example above, Customer 2 and 3 will be distinguishe
from Customer 1. I have fruitlessly tried to us Vlookups and othe
functions. If someone can offer help that would be greatl
appreciated.

Chi

 

[nona]

Customer Number Payment Separation (In formula)
1 50 =IF(B2>=100;A2;"")
1 60 =IF(B3>=100;A3;"")
1 50 =IF(B4>=100;A4;"")
2 100 =IF(B5>=100;A5;"")
2 150 =IF(B6>=100;A6;"")
2 50 =IF(B7>=100;A7;"")
2 75 =IF(B8>=100;A8;"")
3 150 =IF(B9>=100;A9;"")
3 60 =IF(B10>=100;A10;"")
=IF(B11>=100;A11;"")

Etc, Etc, Etc

 

[chipgreenfield]

Thanks Nona. But I dont think this exactly works. I want the Custome
1,2, or 3 to be distinguished. So even though 2 and 3 have payment
below 100, they will still be classified as being above 100. This i
what I wanted to do:

1. Check each to see if it is above 100.
2. If it is below put a 0 in column C.
3. If it is above put a 1 in column C.
*4. If it is below but any payment within its customer is over 100 tha
put a 1 in column C. ( I thought about adding 100 to these situation
to make them over 100).
5. Then I would assign a name to each payment. (For example 0=paymen
from under 100 customer, 1=payment from over 100 customer.)

* I am having a problem with 4.

So it should look like this:

CustomerNumber Payment Type
1 50 0
1 60 0
1 50 0
2 150 1
2 100 1
2 50 1
3 150 1
3 75 1
3 60

 

[Anders S]

One way:

=--(SUMPRODUCT(--($H$2:$H$10>99),--(G2=$G$2:$G$10))>0)

assuming the customer numbers begin in G2 and the amounts begin in H2, adjust to suit.

HTH
Anders Silvén

 

[chipgreenfield]

Thanks Anders..But I am not exactly sure how that works. Specificall
the reference to 3D. Where would I insert the formula? Thanks agai
in advance.

Chi

 

[Anders S]

Hi Chip,

Same formula, but different references:

=--(SUMPRODUCT(--($B$2:$B$10>99),--(A2=$A$2:$A$10))>0)

With row headers in row 1, customer numbers begin in A2 and amounts begin in B2. Adjust $B$10 and $A$10 to fit your actual data (the formula only covers rows 2 to 10).

Enter the formula in C2, then fill down in column C as necessary.

Specifically the reference to 3D.

? What do you mean by 3D?

Best regards
Anders