Huge “IF” formula help.

[sungen99]

Here is what I need to be able to do.

I have 9 columns of clients account numbers as the header.
Each row is a trade that belongs to one of the 9 accounts.

I need to have the 10th column have the account the trade is for
written.

So for example I think I want to do something like this:
If G7 <>”” then make P7=”1P”
Or
If H7 <>”” then make P7=”2P”
Or
If I7 <> “” then make P7=”3P”

And so on.

Just don’t know how to do it.

Thanks in advance,
Ken

 

[Bob Phillips]

Does this do it

=COUNTA(G7:O7)&"P"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[sungen99]

Thanks alot it SORT of works.

The real numbers are actually
3B123
3B454
3B550
3B590
3B625

and so on. so i cant really do it that way. too bad!!!!

any further ideas??

Ke

 

[Bob Phillips]

I get 5P for that, is that not correct?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[sungen99]

Ah. no no- im not explaining it correctly. the actual account numbers
are those. That’s what I want to be displayed. not 1p 2p 3p and so on.

 

[Biff]

Hi!

I think you need to completely rewrite your description and include an
example using the actual values. Be VERY specific!

I'm willing to bet that what you want to do is very simple but the
explanation isn't helping.

Biff

 

[pinmaster]

Hi,
If I understand you correctly a single row will only contain one entry
and you want to match that entry with the account number from the
header row. if so then try this:

=INDEX($G$6:$O$6,MATCH(IF(SUM(G7:O7)=0,"*",SUM(G7:O7)),G7:O7,0))

as you can see, it is a bit crude maybe one of the MVP's out there as
something better.

HTH
JG

 

[sungen99]

Ok does this help?


G1 is 3P100 H1 is 3P200 I1 is 3P250 J1 is 3P322 K1 is 3P532 L1 is
3P555 M1 is 3P560 N1 is 3P667 O1 is 3P800

As I said the trades run for A1 to A100 or whatever.

So A1 has Sold 100 in it. and because it was done for the 3P532
account the number of contracts sold (lets say 10) is found in K1

What I want to do is have P1 show the NAME of the account that did the
trade. In this case P1 would show “3P532”

There will never be a case where there are more than one trade per
line.

Thanks again for the help,

Ken

 

[Sandy Mann]

Ken,

I think that Biff suggestion of being VERY specific in what it is you are
trying to do is very valid. In the mean time, if you are still using G7:K7
then to return the numbers you gave try:

="3B"&CHOOSE(MIN(IF(G7:K7<>"",--(G7:K7<>"")*({1,2,3,4,5}))),123,454,550,590,625)

entered as an array formula with Ctrl + Shift + Enter not just Enter

I had worked it out so it seemed a waste not to post it <g>
--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Biff]

Hi!

Try this:

=INDEX(G1:O1,MATCH(9.99999999999999E+307,G2:O2))

Biff

 

[jayd77]

I have the accounts listed in A1 through I1.
In J2 I have =IF(COUNT(A2:I2)>1,"Error",SUM(A2:I2))
In K2 I have =INDEX(A1:I1,1,MATCH(J2,A2:I2))

I have the test value of 100 entered in C2 with 3p as an account in C1
J2 then equates to 100
J3 then equates to 3p

 

[jayd77]

I didn't mean J3 I meant K2=3p.
You can always hide the J Column

 

[Bob Phillips]

=INDEX(7:7,1,MAX(IF(7:7<>"",COLUMN(7:7))))

as an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[sungen99]

WOW it works!!!!!

This programing is WAY above me. But it works and for that I am very
thankfull.

ken