Category Archives: Excel
Excel, Sum best N numbers in range
Recently I faced an interesting challenge. How to create an Excel formula for calculating the final sum of the best N scores out of M (where N > M)?
The real situation is as follows. In our chess club we have 16 tournaments per year. The winner is the player who scores the most points in 12 tournaments. So, I have to sum the best 12 scores out of 16.
I was trying with different functions. An interesting one was the RANK.AVG function, which returns the rank of value in the specified range. With some additional formulas and a special transformation table I finally succeded. But …. It was to complicated and I was not happy with it.
As it is usualy so, I found another function after spending to much time.
The right function is LARGE or her sister SMALL:
The final formula in my case was:
=SUM(A2:P2)-LARGE(A2:P2;16)-LARGE(A2:P2;15)-LARGE(A2:P2;14)-LARGE(A2:P2;13)
(in the first step I sum all values and then subtract the four worst scores)
Excel, custom function (izdelaj si svojo funkcijo)
Včasih nam pride prav, da si v Excelu izdelamo svojo funkcijo. Ponavadi je to takrat, ko isto funkcijo potrebujemo v več celicah. Funkcijo izdelamo z Visual Basicom. Tukaj je nekaj enostavnih primerov:
Function MyStringA(Length As Integer) As String 'Funkcija vrne niz znakov x v dolžini Length znakov MyStringA = String(Length, "x") End Function Function VzemiFormulo(Cell As Range) As String 'Funkcija prepiše formulo izbrane lokacije VzemiFormulo = Cell.Formula End Function Function IsGreen(Cell As Range) As Boolean 'Funkcija preveri, ce je ozadje podane celice zelene barve If Cell.Interior.ColorIndex = 3 Then IsGreen = True else IsGreen = False End Function
Funkcijo uporabimo v Excelu na običajen način, tako kot standardne funkcije:
Klic funkcije v Excelu Read the rest of this entry |