Excel Cálculos com números acima 1E+308

[FASP]

Devido as limitações de valor máximo do Excel não consigo realizar os calulos que eu preciso.
Felizmente, a calculadora do windows tem me ajudado com isso.

Em B20:B24 eu tenho o valor das variáveis que alimentam a fórmula.
Eu preciso atualizar C3:18 diariamente conforme a alteração dessas variáveis.

Então eu tenho várias linhas (B3:18) com a fórmula:
="("&A3&" yroot (1/("&$B$20&" + (((((("&$B$21&"*("&A3&"-1))/"&$B$22&") yroot (1/0,38685))*18000) - "&$B$23&") / "&$B$24&"))))"

Onde A3:18 são 16 valores de 1,25 a 5 em ordem crescente com intervalo de 0,25 (1; 1,25; 1,5; 1,75; etc...).

Que retornam o resultado como texto:
(1,25 yroot (1/(0,9167 + ((((((8,68e178*(1,25-1))/1217160) yroot (1/0,38685))*18000) - 5,5e450) / 2,4e451))))

*OBS: "yroot" é na calculadora do windows o equivalente ao símbolo de potenciação, que no Excel é "^".

Daí eu copio esse conteúdo e colo na calculadora científica do windows, que me retorna o resultado que eu jogo na linha correspondente da coluna C3:18.

Por ser apenas 16 linhas, tem sido fácil realizar o cálculo e fazer a comparação.
A finalidade é encontrar o maior resultado (em C3:C18), e tem funcionado bem até o momento, apesar de trabalhoso.

O problema é que agora eu preciso fazer a mesma coisa, só que com 400 linhas, (valores de 1-5 com intervalos de 0,01)
E da forma atual é impraticável, devido a necessidade de atualização diária.

Gostaria de saber se tem alguma forma de simplificar esse trabalho, seja com macro, VBA ou qualquer coisa do gênero.

O ideal seria um "botão" que fizesse a mágica de calcular todas as linhas e colocar cada resultado na linha correspondente da coluna C3:C18.

Mas qualquer coisa já ajuda.

Desde já agradeço a colaboração da comunidade.

PS: A planilha está com células de fórmula "Bloqueadas" para proteção de dados. Mas está sem senha.

 

PS2: Como solução alternativa tentei apelar para o "CAS Máxima" (programa especializado em calculos complexos) para tentar encontrar o vértice da função plotando o gráfico para (X = 1,1 ~10), mas ele não reconhece a função devido a erros que eu não soube interpretar, devido a minha total falta de experiência com o programa. Não sei se estou fazendo algo errado, com as variáveis já substituídas pelos valores numéricos de hoje a função seria essa: 

f (x) := (x ^ (1/(3,1666666666666666666666666666667 + ((((((5,24e189*(x-1))/1217160) ^ (1/0,38685))*18000) - 9e478) / 1,44e479))))
Se alguém tiver experiência com o programa citado ou com qualquer outro que me ajude com esse calculo, já resolveria o problema também.

FT - WORKING.xlsx

[Visitante]

 

Talvez seja possível efetuar os cálculos via macro, mas para isso precisamos saber que cálculos são feitos com os conteúdos de B21, B23 e B24.

 

[FASP]

11 horas atrás, osvaldomp disse:

 

Talvez seja possível efetuar os cálculos via macro, mas para isso precisamos saber que cálculos são feitos com os conteúdos de B21, B23 e B24.

 

@osvaldomp

 

Os campos B20:24 são os valores referentes a Tcurrent, Fcurrent, Pbonus, Gcurrent e Gh.
São dados que sofrem alteração diariamente e precisam ser alimentados manualmente.

Os quais são apenas as "variáveis" no cálculo da seguinte equação:

CV = (M^(1/(Tcurrent + ((((((Fcurrent*(M-1))/Pbonus)^(1/0,38685))*18000) - Gcurrent) / Gh)))) 

 

A3:18 são valores referentes a variável "M" nessa mesma equação.

 

No Excel, a fórmula, está como texto: 

 

="("&A3&" yroot (1/("&$B$20&" + (((((("&$B$21&"*("&A3&"-1))/"&$B$22&") yroot (1/0,38685))*18000) - "&$B$23&") / "&$B$24&"))))"

 

O resultado é um "texto" que eu copio e colo na calculadora do Windows, que executa o calculo. 

Atualmente, tenho que realizar o procedimento linha por linha em B3:18, colando cada resultado obtido na linha correspondente em C3:18 

 

Na forma "original" o Excel não consegue executar o calculo, mas B3 ficaria assim:

 

=(A3 ^ (1 / ($B$20 + (((((($B$21 * (A3 - 1)) / $B$22) ^ (1 / 0,38685))*18000) - $B$23) / $B$24))))

 

De forma que o ideal para mim seria obter de forma automática todos os valores de M entre 1,01 ~ 10 com intervalo de 0,01, pois como eu disse atualmente eu faço isso de forma intuitiva dentro de determinado intervalo com ajuda da calculadora do Windows utilizando os campos A3:18.

 

A finalidade da planilha é apenas descobrir o valor de M para o qual a equação retorna o MAIOR CV.

 

CV é um coeficiente determinado pela equação inicial:

CV = (M^(1/Ttotal))

 

A grande dificuldade se encontra no fato de Ttotal ser uma variável de cálculo bem trabalhoso e que se altera diariamente.

 

Caso seja de alguma ajuda, o desmembramento da equação de forma bem detalhada segue abaixo:

 

Ttotal = (Tcurrent + Tmissing)

Portanto: CV = (M^(1/(Tcurrent + Tmissing)))

 

Tmissing = (Gmissing / Gh)

Portanto: CV = (M^(1/(Tcurrent + (Gmissing / Gh))))

 

Gmissing = (Ggoal - Gcurrent)

Portanto: CV = (M^(1/(Tcurrent + ((Ggoal - Gcurrent) / Gh))))

 

Ggoal = (((Fgoal/Pbonus)^(1/0,38685))*18000)

Portanto: CV = (M^(1/(Tcurrent + (((((Fgoal/Pbonus)^(1/0,38685))*18000) - Gcurrent) / Gh))))

 

Fgoal = (Fcurrent*(M-1))

Portanto: CV = (M^(1/(Tcurrent + ((((((Fcurrent*(M-1))/Pbonus)^(1/0,38685))*18000) - Gcurrent) / Gh))))  

 

E é isso... 

Me coloco a disposição para qualquer esclarecimento e agradeço a sua atenção.

[deciog]

@FASP , Boa Tarde

 

Se você souber Inglês, ou souber trabalhar com Google tradutor, você poderia postar num site onde tem um fera em resolver o seu problema, aqui também tem fera mas sei que formulas do Excel pode ser resolvido

 

Cadastre neste site https://chandoo.org/forum/  

 

Explica da mesma forma que explicou acima 

 

vale a pena tentar pois eu não sou muito bom na matemática, e lá tem ótimos matemáticos

 

Eu sei que neste fórum também tem ótimos feras mas o seu problema é complicado

 

Decio

 

 

 

 

[Visitante]

 

16 horas atrás, osvaldomp disse:

 

Talvez seja possível efetuar os cálculos via macro, mas para isso precisamos saber que cálculos são feitos com os conteúdos de B21, B23 e B24.

 

 

Me parece que eu não consegui comentar com precisão o que você deveria definir. Aqui vai nova tentativa.

 

Por exemplo, o texto formado em B3 o Excel/VBA não "sabem" como resolver, basicamente por duas razões, por causa da função "yroot" e porque nas células B21, B23 e B24 o conteúdo é considerado como texto pelo Excel/VBA, e por isso não "sabem" que cálculo fazer.

E segundo os seus comentários, a calculadora do Windows no modo científico, "sabe" como resolver.

 

A função "yroot" você já informou antes que corresponde à potenciação, então repetindo o comentário que fiz antes, falta você informar que cálculo é feito pela calculadora do Windows com os conteúdos daquelas células, que no seu exemplo trazem respectivamente:

B21 = 5,24e189 ~~~> informe qual o cálculo que é feito com esta expressão e qual o resultado ?

B23 = 9e478 ~~~> informe qual o cálculo que é feito com esta expressão e qual o resultado ?

B24 = 1,44e479 ~~~> informe qual o cálculo que é feito com esta expressão e qual o resultado ?

 

Não importa se os conteúdos acima são constantes ou se mudam, desde que mantenham  a máscara "dígitos e dígitos".

São só os três esclarecimentos acima, tudo o mais está entendido, por ora é desnecessário você repetir as explicações.

[FASP]

@osvaldomp

Não sei se entendi, mas vamos lá... 

 

Bem, como eu disse acima, na "linguagem de cálculo do Excel", o conteúdo da célula B3 ficaria da seguinte forma:

=(A3 ^ (1 / ($B$20 + (((((($B$21 * (A3 - 1)) / $B$22) ^ (1 / 0,38685))*18000) - $B$23) / $B$24))))

 

As células B21, B23 e B24 no formato "##e##" não são expressões. São apenas valores em notação científica aceitos no formato da calculadora do windows. O Excel também reconhece valores em notação científica, mas apenas até 1e308 devido a limitação.

 

Exemplos:

30 = 3*(10^1) = 3e10 

Na linguagem aceita pelo excel ficaria 3E+10

 

80.000.000 = 8*(10^7) = 8e7

Na linguagem aceita pelo excel ficaria 8E+50

 

O excel resolveria a formula que coloquei acima facilmente, se não fosse pela limitação de valor máximo a 1e308. Daí o meu problema.

 

Mas se não é essa a informação de que precisa, pode tentar colocar novamente com outras palavras. É que eu sou meio lento mesmo kkk

 

Agradeço a paciência.

adicionado 35 minutos depois

@deciog Agradeço, mas devido as limitações do meu "espanglês" (kkk) precário eu prefiro aguardar por aqui alguns dias antes de recorrer aos gringos. 

 

E acredito que meu problema nem seja assim tão complexo.

Eu só preciso que o Excel reconheça e execute cálculos com valores MUITO altos (como 10^500 por exemplo).

Basicamente, meu sofrimento se resume a isso.

 

Se alguém me der um exemplo de como fazer isso com alguma célula, acredito que talvez eu consiga replicar a aplicação na minha planilha com as células que eu preciso.

 

Não conhecia esse Chandoo, vou deixar salvo aqui para utilizar como ultimo recurso.

Obrigado.

[deciog]

@FASP

 

Boa olha o link  https://chandoo.org/forum/threads/calculations-with-numbers-over-1e308-its-possible.42874/

 

Decio

 

[FASP]

@deciog Sim, eu coloquei lá.

 

Me indicaram o xlPrecision, um suplemento bem bacana.

 

O problema é que ele tem uma parte chata, onde aparece uma caixa de diálogo toda vez que ele calcula, o que me obriga a digitar um CAPTCHA de até 12 dígitos. Ao atualizar uma célula que alimenta as fórmulas ele recalcula todas as 900 células e para cada um desses cálculos sou obrigado a digitar o CAPTCHA. O que para mim é completamente inviável... 

 

Tem a versão paga sem os captchas, mas sai por 90 dólares (R$ 361,42).

O suplemento é ótimo e as funcionalidades vão muito além das minhas necessidades. 

O valor é justo, mas a relação custo-benefício, no meu caso, não são viáveis.

 

Continuo em busca de uma solução alternativa... 

 

Por hora, estou utilizando a calculadora do windows em conjunto com o MACRO RECOVER, que copia um conjunto de ações e replica quantas vezes eu quiser em alta velocidade. Isso me permite: 

 

Copiar a formula (Ctrl+C), Alternar para a calculadora do Windows (Alt+Tab), Colar a fórmula (Ctrl+V), Copiar o resultador (Ctrl+C), Retornar a planilha (Alt+Tab), Selecionar a célula ao lado (KeyRight), Colar o resultado (Ctrl+V), Deixar selecionada a próxima linha a ser calculada (KeyDown, KeyLeft). 

 

Keyboard : ControlLeft : KeyDown
DELAY : 1
Keyboard : C : KeyPress
Keyboard : ControlLeft : KeyUp
Keyboard : AltLeft : KeyDown
DELAY : 1
Keyboard : Tab : KeyPress
Keyboard : AltLeft : KeyUp
Keyboard : ControlLeft : KeyDown
DELAY : 1
Keyboard : V : KeyPress
DELAY : 50
Keyboard : C : KeyPress
Keyboard : ControlLeft : KeyUp
Keyboard : AltLeft : KeyDown
DELAY : 1
Keyboard : Tab : KeyPress
Keyboard : AltLeft : KeyUp
Keyboard : Right : KeyPress
Keyboard : ControlLeft : KeyDown
DELAY : 1
Keyboard : V : KeyPress
Keyboard : ControlLeft : KeyUp
Keyboard : Down : KeyPress
Keyboard : Left : KeyPress

 

Daí eu coloco para repetir o procedimento 900 vezes e ABRACADABRA! 

Em 15 minutos ele resolve todas as 900 células comigo AFK.

 

Não é o ideal... mas quebrou o galho e reduziu bastante o tempo de execução.

[deciog]

@FASP , Bom Dia.

 

Fico contente que eles tentaram ajudar, o problema maior é que o Excel só aceita 15 dígitos + o sinal ou seja 2 Bytes, espero que na versão 2020 ele muda isso

 

Abraços

 

Decio

 

[Visitante]

Veja se o código abaixo pode ajudar.

Talvez no SO Windows 10 não funcione.
 

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" _
      (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" _
      (ByVal dwMilliseconds As Long)
#End If

Const WM_CLOSE = &H10

Sub CalculaCoeficientes()
  Dim cell As Range, hCalc As Long, cw
  [C3:C18] = ""
  cw = Shell("C:\windows\system32\calc.exe", vbNormalFocus)
  Sleep 500
  For Each cell In Range("B3:B18")
    On Error GoTo out
    cell.Copy
    Sleep 100
    SendKeys "^v", True
    DoEvents
    Sleep 200
    Application.CutCopyMode = False
    SendKeys "^c", True
    DoEvents
    cell.Offset(, 1).Select: ActiveSheet.Paste
    ActiveCell.Value = ActiveCell.Value / (10 ^ Right(ActiveCell.Value, 2)): ActiveCell.NumberFormat = "General"
    Next
out:
 SendKeys "{NumLock}", True
  hCalc = FindWindow(vbNullString, "Calculadora")
  If hCalc <> 0 Then PostMessage hCalc, WM_CLOSE, 0&, 0&
End Sub

 

[FASP]

@osvaldomp Parece promissor.

Mas deu esse erro ao tentar executar:

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Erro de compilação:

'Sub' ou 'Function' não definida
---------------------------
OK   Ajuda   
---------------------------

 

Ao aparecer a mensagem de erro acima ele também deixa em evidência o termo "FindWindow" na linha:

hCalc = FindWindow(vbNullString, "Calculadora").

 

Não entendo nada do assunto.

Como proceder?

*OBS: Aqui o SO é W7 Ultimate, SP1 de 32 Bits.

E o Excel é o 2010

[Visitante]

Desculpe, falha minha. Faltaram duas declarações, estavam em outro módulo, acabei esquecendo delas.

 

Acrescente as duas linhas abaixo nas declarações do topo. Coloque-as logo abaixo da linha Const WM_CLOSE = &H10.

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

 

[FASP]

@osvaldomp Ficou quase perfeito.

 

Eu estava apanhando porque algumas células da planilha estão bloqueadas e isso estava impedindo o Macro.

Me arrisquei a fazer um pequeno ajuste, de forma que o resultado final foi esse:

 

 

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" _
      (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" _
      (ByVal dwMilliseconds As Long)
#End If

Const WM_CLOSE = &H10

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Sub CalculaCoeficientes()

    Sheets("Plan1").Select
    ActiveSheet.Unprotect

  Dim cell As Range, hCalc As Long, cw
  [C3:C18] = ""
  cw = Shell("C:\windows\system32\calc.exe", vbNormalFocus)
  Sleep 500
  For Each cell In Range("B3:B18")
    On Error GoTo out
    cell.Copy
    Sleep 100
    SendKeys "^v", True
    DoEvents
    Sleep 200
    Application.CutCopyMode = False
    SendKeys "^c", True
    DoEvents
    cell.Offset(, 1).Select: ActiveSheet.Paste
    ActiveCell.Value = ActiveCell.Value / (10 ^ Right(ActiveCell.Value, 2)): ActiveCell.NumberFormat = "General"
    Next
out:
 SendKeys "{NumLock}", True
  hCalc = FindWindow(vbNullString, "Calculadora")
  If hCalc <> 0 Then PostMessage hCalc, WM_CLOSE, 0&, 0&

    Sheets("Plan1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

Agora atualiza os valores diariamente sem a necessidade de outros programas e mais rápido.

Agradeço muito pela colaboração!

valeu mesmo!

[FASP]

@osvaldomp Sem querer abusar, mas já abusando, que modificação eu teria que fazer no macro para que execute em SO de 64 Bits?

 

É que eu eu também preciso rodar a planilha em casa no FDs.

É o mesmo Windows, só que é de 64, ao invés de 32.

[Visitante]

Eu uso Windows 8.1 64 bits e aqui roda de boa.

[FASP]

@osvaldomp Aqui em casa dá esse erro assim que eu abro a planilha:
 

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Erro de compilação:

O código desse projeto deve ser atualizado para uso em sistemas de 64 bits.  Analise e atualize as instruções Declare e, em seguida, marque-as com o atributo PtrSafe.
---------------------------
OK   Ajuda   
---------------------------
 

Alguma ideia do que poderia ser?

 

[Visitante]

Em 30/10/2019 às 12:33, FASP disse:

@osvaldomp Sem querer abusar, mas já abusando, que modificação eu teria que fazer no macro para que execute em SO de 64 Bits?

 

Esse erro provavelmente é devido ao Office 64 bits e não ao SO 64 bits.

 

Tente após substituir as duas linhas conforme abaixo. Acrescentei PtrSafe.

Se você pretende rodar o código tanto em Office 32 bits como em 64 aí talvez sejam necessários outros ajustes, mas primeiro faça os testes no 64 pois o resultado servirá para outros ajustes, se necessário.

Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As LongPtr