3D Array Formula - Is it Possible?

[krame00]

Before I waste any more time on this - is it possible for an Array formula to be 3D? I am having no success, and I have heard it is - if you know how to do it right...

I tried searching the forum, but got too many hits to weed though - so if anyone has a quick answer that would be helpful.

Thanks!

Khana

 

[Charlie45]

Check out htis link. It talks about a 3D Array.

http://www.dicks-blog.com/archives/2006/02/19/3d-array-formulas/

 

[krame00]

Yeah - thanks - I saw that one, too. But I do not think it solves my question... unless it is way over my head or something. I don't know if it applies.

 

[Domenic]

Can you describe what you'd like to do?

 

[krame00]

Sure. I would like to do a 3D averageif array formula that averages all of the cells in A1 for all of the sheets in the workbook. I want to average only those cells that are not zero.

Does that make sense?

Khana

 

[vane0326]

Sure. I would like to do a 3D averageif array formula that averages all of the cells in A1 for all of the sheets in the workbook. I want to average only those cells that are not zero.

Does that make sense?

Khana

List your worksheets name in cell A2:A10

=AVERAGE(IF(N(INDIRECT("'"&A2:A10&"'!A1"))<>0,N(INDIRECT("'"&A2:A10&"'!A1"))))


Hope it helps!

 

[PaddyD]

and just for reference, all sorts of fun with 3d arrays here:

http://j-walk.com/ss/excel/eee/eee003.txt

 

[xenou]

Another try, with VBA: this sub would sum up the values and average them. I think it is not possible to group sheets in code, so you have to loop through the sheets. In Excel, you could just use a formula like =AVERAGE(SUM(Sheet1:Sheet10!A1)) and this would average Sheet1 to Sheet10 cells A1 (i.e., those two and all sheets in between). It would not add in text but would count zeros in the average.

I'd be interested in your 3D array if you get that working.

Sub AddA1Cells()
Dim ws As Worksheet, sumCells As Double, countCells As Double

For Each ws In Worksheets
    With ws
    If IsNumeric(.Cells(1, 1)) Then
        If .Cells(1, 1) <> 0 Then
            sumCells = sumCells + .Cells(1, 1).Value
            countCells = countCells + 1
        End If
    End If
    End With
Next ws

ActiveCell.Value = sumCells / countCells

End Sub

 

[Domenic]

Here's another way...

1) Download and install the free add-in Morefunc.xll

2) Create two new sheets and name them First and Last

3) Place all relevant sheets between First and Last

4) Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(THREED('First:Last'!A1)>0,THREED('First:Last'!A1))

Hope this helps!

 

[Exceljeanie]

Hi,
by playing a little bit around what about this little funny formula:

STUPID IDEA edited

=SUM(Sheet1:Sheet5!A1)/SUM(FREQUENCY(Sheet1:Sheet5!A1,{0}))

Where Sheet1 is first sheet and Sheet5 is last sheet.
If all values could be zero then it hast to be checked first if sum(frequency ... equals 0)