Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated formula.......
I have:-
=SUMIF('Issue Analysis'!A:A,AND('Titles List'!A3,'Issue Analysis'!J:J0,MAX('Issue Analysis'!H:H)),'Issue Analysis'!J:J) Explanation of what Im trying to do:- I want to return the value in column J that is highest value of column H, but if the highest value of column H has a respective column J value of 0, then look at the preceeding MAX in column H, all based upon the lookup of A3 in the column A. Hope this makes sense. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated formula.......
I know this isn't correct, because I don't understand what you mean by the
lookup of A3, but here's a starter =INDEX(J1:J1000,MATCH(MAX(IF(J1:J1000<0,(H1:H1000 ))),H1:H1000,0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Matthew Balch" wrote in message ... I have:- =SUMIF('Issue Analysis'!A:A,AND('Titles List'!A3,'Issue Analysis'!J:J0,MAX('Issue Analysis'!H:H)),'Issue Analysis'!J:J) Explanation of what Im trying to do:- I want to return the value in column J that is highest value of column H, but if the highest value of column H has a respective column J value of 0, then look at the preceeding MAX in column H, all based upon the lookup of A3 in the column A. Hope this makes sense. Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated formula.......
I was hoping to stay away from array formulas as these seem to 'freeze' excel.
The lookup is the first part of the formula. Thanks for the other bit, will try and work it myself, if not repost "Bob Phillips" wrote: I know this isn't correct, because I don't understand what you mean by the lookup of A3, but here's a starter =INDEX(J1:J1000,MATCH(MAX(IF(J1:J1000<0,(H1:H1000 ))),H1:H1000,0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Matthew Balch" wrote in message ... I have:- =SUMIF('Issue Analysis'!A:A,AND('Titles List'!A3,'Issue Analysis'!J:J0,MAX('Issue Analysis'!H:H)),'Issue Analysis'!J:J) Explanation of what Im trying to do:- I want to return the value in column J that is highest value of column H, but if the highest value of column H has a respective column J value of 0, then look at the preceeding MAX in column H, all based upon the lookup of A3 in the column A. Hope this makes sense. Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complicated formula.......
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 "Matthew Balch" wrote: I have:- =SUMIF('Issue Analysis'!A:A,AND('Titles List'!A3,'Issue Analysis'!J:J0,MAX('Issue Analysis'!H:H)),'Issue Analysis'!J:J) Explanation of what Im trying to do:- I want to return the value in column J that is highest value of column H, but if the highest value of column H has a respective column J value of 0, then look at the preceeding MAX in column H, all based upon the lookup of A3 in the column A. Hope this makes sense. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complicated formula help | Excel Worksheet Functions | |||
Complicated Formula - I think | Excel Worksheet Functions | |||
complicated if then formula...need help | Excel Discussion (Misc queries) | |||
Complicated formula | Excel Worksheet Functions | |||
Complicated formula | Excel Programming |