+ Reply to Thread
Results 1 to 13 of 13

Apurar valores

  1. #1
    Registered User
    Join Date
    08-18-2021
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    3

    Apurar valores

    olá pessoal,

    Necessito de encontrar na tabela em anexo o maximo de viaturas que se encontram em simultâneo no exterior.

    alguma função que devolva esse valor?

    obrigado
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,141

    Re: Apurar valores

    Welcome to the forum!

    This is an interesting problem. I don't know any short cut formula. I did it the long way.

    Hopefully some experts will pick it and help solve the problem in a more elegant way.


    Using Google Translate: https://translate.google.com/

    Bem-vindo ao fórum!

    Este é um problema interessante. Não conheço nenhuma fórmula de atalho. Eu fiz isso pelo caminho mais longo.

    Esperançosamente, alguns especialistas irão escolher e ajudar a resolver o problema de uma forma mais elegante.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-18-2021
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    3

    Re: Apurar valores

    Obrigado pela ajuda.
    Sua solução me ajudou muito, porém ainda é muito complicado de aplicar no meu caso real, pois tenho muitos valores (1 ano com +10.000 linhas).
    Talvez alguém ajude como você fez

    Atenciosamente.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,690

    Re: Apurar valores

    Perhaps the following will help.
    1. The names of the vehicles are listed in F1:J1
    2. Cells E2:E16 are populated using: =COUNTIFS(F2:J2,">0")
    3. Cells F2:J16 are populated using: =COUNTIFS($A$2:$A$16,$A2,$C$2:$C$16,"<"&$D2,$D$2:$D$16,">"&$C2,$B$2:$B$16,F$1)
    4. Cell M1 displays the maximum vehicles out at one time using: =MAX(E2:E16)
    Note that this method calculates five vehicles to be out at one time, which by observation appears to be correct i.e. when vehicle A goes out at 10:30, vehicles B and C are still out and before vehicle A returns at 11:00 vehicles D and E go out.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,141

    Re: Apurar valores

    Thanks JeteMc for answering the call of the calvary.

    Cell B6 I believe is wrongly stated as Viaura D. If changed to Viatura D, max is 4.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,690

    Re: Apurar valores

    @josephteh, Good catch - Thank You.
    I also changed I1 in my file from Viaura D to Viatura D which puts the max at 5.

  7. #7
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,141

    Re: Apurar valores

    Oh ya.. by the way.. great formula! Here is a Rep!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,690

    Re: Apurar valores

    @josephteh, Thank You for the feedback and for the added reputation. I hope that you have a blessed day.

  9. #9
    Valued Forum Contributor
    Join Date
    01-05-2013
    Location
    Singapore/China
    MS-Off Ver
    H&B 2016
    Posts
    1,141

    Re: Apurar valores

    You are welcome, JeteMc. You too have a blessed day.

  10. #10
    Registered User
    Join Date
    08-18-2021
    Location
    Portugal
    MS-Off Ver
    2010
    Posts
    3

    Re: Apurar valores

    muito bom!
    obrigado pelo vosso esforço,
    acho que consegui resolver o meu problema.

    muito obrigado.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,109

    Re: Apurar valores

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    52,109

    Re: Apurar valores

    You have given me NEGATIVE reputation - was there a problem? You added the words "thank you" to the reputation comment box, so was this a mistake?

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,690

    Re: Apurar valores

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-30-2021, 11:17 PM
  2. Ler valores de um site da net que necessita de password
    By Vinhas in forum Non English Excel
    Replies: 1
    Last Post: 12-18-2019, 12:20 PM
  3. localizar valor entre intervalo de 2 valores
    By dugsblack in forum Non English Excel
    Replies: 9
    Last Post: 02-20-2019, 06:43 PM
  4. localizar valor entre intervalo de 2 valores
    By dugsblack in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-19-2019, 10:25 AM
  5. Usando una Imagen para Ver Valores de Celda en Otras Hojas
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-28-2005, 09:29 AM
  6. Ocultando la Visualización de los Valores Cero
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-28-2005, 04:03 AM
  7. ocutar,fixar valores
    By Juninho PUC-Minas in forum Excel General
    Replies: 1
    Last Post: 02-28-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1