Minmun

[ziad alsayed]

Dear all

please advise why the below two formulas are not Working.

Excel Workbook
	A	B	C	D	E
1	S/N	Name	Department	Joined On	Salary
2	1	Ziad Alsayed	Management	20/08/08	20 000
3	2	Imad Elhaje	Management	22/09/09	30 000
4	3	Mohammed Moussa	Account	27/11/09	15 000
5	4	Amin Marie	IT	30/01/11	12 000
6	5	Firas Mamlouk	Account	04/12/10	17 000
7	6	Ekpo Ochuko	IT	27/08/08	13 000
SUm Product

Excel 2010

Cell Formulas
Range	Formula
A2	=ROWS($A$2:A2)
A3	=ROWS($A$2:A3)
A4	=ROWS($A$2:A4)
A5	=ROWS($A$2:A5)
A6	=ROWS($A$2:A6)
A7	=ROWS($A$2:A7)

Excel Workbook
	G	H	I
6	Array formula	Lowest Salary in Management Department	0
7	Sum Product	Lowest Salary in Management Department	0
SUm Product

Excel 2010

Cell Formulas
Range	Formula
I7	=SUMPRODUCT(MIN((C2:C7="Management")*(E2:E7)))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Appreciate any help.

 

[Aladin Akyurek]

Some comments...

1.

20 000 in E1 does not look like a true number, that is,

=ISNUMBER(E1)

will yield FALSE.

2. After you change all salaries into true numbers, try:

Control+shift+enter (CSE), not just enter:

=IF(COUNTIF(C2:C7, Management),MIN(IF(C2:C7="Management",E2:E7)),"")

Note. SumProduct is not appropriate here. Invoking it just in order to avoid CSE is not a "good practice".

 

[ziad alsayed]

thanks aladin
in fact i am tryong to practice Both array formula and sumProduct, i agree with that sumproduct is not appropriate here.

your formula work perfeclty. but i wanted to understand why my formula didn't work specialy that it works with MAX formula , please check the below

Excel Workbook
	G	H	I
3	Array formula	Highest Salary in IT Department	13 000
4	Sum Product	Highest Salary in IT Department	13 000
SUm Product

Excel 2010

Cell Formulas
Range	Formula
I4	=SUMPRODUCT(MAX((C2:C7="IT")*(E2:E7)))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.


please advise.

 

[Aladin Akyurek]

=SUMPRODUCT(MAX((C2:C7="IT")*(E2:E7)))

is the same as

=MAX((C2:C7="IT")*(E2:E7))

confirmed with CSE.

A non-zero result means there is at least one IT-record with a corresponding value > 0.

For the sample at hand, we have:

{0;0;0;12000;0;13000}

as result of (C2:C7="IT")*(E2:E7), whose MAX is 13000.

MIN of the foregoing will always be 0, not 12000, when:

MIN((C2:C7="IT")*(E2:E7))

is used.

On the other hand, with

=MIN(IF(C2:C7="IT",E2:E7))

we get:

MIN({FALSE;FALSE;FALSE;12000;FALSE;13000})

wit as result: 12000.

Hope this helps.