Formula works in dummy sheet but doesnt in the main workbook

**amid** · 07-16-2009, 12:08 AM

Hi again,

Im using the following formula
=MEDIAN(IF(($A$2:$A$34249=$A2)*($B$2:$B$34249=$B2),$C$2:$C$34249))
to enter the median of entries in column C (based on conditions in columns A and B) into column D. I have 32429 rows in my workbook so I had created a dummy workbook with 17 data rows and applied the same formula with the 32429 replaced by 17 everywhere. The formula worked fine in the dummy workbook but its not working in the main sheet; it is giving me the same value in all the rows of column D. Maybe its something really trivial but for the life of me I cant figure it out. Please help me out with this. Thanks a lot.

The dummy workbook -
Book2.xlsm

**Paul** · 07-16-2009, 12:12 AM

I have to ask... is calculation set to Automatic in the real workbook?

**amid** · 07-16-2009, 04:07 AM

... It has been changed to automatic. I still havent figured out why it doesnt work.

**DonkeyOte** · 07-16-2009, 05:47 AM

Did you use the Macro provided previously to insert the formulae in the real book ? If not and you applied manually did you enter all formulae simultaneously rather than entering the first and then copying across remainder of range ? To check go to say row 10 and check the references in the MEDIAN array - are they referring to row 2 or row 10 (=$A2 as opposed to =$A10)

On a final note - if you're running 32429 arrays your file will perform horrendously.

**amid** · 07-16-2009, 06:45 AM

Hi

I checked all these things before posting the query. And the page does take a painfully long time to open.
My only problem is if the formula works on the dummy sheet, why does it not on the main sheet?
Also Im trying to get better at VBA coding so that I can simplify things for myself. Can you suggest some decent tutorial (preferably video) which I can refer to?

Thanks a lot.