AGGREGATE returns wrong results

Hi friends,
the aggregate function should returns the minimum of specified range, for each cell in a column, but it returns wrong values.
I have tried to change format and conditions, but the result is still wrong.

If you can, please check attached file (in column D, used function is:

HTML Code: 

Thank you.

Welcome to the forum!

Attach the workbook, not a picture of it.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

OK - I've had a look. Please explain your desired results - how would you calculate them manually? I don't understand how you are arriving at them.

Forget that - I get it! You may need to replace commas with semi-colons for your locale in the formulae below.

Newer versions of Excel:

=MINIFS($D$2:$D$57,$B$2:$B$57,B2)

Older versions:

=MIN(IF($B$2:$B$57=B2,$D$2:$D$57))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly brackets { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

@ JozefJ

As near as I can see your formula is working properly. I agree with Ali. How would you calculate them manually?

I did note that the only truly blank cell in column C is C44. The rest all apparently have "" in them. I don't know if there are regional differences in Excel versions that would make this an issue or not. The non blanks return false here C43<>TEXT(,) (""<>"" ) and false here C44<>TEXT(,) (0<>"") in my version.

And speaking of versions please update your profile. AGGREGATE was introduced in Excel 2010.

Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

PS As Dave has said, you should update your user profile to reflect the version of Excel that you are currently using.

Originally Posted by JozefJ

Hi friends,
the aggregate function should returns the minimum of specified range, for each cell in a column, but it returns wrong values.
......

Originally Posted by JozefJ

Thank you Dave. Excuse me, Im not sure what you understand as "manually". I need cycle (or alternative to) bounded by condition of the same content in column B, for the search of a minimum of values in column C, remember this minimum, come back to first cell in column D, write in the whole range and start new cycle with new content in B.
Ok, thanks, I will check the version.

So far your responses have been a bit vague for purposes of solving this dilemma. What specific values is it returning .... type those in manually in their respective locations and post that workbook so we can see where and what those "wrong values" are. Please also include a column of hand typed correct answers.

This is from the file 1st post.

I see no wrong answers in column D.

	A	B	C	D
1	name1	name2	countOfname1	minOfC_inRangeName2
8	NE-04	40	7	7
11	PA-10	40	3	3
25	PI-01	40	14	14
32	A1-11	13	7	7
36	PA-07	41	4	4
40	PA-35	41	4	4
45	PA-36	41	5	5
53	PI-01	41	8	8
57	PI-05	41	4	4

Aha!

There are 2 sheets in the attachment! The first one is not visible at upload and it is named 'desired results(column D)'. This makes for confusing dialogue.

It is cell C44 in the 'desired results(column D)' sheet that is causing the problem. It is really blank ... the others are not.

Enter this in D2 of 'desired results(column D)' and fill down.

Formula:

Please Login or Register  to view this content.

The wrong answers are as you report in D33:D57... all 0.

Now go to cell C43 of the 'desired results(column D)' sheet, grab the fill handle and drag down one cell. All those problem cells in D33:D57 fill in with 4s.

Does this do what you want?

When formulas don't work as expected we start examining the data for irregularities ... mismatched data types, numbers that are actually text, extra spaces etc. etc. etc...

In this case =ISTEXT in a temporary helper column revealed this one. =ISBLANK further confirmed it.

What I hadn't realized was the second sheet off screen to the left and that desired results(column D) and wrong results(column D) you were referring to were actual sheet names. LOL Live and learn.

Anyway you are welcome. Glad to hear we got it resolved.