Using IF, ISNUMBER and MAX function

Hi there,

I am using the following formula so that the excel sheet I am using for data entry will automatically select the highest of 3 values for each participant in a study I am conducting, so that the second sheet will contain the highest value for dynamic graphing.

=IF(ISNUMBER('Muscle data'!I2:I4), MAX('Muscle data'!I2:I4)," ")

Is there a way I can manipulate this formula so that I do not have to manually change the range from where I want the values to come from?

E.g. I2-I4, the next selection would be I5-I7 but I want the formula to change automatically. Is this possible?

what does the rest of your data look like? do you have names in an adjacent column?

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Perhaps something like this?

	F	G	H	I
2	aa	12	aa	10
3	bb	15	aa	11
4			aa	12
5			bb	13
6			bb	14
7			bb	15

G2=MAX(IF($H$2:$H$7=F2,$I$2:$I$7))
This is an ARRAY formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Based on your data, try this ARRAY formula, copied down and across...
=IF(MAX(IF(Data!$A$2:$A$7='Data for graph'!$A2,Data!C$2:C$7))=0,"",MAX(IF(Data!$A$2:$A$7='Data for graph'!$A2,Data!C$2:C$7)))

Did you use CSE to enter it?

Withdrawn by FR

If you changed the sheet names and get the "file open" window, that means that it cannot find that sheet name in that file. Check to make sure that the sheet name matches EXACTLY the name you are putting into the formula. Better yet, just reference it.

Edit: Does the sheet name contain a space?
If so, then you need to add ' around the name. See the differences below...
=IF(MAX(IF(Data!$A$2:$A$7='Data for graph'!$A2,Data!C$2:C$7))=0,"",MAX(IF(Data!$A$2:$A$7='Data for graph'!$A2,Data!C$2:C$7)))
=IF(MAX(IF('Data 1'!$A$2:$A$7='Data for graph'!$A2,'Data 1'!C$2:C$7))=0,"",MAX(IF('Data 1'!$A$2:$A$7='Data for graph'!$A2,'Data 1'!C$2:C$7)))

This should not cause the problem you describe though