Compressing multiple functions across an array into one cell
 

Hiya, I just had a question I hoped you all might be able to answer. I currently have a formula I would like to have repeat across several cells in an array, and I've done that by making a whole other set of cells with the results in them. Then I take the second set of cells and use the MAX() function to find the maximum value of them.

What I would LIKE to do is compress all of that down into one function - so it runs the formula on every cell across the row in the array, then finds the max value in that row.

Any idea how or whether that can be done?

Hi,

You're going to need to provide an example workbook for the answer to this. It's not clear exactly what you mean.

Yeah, thought that may be the case, I've attached it in the zip file here.

(N4:U4) we can use as an example here - I have to remove the < symbols and then check the absolute value of them so that I can check the max value of that row in Y4. What I want to do is to put all of that work into Y4 - instead of creating the columns N to U, to combine all those formulas into one. Something like MAX(If(N4.....):If(U4......)), where it just runs that formula over every cell in that row. Any clearer? Probably not, but I'll try to explain it better if that makes a difference. : /

Compressing multiple functions across an array into one cell
 

Hi Nick

I have a length of string.

Can you guess how long it is from where you sit... O.o

You are going to have to give us something more in order to get a result
for you.

Paste your formula(s)
Some sheets names or at least some ranges and or sample data and an
example of your expected result would be nice...

Cheers
Mick.

I pasted the whole workbook I'm using one post before this post, along with an explanation of what I was looking for.

Compressing multiple functions across an array into one cell
 

Nick

You can't attach files in NG's, you have to use a File Sharing website
eg www.ExcelBanter.com, it is an excel forum that allow attachments..

HTH
Mick.

Again, I didn't post this in a newsgroup, it was posted on ExcelBanter, three posts above this one.

Compressing multiple functions across an array into one cell
 

Il 17/09/2012 12:31, Nick Sabo ha scritto:
Living the Dream;1605561 Wrote:
Nick

You can't attach files in NG's, you have to use a File Sharing website
eg www.ExcelBanter.com, it is an excel forum that allow attachments..

HTH
Mick.

Again, I didn't post this in a newsgroup, it was posted on ExcelBanter,
three posts above this one.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+




In Y4 put:
=MAX(--SUBSTITUTE(SUBSTITUTE(C4:L4,"<",""),"-",0))
(matricial formula, to confirm by CTRL+SHIFT+RETURN)

Wow, didn't know about SUBSTITUTE(), that's really useful. However, it still seems not be working, somehow... does it work for you?

EDIT: Wow I didn't know about CSE Formulas. That is staggeringly useful and completely unintuitive, but thank you, you're amazing!