Search:

Type: Posts; User: a massive zebra; Keyword(s):

Page 1 of 4 1 2 3 4

Search: Search took 0.01 seconds.

  1. Replies
    2
    Views
    252

    Re: How write formula contains words

    Perhaps:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(KeywordTable,E2))),"Alert","Batch")

    Where KeywordTable is a named range containing all keywords.
  2. Replies
    0
    Views
    121

    Invisible Functions

    I understand the DATEDIF function can be used in excel even though it does not appear on the list of inbuilt functions. Are any other functions available in excel that do not appear in the formula...
  3. Re: Look Up Help Required with newest date

    Try this:

    =VLOOKUP(MAX(IF(Sheet2!$G$2:$G$25="Priory",Sheet2!$C$2:$C$25))&"Priory",CHOOSE({1,2},Sheet2!$C$2:$C$25&Sheet2!$G$2:$G$25,Sheet2!$H$2:$H$25),2,0)

    Enter with CTRL+SHFT+ENTR, not just...
  4. Re: Drag down same value at multiple points in a column

    You could enter this formula into cell J2 and copy down across to column L and down the to the final row of data:

    =IF($E2=$E1,J1,B2)
  5. Re: return a number in place of a text string

    Hi Aladin,

    Is there any benefit to using +0 rather than the VALUE function?

    Thanks
  6. Re: return a number in place of a text string

    Replace your formula with:

    =VALUE(20&LEFT(A1,2))

    or

    =(20&LEFT(A1,2))*1
  7. Replies
    5
    Views
    218

    Re: Max Value IF

    This also seems to work:

    =SUMPRODUCT(MAX((A$2:A$2001=A2)*B$2:B$2001))

    It is arguably a superior formula as it can be entered normally and probably calculates slightly faster.
  8. Replies
    5
    Views
    218

    Re: Max Value IF

    Try this, entered into cell C2 and copied down:

    =MAX(IF($A$2:$A$2001=A2,$B$2:$B$2001))

    Enter with CTRL+SHTF+ENTR, not just enter.
  9. Re: Formula to Count Column items until next blank cell

    Glad to help. :-)
  10. Re: Formula to Count Column items until next blank cell

    Try this:

    =COUNTIF(INDIRECT(ADDRESS(ROW()+1,3)&":"&ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C2:C200))),3)),"Y")

    Entered with CTRL+SHFT+ENTR
  11. Re: Formula to Count Column items until next blank cell

    Replace: =COUNTA(INDIRECT(C2&:&ADDRESS(MATCH(TRUE,INDEX((A3:A200<>0),0),0),3),FALSE),"Y")

    with: =ADDRESS(MIN(IF(ISBLANK(C2:C200),ROW(C1:C200))),3)

    Entered with CTRL+SHFT+ENTR, not just enter.
    ...
  12. Replies
    2
    Views
    302

    Re: SUMPRODUCT with VLOOKUP

    Try this:

    =SUMPRODUCT(IF(A2:A4={"Item1","Item2","Item3"},B2:B4),IF(A2:A4={"Item1","Item2","Item3"},C2:C4))/SUM(SUMIF(A2:A4,{"Item1","Item2","Item3"},B2:B4))

    Entered with CTRL+SHFT+ENTR, not...
  13. Re: Averaging or Counting a number of cells in a column

    Rick,

    This formula could be copied down:

    =AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(A2:Y2<>"",COLUMN(A2:Y2)),8))&":"&ADDRESS(2,COLUMN()-1)))
  14. Re: Averaging or Counting a number of cells in a column

    Wouldn't this be simpler?

    =AVERAGE(INDIRECT(ADDRESS(ROW(),LARGE(IF(A2:Y2<>"",COLUMN(A2:Y2)),8))&":Y2"))

    Entered into cell Z2 using CTRL+SHFT+ENTR, not just enter.
  15. Re: Check if data is in lower case or non-proper case

    Enter into C5 and copy down:

    =IF(OR(LEFT(B5,6)="_rfsti",LEFT(B5,6)="_rfeti"),IF(ISERROR(FIND(MID(B5,2,1),LOWER(MID(B5,2,1)))),"","First letter should be...
  16. Re: SUMPRODUCT with non continuous ranges

    Great, that worked perfectly.

    Thanks Aladin.
  17. SUMPRODUCT with non continuous ranges

    Hi,

    I would like to multiply the values in cells F22, F39 and F48 by the corresponding values in column J. I thought the following formula should work, but it is not being accepted by excel.
    ...
  18. Replies
    1
    Views
    30,678

    Re: ModelOff 2014 - starts 25th October!

    I've qualified for round 2! :)

    Is round 2 the final round before the world finals? Or is there a round 3 and/or national finals?

    Also, I ran out of time before submitting my workbook for the...
  19. Replies
    9
    Views
    224

    Re: Countifs / sumproduct...

    Thanks very much for the explanation. :)
  20. Re: Is there anything faster than countif?

    COUNTIF is a quick function. I would guess you must have a lot of other formulae in the spreadsheet. Try calculating that sheet only: Formulas > Calculation > Calculate Sheet
  21. Replies
    9
    Views
    224

    Re: Countifs / sumproduct...

    Hi Aladin. Can you explain why the +0 is necessary? Is it to ensure the data is recognised as a number? If so why not use the VALUE function? Always appreciate your input. Thanks.
  22. Replies
    5
    Views
    144

    Re: SUM with Critera in 3 columns.

    If you are using excel 2007 or later, you could just use COUNTIFS: =COUNTIFS('TASK DETAILS'!D1:D5000,2014,'TASK DETAILS'!E1:E5000,"Jan",'TASK DETAILS'!AT1:AT5000,"Y")
  23. Replies
    5
    Views
    144

    Re: SUM with Critera in 3 columns.

    I think you need consistent row ranges, so either 1 to 1000 or 1 to 5000, but not a mixture of both. If that doesn't work, does this: =SUMPRODUCT(('TASK DETAILS'!D1:D5000="2014")*('TASK...
  24. Replies
    4
    Views
    144

    Re: Numbers only

    In E2: =IF(ISNUMBER(B2),B2,IF(ISNUMBER(C2),C2,D2))
  25. Re: Backwards compatibility to 97-03 for IFERROR array

    =IF(ISERROR(INDEX('Player Seasons'!$E:$E,SMALL(IF(FREQUENCY(IF('Player Seasons'!$D$2:$D$358="REG",MATCH('Player Seasons'!$E$2:$E$358,'Player Seasons'!$E$2:$E$358,0)),ROW('Player...
Results 1 to 25 of 87
Page 1 of 4 1 2 3 4