| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Huge IF formula help.
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 dont know how to do it. Thanks in advance, Ken -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge "IF" formula help.
Does this do it
=COUNTA(G7:O7)&"P" -- HTH Bob Phillips (remove nothere from email address if mailing direct) "sungen99" wrote in message ... 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 -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
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?? Ken -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge "IF" formula help.
I get 5P for that, is that not correct?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "sungen99" wrote in message ... 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?? Ken -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
Ah. no no- im not explaining it correctly. the actual account numbers are those. Thats what I want to be displayed. not 1p 2p 3p and so on. -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge "IF" formula help.
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 "sungen99" wrote in message ... 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. -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
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 -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
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 -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge "IF" formula help.
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 Replace@mailinator with @tiscali.co.uk "sungen99" wrote in message ... 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. -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge "IF" formula help.
Hi!
Try this: =INDEX(G1:O1,MATCH(9.99999999999999E+307,G2:O2)) Biff "sungen99" wrote in message ... 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 -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
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 ------------------------------------------------------------------------ jayd77's Profile: http://www.excelforum.com/member.php...o&userid=19048 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
I didn't mean J3 I meant K2=3p. You can always hide the J Column -- jayd77 ------------------------------------------------------------------------ jayd77's Profile: http://www.excelforum.com/member.php...o&userid=19048 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge "IF" formula help.
=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" wrote in message ... 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 -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Huge IF formula help.
WOW it works!!!!! This programing is WAY above me. But it works and for that I am very thankfull. ken -- sungen99 ------------------------------------------------------------------------ sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144 View this thread: http://www.excelforum.com/showthread...hreadid=493117 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hide formula | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| Help with macro formula and variable | Excel Worksheet Functions | |||
Linear Mode
