Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
Normally I would google this but I cant seem to find a decent answer.
basically I have the current formula (not as big also I have range names in there for each column but this is to get the idea).
{= Max (if(C2:C5="x",E2:E5:G2:G5:I2:I5)}
Basically I have data as follows
| c | d | e | f | g | h | I
1 | x |10 | 20 | 35 | 30 | 10| 20
2 | Y | 5 | 10 | 5 | 25 | 30 | 6
Basically I want the formula to return 30 not 35 which it is currently giving me (I assume its because I am using : in the formula so it is looking between column E + G so its picking up the 35??)
This make anysense? (ideally id prefer not to use code)
edit: trying to get example to format
[Edited on 06-01-2014 by Matt L]
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
Upload your workbook somewhere and post a link. Remove any sensitive or proprietary data obviously.
I'm not sure what you're trying to do.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Surely you stick Max within the IF statement?
Eg, if you're attempting to get the max value of each row based on the Yes/No boolean then you'd do (returns zero if boolean is No/False) -
code:
=IF(C1="x",MAX(D1:I1),0)
Doing this gets you something like -
[Edited on 06-01-2014 by Dom]
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
quote:
Originally posted by Dom
Surely you stick Max within the IF statement?
That would include rows marked as 'y' in the first column - as what you would be doing by putting the MAX inside the IF is saying, if 'x' exists in the first column, then return the MAX of range (E2:E5,G2:G5,I2:I5).
I think what he's trying to do is return the MAX of range (E2:E5,G2:G5,I2:I5) only taking into consideration rows marked 'x'.
i.e. he's trying to find the MAX of an array with non-contiguous rows and columns. This might do the job:
{=MAX(IF(C2:C5="x",E2:E5),IF(C2:C5="x",G2:G5),IF(C2:C5="x",I2:I5))}
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
|
Matt L
Member
Registered: 17th Apr 06
User status: Offline
|
Cheers Rob, I shall try that, didnt think to do it by column although I have a feeling this may slow the sheet down (already slow with it being an array formula).
|
evilrob
Premium Member
Registered: 16th Mar 12
Location: Your mum's house
User status: Offline
|
It *should* be faster because technically it's examining fewer cells than your original formula.
|
