Whopper If Statement and Formula to Long

[Laura]

I have a whopper of an If statement and prior to
completion, I received the error that the formula is too
long. Is there any way around this?

Sample before I was cut off:

=MIN(IF(A5>=Sheet1!$A$24,45000,Sheet1!$A$24),IF(A5>=Sheet2!
$A$24,45000,Sheet2!$A$24),IF(A5>=Sheet3!$A$24,45000,Sheet3!
$A$24),IF(A5>=Sheet4!$A$24,45000,Sheet4!$A$24),IF
(A5>=Sheet5!$A$24,45000,Sheet5!$A$24),IF(A5>=Sheet1!
$A$25,45000,Sheet1!$A$25),IF(A5>=Sheet2!$A$25,45000,Sheet2!
$A$25),IF(A5>=Sheet3!$A$25,45000,Sheet3!$A$25),IF
(A5>=Sheet4!$A$25,45000,Sheet4!$A$25),IF(A5>=Sheet5!
$A$25,45000,Sheet5!$A$25),IF(A5>=Sheet1!$A$26,45000,Sheet1!
$A$26),IF(A5>=Sheet2!$A$26,45000,Sheet2!$A$26),IF
(A5>=Sheet3!$A$26,45000,Sheet3!$A$26),IF(A5>=Sheet4!
$A$26,45000,Sheet4!$A$26),IF(A5>=Sheet5!$A$26,45000,Sheet5!
$A$26),IF(A5>=Sheet1!$A$27,45000,Sheet1!$A$27),IF
(A5>=Sheet2!$A$27,45000,Sheet2!$A$27),IF(A5>=Sheet3!
$A$27,45000,Sheet3!$A$27),IF(A5>=Sheet4!$A$27,45000,Sheet4!
$A$27),IF(A5>=Sheet5!$A$27,45000,Sheet5!$A$27))


Any insights are most appreciated.

 

[Frank Kabel]

Hi Laura
I'd try the following formula
=IF(A5>=MIN('sheet1:sheet5'!A24:A30),45000,MIN('sheet1:sheet5'!A24:A30)
)

if I understood your logic correctly

 

[Robert McCurdy]

This is array entered Laura, use the Ctrl + Shift + Enter keys, instead of just Enter each time you enter or edit the formula. If
you see { } around it you did it correctly, but you don't type these.

=MIN(IF(Sheet1!A24:A27>A5,Sheet1!A24:A27),IF(Sheet2!A24:A27>A5,Sheet2!A24:A27),IF(Sheet3!A24:A27>A5,Sheet3!A24:A27),IF(Sheet4!A24:A2
7>A5,Sheet4!A24:A27),IF(Sheet5!A24:A27>A5,Sheet5!A24:A27),45000)

The Min function takes up to 30 arguments so you can add a lot more IF's to the above if you need to extend this formula. Even so
it may still get to big so I'd suggest you look at Named ranges.
For example I named this range > Sheet1:Sheet5!$A$24:$A$27
to My3D and then I used it to find the MIN like so

=MIN(My3D)

Arrays are not allowed with 3d ranges and there are not to many functions one can use with them.
Anyone know if this has changed for XL 2003?

Anyway the Small or Large function can be used like so (its not mentioned in the Help files)

SMALL(My3D,ROW(A1))

Just fill this down to get a list of the lowest to the highest values from your 3D range.
Use Large to reverse the list. I filled down the Small function from G3:G22 then used this formula to do what the first one does.
(also array entered)

=MIN(IF(G3:G22>A5,G3:G22),45000)


Regards Robert