probs with MODE function

Posted by Bob Findlay on June 01, 2001 7:10 AM

Hi
Having trouble with =MODE(blah)

if blah is a range on the current worksheet. it works fine.

If blah is across worksheets e.g. Sheet1:Sheet3!A1 then I get #REF! If I change it to be Sheet1!A1,Sheet2!A1,Sheet3!A1 it works, UNLESS one of the cells is blank, in which case I get #VALUE!

If I change to the median function, it works in both instances! Any idea whassup? I think it could be a bug - it's the same in excel 97 and 2000.

I was considering just writing a macro to replace the mode function with one of my own, but don't know how to pass multiple cell addresses to a function. Can anyone help me with that?

TIA
Bob

Posted by david on June 01, 2001 7:37 AM

can't you just limit the amount of ranges to something like three

Public Function mode2(x As Range, Optional y As Range, Optional z As Range) As Single

For Each cell In x
temp = 1
Next

For Each cell In y
temp = 1
Next

For Each cell In z
temp = 1
Next

End Function