Search:

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

Page 1 of 6 1 2 3 4

Search: Search took 0.18 seconds.

  1. Replies
    10
    Views
    170

    Re: VBA- Excel formula not working in vba

    Your method is not going to work for a criteria of greater than, try using the formula

    =SUMPRODUCT(COUNTIFS($A$1:$A$36,$E$2:$E$4,$B$1:$B$36,TRANSPOSE($F$2:$F$4),$C$1:$C$36,">="&$G$2))

    You can...
  2. Replies
    5
    Views
    128

    Re: Rounding while still seeing ".0"

    You can drop the decimal in the text format, omitting the need for the round function,

    ="F/"&TEXT(A1,"0.\0")&"//"

    Or even shorter

    =TEXT(A1,"F\/0.\0\/\/")

    You could also use the format in...
  3. Replies
    7
    Views
    167

    Re: Modified Rank function?

    Repeating my previous question which you appear to have overlooked, Do you really need a udf?

    You can do what you want with native excel functions, there is no point in trying to re-invent the...
  4. Replies
    10
    Views
    170

    Re: VBA- Excel formula not working in vba

    I don't think that a vba array is valid as an array constant, try dropping the range address into the formula instead.


    Dim rng1 As Range, rng2 As Range
    Set rng1 = Range("C2:D3")
    Set rng2 =...
  5. Replies
    7
    Views
    167

    Re: Modified Rank function?

    Do you really need a udf for this?

    For ascending order,

    Original logic =COUNTIFS($A$2:$A$5,"<"&A2)+1
    New logic =COUNTIFS($A$2:$A$5,"<="&A2)

    change < to > for descending.

    edit:- for the...
  6. Replies
    10
    Views
    170

    Re: VBA- Excel formula not working in vba

    Your mistake is using Range(...) within the formula, as you're only using vba to enter the formula into the worksheet you need to write it as a formula, not as vba. Apart from that, the rest looks...
  7. Re: SUMIFS with INDEX MATCH and greater than?

    I think that I have it right this time, Sumproduct will be easier to work with than sumifs but it is less efficient, if this is too slow in your real file then we can look at alternatives to improve...
  8. Re: SUMIFS with INDEX MATCH and greater than?

    I think I get the idea of what you're asking but just to be sure, could you post your example again, but this time type in the answers that you want in C2:C8 (if you want additional results in the...
  9. Re: SUMIFS with INDEX MATCH and greater than?

    Perhaps in C2 and fill down.

    =IFERROR(SUM(INDEX($C$11:$I$15,MATCH($A2,$A$11:$A$15,0),0)),0)
  10. Replies
    14
    Views
    277

    Re: INDEX/MATCH Lookup Assistance

    In a formula "1" is text, 1 is a number. In a cell that just conains a number with no formula, '1 is text.

    Anything resulting from an =TEXT(...) formula is naturally text, as is anything in a cell...
  11. Replies
    8
    Views
    237

    Re: Conditional formatting by merged cells

    The best way that I can describe Target is a varaible that is already defined as part of excel.

    I don't fully understand why it works, I just know how to use it.
  12. Replies
    14
    Views
    277

    Re: INDEX/MATCH Lookup Assistance

    Seeing the #REF ! error in there, I guess that you've been deleting rows / columns.

    I don't see any problems with your variation to the formula, but trimming it down a bit, I think that this...
  13. Replies
    14
    Views
    277

    Re: INDEX/MATCH Lookup Assistance

    The easiest way to do it would be to just have the start date for each sprint listed in column B, then use the formula from post #2

    You could use something like
    ...
  14. Replies
    8
    Views
    237

    Re: Conditional formatting by merged cells

    See if this works.

    Go to excel, right click the sheet tab (the name of the sheet at the bottom) for the sheet where you want the conditional formatting to be applied, then click 'View code'
    ...
  15. Re: How to use nested IF statement with a running total?

    Last attempt, H33 then drag right

    =MIN(IF(G34=0,ROUND((G33+H33)/60,0),0),4)
  16. Re: How to use nested IF statement with a running total?

    Not quite the same results as you shown in post #1 , but perhaps in H34

    =LOOKUP(IF(G34>0;H33;SUM(G33:H33));{0;50;70;150;210};{0;1;2;3;4})

    or
    ...
  17. Re: How to use nested IF statement with a running total?

    Pella's suggestion gives the same results as your original formula so I'm lost as to what you're trying to achieve.
  18. Re: Formatting Half of the Cell data in formula

    Given the nature of the worksheet change event, the OP might also need a little direction on how to make that work with any data that has already been entered into the sheet. ;)
  19. Re: How to use nested IF statement with a running total?

    Is this what you want?

    =LOOKUP(SUM($G33:H33);{0;50;70;150;210};{0;1;2;3;4})

    Your examples and explanations appear to contradict each other in places.
  20. Re: Formatting Half of the Cell data in formula

    Rick, when I said that I was thinking that a calculate event would format the existing results but not pick up any future changes, while a change event would do the opposite.

    Thought I would...
  21. Replies
    14
    Views
    277

    Re: INDEX/MATCH Lookup Assistance

    Ok, so in the case that the date in E3 is 9/24, which result should the formula show from the Sprints sheet, the content of A2, or that of A3?
  22. Re: Counting instances in workbook with both AND and OR statements

    See if this gives the expected results.

    =SUMPRODUCT(COUNTIFS($C$9:$C$74,"Indianapolis",$I$9:$I$74,"Database",OFFSET($S$9,0,COLUMN($S$9:$BS$9)-COLUMN($S$9),ROWS($B$9:$B$74),1),"Cancelled"))
  23. Replies
    14
    Views
    277

    Re: INDEX/MATCH Lookup Assistance

    Match can only be 1 row or column, you can work around it assuming that your dates are consecutive with B3 being the day after P2, B4 being the day after B3, etc. by using
    ...
  24. Re: Formatting Half of the Cell data in formula

    You can apply such formatting manually with a text string, but not with a formula.

    It might be possible to create a workaround with vba to replace the formula with the result as a text string,...
  25. Thread: mode

    by jasonb75
    Replies
    12
    Views
    183

    Re: mode

    Try the helper formula formatted as number / general instead of time, that should allow mode and average to work with negatives.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4