Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help
Posed this question yesterday. Bob replied but need more help. Driving me
insane (can normally figure out formulas!) Bobs reply:- =INDEX('Issue Analysis'!J1:J1000,MATCH(MAX(IF('Issue Analysis'!J1:J1000<0,('Issue Analysis'!H1:H1000))),'Issue Analysis'!H1:H1000,0)) What I've got so far:- =INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue Analysis'!$A$3:$A$15000,0),10) I would now like to add the MAX part of the formula (column J) as highlighted in Bobs formula. I would like to, if possible, stay away from array formulas as these seem to creat lag issues within excel (Why?) and also other users will not know how to set them with ctrl+shift+enter etc Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help
Hi
could you put this post on the end of the previous thread. Taken on its own, the answer to this post is to be to copy and paste bits of Bob's formula into yours! regards Paul Matthew Balch wrote: Posed this question yesterday. Bob replied but need more help. Driving me insane (can normally figure out formulas!) Bobs reply:- =INDEX('Issue Analysis'!J1:J1000,MATCH(MAX(IF('Issue Analysis'!J1:J1000<0,('Issue Analysis'!H1:H1000))),'Issue Analysis'!H1:H1000,0)) What I've got so far:- =INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue Analysis'!$A$3:$A$15000,0),10) I would now like to add the MAX part of the formula (column J) as highlighted in Bobs formula. I would like to, if possible, stay away from array formulas as these seem to creat lag issues within excel (Why?) and also other users will not know how to set them with ctrl+shift+enter etc Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help
I still don't get what you want. Can you post some data in a message and
some results so that we can see better what is required. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Matthew Balch" wrote in message ... Posed this question yesterday. Bob replied but need more help. Driving me insane (can normally figure out formulas!) Bobs reply:- =INDEX('Issue Analysis'!J1:J1000,MATCH(MAX(IF('Issue Analysis'!J1:J1000<0,('Issue Analysis'!H1:H1000))),'Issue Analysis'!H1:H1000,0)) What I've got so far:- =INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue Analysis'!$A$3:$A$15000,0),10) I would now like to add the MAX part of the formula (column J) as highlighted in Bobs formula. I would like to, if possible, stay away from array formulas as these seem to creat lag issues within excel (Why?) and also other users will not know how to set them with ctrl+shift+enter etc Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help
Sheet 1:
A H J JAESTHJ 39052 356.57 JAESTHJ 38718 536.83 JAESTHJ 38808 473.43 JAFRAFJ 39052 342.99 JAFRAFJ 38718 1516.58 JAFRAFJ 38808 1499.16 Sheet 2 A D JAESTHJ want to equal 473.43 JAFRAFJ want to equal 1499.16 ignore any 39052 values in the MAX function. ........ Coincidentally on Sheet 1 Column H I have had to use a number format of a date for the MAX function to work. If at all possible I would like the MAX function to work on the following:- Sheet 1 A Replace H values above with:- JAESTHJ JAESTHJ0600 356.57 JAESTHJ JAESTHJ0601 536.83 JAESTHJ JAESTHJ0602 473.43 JAFRAFJ JAFRAFJ0600 342.99 JAFRAFJ JAFRAFJ0601 1516.58 JAFRAFJ JAFRAFJ0602 1499.16 On JAESTHJ the MAX value = JAESTHJ0602 :. 473.43 Thank for your patience and time. Matthew Balch "Bob Phillips" wrote: I still don't get what you want. Can you post some data in a message and some results so that we can see better what is required. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Matthew Balch" wrote in message ... Posed this question yesterday. Bob replied but need more help. Driving me insane (can normally figure out formulas!) Bobs reply:- =INDEX('Issue Analysis'!J1:J1000,MATCH(MAX(IF('Issue Analysis'!J1:J1000<0,('Issue Analysis'!H1:H1000))),'Issue Analysis'!H1:H1000,0)) What I've got so far:- =INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue Analysis'!$A$3:$A$15000,0),10) I would now like to add the MAX part of the formula (column J) as highlighted in Bobs formula. I would like to, if possible, stay away from array formulas as these seem to creat lag issues within excel (Why?) and also other users will not know how to set them with ctrl+shift+enter etc Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula help
=INDEX(Sheet1!$J$1:$J$100,MATCH(MAX(IF((Sheet1!$A$ 1:$A$100=A1)*(Sheet1!$H$1:$H$100<39052),Sheet1!$H $1:$H$100)),IF((Sheet1!$A$1:$A$100=A1)*(Sheet1!$H$ 1:$H$100<39052),Sheet1!$H$1:$H$100),0))
entered with Ctrl+Shift+enter rather than just enter since this is an array formula. -- Regards, Tom Ogilvy "Matthew Balch" wrote in message ... Sheet 1: A H J JAESTHJ 39052 356.57 JAESTHJ 38718 536.83 JAESTHJ 38808 473.43 JAFRAFJ 39052 342.99 JAFRAFJ 38718 1516.58 JAFRAFJ 38808 1499.16 Sheet 2 A D JAESTHJ want to equal 473.43 JAFRAFJ want to equal 1499.16 ignore any 39052 values in the MAX function. ....... Coincidentally on Sheet 1 Column H I have had to use a number format of a date for the MAX function to work. If at all possible I would like the MAX function to work on the following:- Sheet 1 A Replace H values above with:- JAESTHJ JAESTHJ0600 356.57 JAESTHJ JAESTHJ0601 536.83 JAESTHJ JAESTHJ0602 473.43 JAFRAFJ JAFRAFJ0600 342.99 JAFRAFJ JAFRAFJ0601 1516.58 JAFRAFJ JAFRAFJ0602 1499.16 On JAESTHJ the MAX value = JAESTHJ0602 :. 473.43 Thank for your patience and time. Matthew Balch "Bob Phillips" wrote: I still don't get what you want. Can you post some data in a message and some results so that we can see better what is required. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Matthew Balch" wrote in message ... Posed this question yesterday. Bob replied but need more help. Driving me insane (can normally figure out formulas!) Bobs reply:- =INDEX('Issue Analysis'!J1:J1000,MATCH(MAX(IF('Issue Analysis'!J1:J1000<0,('Issue Analysis'!H1:H1000))),'Issue Analysis'!H1:H1000,0)) What I've got so far:- =INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue Analysis'!$A$3:$A$15000,0),10) I would now like to add the MAX part of the formula (column J) as highlighted in Bobs formula. I would like to, if possible, stay away from array formulas as these seem to creat lag issues within excel (Why?) and also other users will not know how to set them with ctrl+shift+enter etc Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |