Fórmula para somar n menores valores de uma lista


#1

image

Para calcular a fórmula acima no Excel eu usei:

=((2*(((SOMASE(D13:D18;"<"&MENOR(D13:D18;C9);D13:D18)))))/(C9-1))-MENOR(D13:D18;C9)

Em que ela pega todos os valores em D13 a D18, soma os que forem menor que o M menor, multiplica-os por 2 e divide-os por M-1 e depois diminui o M menor.
Sendo o M na célula C9 (segundo menor, terceiro menor, quarto menor, e por aí vai, dependendo do valor em C9).

Por exemplo, numa lista com 6 valores, e que o número M (C9) é 3:
Valores:
10
20
30
40
50
60

A fórmula calcularia:
=(2*(10+20)/(3-1)) - 30
O resultado seria 0.

Correto, não? Não!

A fórmula não apresenta o resultado correto se os valores menores que o M menor forem tiverem 1 ou mais iguais!

Exemplo:
10
10
20
30
40
50

Assim, ela somaria todos os valores menores que o M menor (30): 10+10+20=40.
O problema é que a soma aplicada no SOMASE não é limitada a um número limite de somas.

De acordo com a fórmula que postei inicialmente esse número de somas deve ser limitado a M-1, por exemplo, neste último exemplo a fórmula deveria somar apenas o 10+10, 2 valores somente.

Então vem a pergunta, é possível fazer isso com alguma fórmula do próprio Excel ou devo recorrer ao VBA?

Obs.: Não posso colocar MENOR(D13:D18;1)+MENOR(D13:D18;2) porque o intervalo (D13:D18) e o número M (C9) são variáveis. Intervalo podendo ser por exemplo D13:D23 e número M 5, então o número de “MENOR” é variável.


#2

Tive então a ideia de criar uma função no VBA, função chamada SOMAMENORES que seria basicamente assim:

Public Function SOMAMENORES(Intervalo() as Double, Menores as Integer) As Double

If Intervalo.Length <= 0 Then Exit Function

SOMAMENORES = 0
Dim i As Integer
i = 1

 For i <= Menores Then
      SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
 Next i

End Function

Mas não funciona, como fazer funcionar?


#3

Olá, @Bragato. Tudo bem?

Não entendi bem o seu caso, mas notei que no seu código tem um Then sobrando e um End faltando. Ficaria assim:

Public Function SOMAMENORES(Intervalo() as Double, Menores as Integer) As Double

If Intervalo.Length <= 0 Then Exit Function

SOMAMENORES = 0
Dim i As Integer
i = 1

 For i <= Menores
      SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
 Next i
End Function

End

#4

Boa noite, xavier. Consegui utilizando o seguinte:

Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double

SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1

Do Until i = MenoresAssistant

    SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
    i = i + 1
    
Loop

End Function


#5

Cheguei em um problema.
A fórmula em si funcionou, consegui fazer cálculos usando ela.
Mas sempre que vou inserir a fórmula usando o VBA ele dá o erro seguinte:

Mas se eu usar a fórmula em qualquer célula digitando manualmente dentro do excel =somamenores… ela funciona perfeitamente!!

Como corrigir este problema?


#6

Estou inserindo a fórmula com este código:


#7

@Bragato, seu Excel está em português? Você está tentando inserir a função como texto na célula. Sendo assim, e o seu excel estiver em português só vai funcionar se você usar Menor.


#8

Consegui. Alterei o .Value para .Formula (antes tinha feito assim e tinha dado errado também, só mudei para .Value pra ver se funcionava, não funcionou também mas o erro foi o mesmo).

O problema foi que o C9 do Sub fbkestatistico no momento em que a fórmula era inseria pelo VBA é um ponto de interrogação, que ao final da execução do código se tornaria um valor. Então o VBA apresentava o erro pois o C9 precisaria ser um número inteiro conforme declarado na Function SOMAMENORES. Isso porque coloquei Application.Calculation = False no início de todas as macros que se executam ao alterar uma célula e …= True no final de tudo, e o fbkestatistico tentava calcular com C9 travado com o “?”, daí o erro. Tirei as declarações de Application.Calculation e tudo funcionou sem erros.