Hi Guys,

Grateful for the benefit of your accumulated wisdom. I am not yet into VBA so I need a simple answer to this apparently simple problem.

In my table I have a number of columns. In one is a value, AB or FS, in another a numerical value. I want to find the Min and Max AB and FS value - it looks like it should be easy but so far...............

Ed

You will need 4 array formulae of the form

{=MIN(B2:B100)*(A2:A100="ab")}
{=MAX(B2:B100)*(A2:A100="ab")}

The above assumes the numerics in col B and the ab etc in col A

Remember as its an ARRAY formula activate by pressing Ctrl shift and Enter not just Enter

Brian

Hi Brian,

Thanks for that - unfortunately it does not seem to work as (I think) it is picking up the value associated with the identifier in the first line and using that.

I have attached the spreadsheet. The idea is to obtain summary information from tenders received or contracts renegotiated, the key information being the Cost per support hour (CPSH). There are two different types of service and the information is required for both. In an ideal world it would be nice to have a third identifier so that information could be summarised in respect of Successful tenders.

Hope this makes sense.

Ed

You don't type the { } braces. Put the rest of the formula in and then press Ctrl+Shift+Enter to create an array formula.

Hi Ed
I think that I have misled you, my tests appeared to have worked purely by coincidence, it appears to return the MAX or Min value if not when the logical test is true, if you get my drift.
So sorry at the moment I cannot help, I think it may require some VBA code.

Again sorry to have misled you.

Brian

The MAX should be okay. For the MIN, you have to adjust slightly

=MIN(IF(A2:A100="AB",B2:B100,""))

Hi Shades,

You seem to have cracked it - your formula works for both MIN and MAX - thanks very much!

Did you have any thoughts about how to introduce a third identifier i.e. to do the same for successful tenders.

Ed

OK - I've worked it out for myself, building on your formula

The array needed is as below


=MIN(IF(Tenders!D5:D79="S",IF(Tenders!E5:E79="AB",Tenders!J5:J79,"")))

Thanks everybody

Ed