Search:

Type: Posts; User: jasonb75; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.20 seconds.

  1. Replies
    2
    Views
    48

    Re: COUNTIFS(AND( help

    I just noticed a typo in the formula above, it should be

    =COUNTIFS($A$2:$A$7,$I2,$B$3:$B$8,"ACTUAL",C$3:C$8,"PRES")

    Note that the ranges used to count "actual" and "pres" start and finish one...
  2. Replies
    2
    Views
    48

    Re: COUNTIFS(AND( help

    Try this in J2, then drag right and down.

    =COUNTIFS($A$2:$A$7,$I2,$B$3:$B$8,"ACTUAL",C$23:C$8,"PRES")
  3. Replies
    7
    Views
    99

    Re: Using "OR" statement with DATEDIF

    You shouldn't need to change the year if you use datedif properly, see post 2 or type datedif into google.
  4. Replies
    7
    Views
    99

    Re: Using "OR" statement with DATEDIF

    The formula that you say works perfectly makes no sense, Employees that started on 8/1/18 have completed over 1 year of service based on the current date, so why 0?
    If you want proper years of...
  5. Replies
    11
    Views
    178

    Re: Problem with NETWORKDAY.INTL Function

    When I replied earlier, I misread the last line of your post as 'half day off' instead of '1 or 2 days off'.

    In hindsight, I think that this is correct
    ...
  6. Replies
    11
    Views
    178

    Re: Problem with NETWORKDAY.INTL Function

    Perhaps

    =WORKDAY($E$8+1,$F$19/($B$23+1),IF($D$16:$D$48<>"",$D$16:$D$48+1,))

    Or

    =WORKDAY($E$8+1,($F$19+1)/($B$23+1),IF($D$16:$D$48<>"",$D$16:$D$48+1,))

    Without knowing what is expected, or...
  7. Replies
    8
    Views
    252

    Re: Unique in Range

    Something that I just tried in another thread inspired this idea.

    Using the same layout as Peter in the previous reply, try this array formula in H2, confirmed with Ctrl Shift Enter
    ...
  8. Re: How do I get Excel to return a value based on the max value in a table?

    With newer excel versions, perhaps this with wrapped text (array confirmed with Ctrl Shift Enter)

    =TEXTJOIN(CHAR(10),1,IF($B$2:$F$5=MAX($B$2:$F$5),$A$2:$A$5&"&"&$B$1:$F$1,""))
  9. Replies
    3
    Views
    177

    Re: Macro for DATEVALUE

    If you're simply trying to get around the problem of selecting a column, running the macro, selecting the next column running it again, etc. Then try this with the whole range selected.

    Sub...
  10. Replies
    3
    Views
    177

    Re: Macro for DATEVALUE

    Try copying an empty cell, then paste to your table of dates using PasteSpecial > Add.

    I think it should work with your dates if text to columns is working, if it does then you should be able to...
  11. Re: Listing holidays separately and finding values in aggregate

    As before, these will need array confirming.

    Excel 2007

    =IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$I$2:$I$10,0)),IF(WEEKDAY($A$2:$A$10)<6,$A$2:$A$10)),ROWS(C$2:C2)),"")

    Excel 2003 or older...
  12. Replies
    6
    Views
    56

    Re: Laptops and large Excel files

    This could be worth a read. https://docs.microsoft.com/en-us/office/troubleshoot/excel/clean-workbook-less-memory

    You might find that there is not one specific thing causing the problem, but an...
  13. Replies
    6
    Views
    56

    Re: Laptops and large Excel files

    What is the actual file size?

    While graphics (embedded images) might inflate the size of the file, I doubt that they should affect performance too much.
  14. Replies
    11
    Views
    178

    Re: Problem with NETWORKDAY.INTL Function

    Ignore that, I had overlooked the most simple fix

    Array confirmed as before.
    ...
  15. Replies
    11
    Views
    178

    Re: Problem with NETWORKDAY.INTL Function

    Can you modify the layout slightly?

    I have just noticed that your holidays are spread over 2 columns, so the change will not work. I'm going to try a couple of other methods, but I think that it...
  16. Replies
    11
    Views
    178

    Re: Problem with NETWORKDAY.INTL Function

    Array confirmed with Ctrl Shift Enter

    =IF($K$7="","",IF(NETWORKDAYS($K$7+1,$E$8+1,$T$35:$V$67+1)<1,"-",NETWORKDAYS($K$7+1,$E$8+1,$T$35:$V$67+1)))

    If you have blanks in the holidays range then...
  17. Re: Add asterisk to string with number format

    Assuming that this is to format barcodes, a custom format of \*@\* should do it.
  18. Re: Listing holidays separately and finding values in aggregate

    For excel 2007, array confirmed with Ctrl Shift Enter.

    =IFERROR(SMALL(IF(ISERROR(MATCH($A$2:$A$10,$C$2:$C$6,0)),$A$2:$A$10),ROWS(E$2:E2)),"")

    For excel 2003, array confirmed with Shift Ctrl...
  19. Re: Excel 2010 formula to look up duplicates so I can contra them off

    Assuming references in column A and amounts in column B, try this formula

    =COUNTIFS(A:A,A2,B:B,-B2)

    This should give you a count of 1 for each record that has a positive amount and a...
  20. Replies
    8
    Views
    252

    Re: Unique in Range

    This came up as the result of using the search box at the top of the page
    ...
  21. Re: Need Help with Array Formula to return multiple results for specified criteria

    Looks like you almost had it right,

    The formula in A7 should be ROWS(A$7:A7) not ROWS(A$7:A16).

    A slightly shorter version that might be easier to manage when adding extra criteria, as long as...
  22. Thread: Formula

    by jasonb75
    Replies
    1
    Views
    72

    Re: Formula

    You don't need a vlookup formula for that

    =ISERROR(MATCH(F2,Sheet1!$A:$A,0))
  23. Re: Listing holidays separately and finding values in aggregate

    That's not quite how the forum works.

    An attachment should only be needed to clarify details on a complex question, your post should contain enough information to explain what you need without the...
  24. Re: Name manager - adding data manually as reference

    It might work if you set the the 'refers to ' range as an array constant, I haven't tried this so no guarantee. It looks like you're trying to make something simple more complicated than it needs to...
  25. Re: Listing holidays separately and finding values in aggregate

    An alternative to enter into C2 and fill down to 10, this one will pick up a holiday or weekend in A2 rather than assuming it is always a working day. It is also error trapped to stop at the last...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4