Max and Min (1 Viewer)

qafself

Registered User.
Local time
Today, 08:07
Joined
Nov 9, 2005
Messages
119
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
 

Brianwarnock

Retired
Local time
Today, 08:07
Joined
Jun 2, 2003
Messages
12,701
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
 

qafself

Registered User.
Local time
Today, 08:07
Joined
Nov 9, 2005
Messages
119
Min and Max

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
 

Attachments

  • Contract details.zip
    15.4 KB · Views: 301

Mile-O

Back once again...
Local time
Today, 08:07
Joined
Dec 10, 2002
Messages
11,316
You don't type the { } braces. Put the rest of the formula in and then press Ctrl+Shift+Enter to create an array formula.
 

Brianwarnock

Retired
Local time
Today, 08:07
Joined
Jun 2, 2003
Messages
12,701
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
 

shades

Registered User.
Local time
Today, 03:07
Joined
Mar 25, 2002
Messages
516
The MAX should be okay. For the MIN, you have to adjust slightly

=MIN(IF(A2:A100="AB",B2:B100,""))
________
F-550
 
Last edited:

qafself

Registered User.
Local time
Today, 08:07
Joined
Nov 9, 2005
Messages
119
MIN and MAX

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
 

qafself

Registered User.
Local time
Today, 08:07
Joined
Nov 9, 2005
Messages
119
MIN and MAX

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
 

Users who are viewing this thread

Top Bottom