If statement error. Constant error message

I am getting an error with an If statement I am trying to extend and don't know why.

Original statement =IF(MAX(E113:E117)<ABS(MIN(E113:E117)),MIN(E113:E117),MAX(E113:E117))

New statement required =if('12811-1'!E35>0,=IF(MAX(E113:E117)<ABS(MIN(E113:E117)),MIN(E113:E117),MAX(E113:E117)),calcs!M97)

cell is e75 in the calcs tab. Note I am using the NA() command to stop data being drawn on the charts based on parameters seen, so you will see  a lot of #NA.

Regards

Paul
scaffold-designer-v5.xlsm
vipamanAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
I think the errors I had was due to my version of Excel are not able to use the IFS function.
When I changed the IFS formulas to nested IF, there was no error, so I guess the error you have in the formula is caused by the = before the second IF.
0
 
Ejgil HedegaardCommented:
You can't calculate max or min on errors, that leads to error.
The new statement in E75
=IF('12811-1'!E35>0,IF(MAX(E113:E117)<ABS(MIN(E113:E117)),MIN(E113:E117),MAX(E113:E117)),M97)
And similar in E76 I guess the formula should point to M95 when '12811-1'!E35 = 0
=IF('12811-1'!E35>0,C126,M95)
0
 
vipamanAuthor Commented:
I must have altered something else too. I removed the = statement as i decided it shouldn't be there. I had a few issues with the max function and brackets! Heh ho as they say. Thanks again. I had not come across the IFS function until the other night and it really simplified a few equations, that were getting a little long.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.