# Search:

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

Page 1 of 6 1

1. ## Thread: VBA- Excel formula not working in vba

by jasonb75
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. ## Thread: Rounding while still seeing ".0"

by jasonb75
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. ## Thread: Modified Rank function?

by jasonb75
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. ## Thread: VBA- Excel formula not working in vba

by jasonb75
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. ## Thread: Modified Rank function?

by jasonb75
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. ## Thread: VBA- Excel formula not working in vba

by jasonb75
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. ## Thread: SUMIFS with INDEX MATCH and greater than?

by jasonb75
Replies
7
Views
152

### 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. ## Thread: SUMIFS with INDEX MATCH and greater than?

by jasonb75
Replies
7
Views
152

### 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. ## Thread: SUMIFS with INDEX MATCH and greater than?

by jasonb75
Replies
7
Views
152

### 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. ## Thread: INDEX/MATCH Lookup Assistance

by jasonb75
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. ## Thread: Conditional formatting by merged cells

by jasonb75
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. ## Thread: INDEX/MATCH Lookup Assistance

by jasonb75
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. ## Thread: INDEX/MATCH Lookup Assistance

by jasonb75
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. ## Thread: Conditional formatting by merged cells

by jasonb75
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. ## Thread: How to use nested IF statement with a running total?

by jasonb75
Replies
10
Views
270

### 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. ## Thread: How to use nested IF statement with a running total?

by jasonb75
Replies
10
Views
270

### 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. ## Thread: How to use nested IF statement with a running total?

by jasonb75
Replies
10
Views
270

### 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. ## Thread: Formatting Half of the Cell data in formula

by jasonb75
Replies
10
Views
133

### 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. ## Thread: How to use nested IF statement with a running total?

by jasonb75
Replies
10
Views
270

### 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})

20. ## Thread: Formatting Half of the Cell data in formula

by jasonb75
Replies
10
Views
133

### 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. ## Thread: INDEX/MATCH Lookup Assistance

by jasonb75
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. ## Thread: Counting instances in workbook with both AND and OR statements

by jasonb75
Replies
3
Views
89

### 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. ## Thread: INDEX/MATCH Lookup Assistance

by jasonb75
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. ## Thread: Formatting Half of the Cell data in formula

by jasonb75
Replies
10
Views
133

### 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,...