Max function in Excel 2010

[sparkles]

I'm working on improving my stock keeping system and have been trying to use the Max function with varing results.


The formulae below is designed below to find the maximum purchase paid for a raw material.


=MAX((Purchase!$B$2:$B$70=A2)*(Purchase!$F$2:$F$70))


Purchase! Column B is the product code in the purchase order

Purchase! column f is the price paid


However I'm having problems with the formulae - can anybody offer advise please?


The problem:-

In the cell in which the formulae is typed the the first value of product A2 price paid is depicted rather than the correct answer. If however you pressed the "fx" insert function tab the function arguments window appears showing the correct answer.


If you F9 the formulae the correct answer appears, but does not then update if you change the data. Has anybody got any ideas as to where I am going wrong?


Thanks

Sparkles

 

[sparkles]

Also tried:-


=MAX(--(Purchase!$B$2:$B$70=A2),--Purchase!$F$2:$F$70)


with the same problem - incorrect in the cell, yet correct in the function argument window!


Help

Rgs

 

[Montrey]

Make sure Autocalculate is on in the options or just press F9

 

[sparkles]

Thanks


However Autocalculate is on and F9 'appears' to remove the formulae!

 

[Montrey]

Are you using Ctrl+Shift+Enter?

 

[sparkles]

Well I wasn't - but you've cracked it thank you.


Sparkles

 

[Montrey]

I guess that means solved! Yay. Come back anytime.