Large function doesn´t include negative numbers

[dani9]

Hi,

I have a table like this
A B C D E
<TABLE style="WIDTH: 339pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=452 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" width=64 height=20>299899</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=64>2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=119>Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 88pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=117>GT CM </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 66pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=88>660874,00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>301283</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT CM </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">80679,33</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>282200</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT CM </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">11306,51</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>302359</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT CM </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">5637,28</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>302300</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT CM </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">1332,00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>298032</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT IES</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">0,00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>295944</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT CB IT</TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">0,00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>299101</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">2011</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">Operational Risk</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">GT CM </TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">-60485,81
</TD></TR></TBODY></TABLE>

and i need to use large function, but the problem is that is doesn´t include the negative values in the end result.

The final outcome is:
<TABLE style="WIDTH: 107pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=143 border=0><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 107pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=143 height=20> 660.874,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>80.679,33 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11.306,51 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5.637,28 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1.332,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 0,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 0,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> - € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> - € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> - € </TD></TR></TBODY></TABLE>

But it should be:
<TABLE style="WIDTH: 107pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=143 border=0><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 107pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=143 height=20>660.874,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>80.679,33 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11.306,51 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5.637,28 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1.332,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 0,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> 0,00 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>- 60.485,81 € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> - € </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> - € </TD></TR></TBODY></TABLE>

So the Zeros must also be included. I dont know why the negative value is not included??
This is my formula:
{=LARGE(($B$2:$B$160000=2011)*($C$2:$C$160000="Operational Risk")*($D$2:$D$160000<>"not relevant")*($E$2:$E$160000);ROW(A1))}

anyone? help will be appreciated!

 

[BarCodeWiz]

Dani,

I've no idea what you're trying to do with this function here (looks like the formula didn't copy correctly?), but the correct way to use it would be something like:

=LARGE(range, v)

range could be E2:E160000
and
v is the n'th largest number from the set.
For example,

=LARGE(E2:E160000,1)

will give you the largest number from the column, while:

=LARGE(E2:E160000,COUNT(E2:E160000))

will give you the smallest number. If the smallest number is negative, then that will be it.

 

[moonfish]

Are there blank fields in your data? Chances are that some blanks are counted as having a value of zero as opposed to having no value. I never really managed to put my finger on it but here's an example:

Excel Workbook
	A	B	C	D
1	value		0	>0
2	0	FALSE	TRUE	FALSE
3	*	FALSE	FALSE	TRUE
4	0	FALSE	TRUE	FALSE
5	*	FALSE	TRUE	FALSE
Sheet3

A1=0, A2="", A3=A4 and A4 is actually blank.

 

[BarCodeWiz]

...and of course, the easiest way to get the smallest number would be to use the =SMALL function

 

[GlennUK]

Your empty cells will evaluate to zeroes ... so -60488,81 will be the 159999th largest value.

 

[dani9]

OK, i know the large function. I should have written that i have much more data that these couple of rows. I just gave you a sample.
So i need to sort it by value, from largest to smalles (-60.485,81) with additional criteria, because i have other risk types and other years in the data...

And the values are as negative as positive, but it is never a zero - what you see here is actually 0,0000345, narrowed down to 2 decimal points.

any suggestions?

 

[GlennUK]

And do you have empty cells in the referenced area?

 

[dani9]

And do you have empty cells in the referenced area?

Yes, i do. Is this the problem??

 

[GlennUK]

Yes, i do. Is this the problem??

oh yes indeed. Is that the blank cells are after the real data ... or do you have blanks randomly scattered within the data range?

 

[dani9]

oh yes indeed. Is that the blank cells are after the real data ... or do you have blanks randomly scattered within the data range?

the blanks are after the real data. I now have first 152.139 rows populated with data, but in my original formula the range checks first 200.000 rows. so the last 50.000 are blank.

If it is due to the blanks, it will be really ackward, if i will have to change the range each time, because i keep getting the data each month and inserting it to make calculations. and each time the number of rows is bigger.