multiple countblank

[malone3906]

In Excel 97 I am able to take a determined range of 6 cells and minu
the max and
minus the min then divide remainder for my answer.
=(SUM(A8:A13)-MIN(A8:A13)-MAX(A8:A13))/4-36)
There will be instances when no value is placed in a cell and must
then go back to previous cell to find a value so always working with
numbers
=IF(COUNTBLANK(A8:A13)=0,(SUM(A8:A13)-MIN(A8:A13)-MAX(A8:A13))/4-36)
what I'd want it to do is be able to nest multiple arguments as
IF(COUNTBLANK(A8:A13)=1,(SUM(A7:A13)-MIN(A7:A13)-MAX(A7:A13))/4-36)
IF(COUNTBLANK(A8:A13)=2,(SUM(A6:A13)-MIN(A6:A13)-MAX(A6:A13))/4-36)
IF(COUNTBLANK(A8:A13)=3,(SUM(A5:A13)-MIN(A5:A13)-MAX(A5:A13))/4-36)

I'm just not sure if possible to nest these multiple conditions...any
suggestions to make it work

 

[Frank Kabel]

Hi
try the following (no need for nested statement): Enter the following
as array formula (enter with CTRL+sHIFT+ENTER)

=SUM(INDIRECT("A" & LARGE(IF($A$1:$A$13="",0,ROW($A$1:$A$13)),6) &
":A13"))/4-36

 

[Tom Ogilvy]

Wouldn't it need to be more like this:

=(SUM(INDIRECT("A" & LARGE(IF($A$1:$A$13="",0,ROW($A$1:$A$13)),6)
&":A13"))-MAX(INDIRECT("A" & LARGE(IF($A$1:$A$13="",0,ROW($A$1:$A$13)),6)
&":A13"))-MIN(INDIRECT("A" & LARGE(IF($A$1:$A$13="",0,ROW($A$1:$A$13)),6)
&":A13")))/4-36

 

[Frank Kabel]

Hi Tom
you're definetly right. I somehow missed the middle part of the OP's
original formula.
Thanks for adding this - so 'don't drink and do Excel' on my side