AverageIf Criteria value has comparison operator as value? 1

[lameid]

AVERAGEIF(OverallBO!$G$2:OverallBO!$G$714,$S29, OverallBO!V$2:V$714)

 

[lameid]

I fat fingered something and posted... more details below...

In this case the cell $S29 has the value "<20". My issue is I want to match that text and not values less than 20...

Now I'm going to go try and find where the formula is written in code.

 

[SkipVought]

Hi,

So how can you average TEXT? Sorry, you're averaging another range.

I assume that you mean that...
[tt]
"100" < "20"
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

You are stating the error case...

Apparently, in at least this one scenario wrapping the criteria in "<>" fixes it...

So for data

A B
<20 1
50 2
30 3

The formula

=AVERAGEIF(A1:A3,"<<20>",B1:B3)

Returns 1 (The average of the value 1 found at B1). I still have no clue what the delimiters on the value are doing. A friend found it on a blog somewhere... I've yet to find a description anywhere.

 

[lameid]

I didn't realize the site would eat my extra spaces, so the range again for readability...

A      B
<20    1
50     2
30     3

 

[lameid]

For clarity of the desired result for those who understand SQL that may have missed it...

For table X as range above as columns A, B

Select AVG(B)
FROM X
WHERE A = '<20'

 

[SkipVought]

I wouldn't beat a dead horse. I converted your table to a Structured Table for ease of documentation...
[tt]
=SUMPRODUCT((tLam[Avalue]="<20")*(tLam[Bvalue]))/SUMPRODUCT(--(tLam[Avalue]="<20"))
[/tt]

In other words...
[tt]
[Sum With Criteria]/[Count With Criteria]
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

=SUMPRODUCT((tLam[Avalue]="<20")*(tLam[Bvalue]))/SUMPRODUCT(--(tLam[Avalue]="<20"))

Unpacking that, I had to lookup structured references... I think I've seen them before but I'm not a heavy Excel user.

What is odd to me is the use of "--". I see that taking it out results in 0 for that Sumproduct. I also tried multiplying the expression by 1 inside to get count which works... So what exactly is "--" doing and any reason to use that instead of * 1? I am assuming it is some sort of calculation shortcut for Excel.

 

[SkipVought]

Yes, -- coerces a calculation, as does *1, wich produces the same result.

Bottom line (Strctured Table or not) SUMPRODUCT makes it possible to use "<20" directly as a value.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

This is my second response and second question.

Circling back to VBA, the recorded macro for adding a table looks like below.

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$B$5"), , xlYes).Name = "Table1"

Where MyRange is an Excel range object, I suspect my code would end up looking more like...

MyRange.parent.ListObjects.Add(xlSrcRange, MyRange, , xlYes).Name = "AverageTable"

And new problem, when I replace "<20" with a cell reference in the formula that contains that value, I receive a #Value! error.

Another thought I had was to replace "<20" with "Less Than 20". I think that is the same issue funamentally, it is not seen as text. I'm sure there is a worksheet function for that... I might get back to that later.

 

[SkipVought]

So what's the rationale for adding tables on the fly via VBA?

In 25 years of coding in VBA, I don't think I've ever added tables/queries programmatically. I've changed a connection string or two many times, but never had to add tables. The that would all be done at design time.

The "new problem"??? Where are the specifics? Saint George ain't gonna slay unspecified dragons.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

If D1 = "<20" Then the below doesn't work... Not really a new problem just not a working solution. D1 would be equivalent to S29 in the original posts.

=SUMPRODUCT((tLam[Avalue]=D1)*(tLam[Bvalue]))/SUMPRODUCT(--(tLam[Avalue]=D1))

 

[SkipVought]

Are you saying that D1 contains "<20"?

If so those QUOTES are your problem!

Either change the NumberFormat of D1 to TEXT and then enter <20 or enter '<20.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

When I revisited this, I could not replicate the problem... even by setting the format to general, so I do not know what was going on in my test but I thought I had tried formatting without luck.

For the example data plus a criteria field in D1, the old formula that didn't work for special criteria....

=AVERAGEIF(A1:A3,$D$1,B1:B3)

The revised formula that does work.

=SUMPRODUCT(((A1:A3)=$D$1)*(B1:B3))/SUMPRODUCT(--((A1:A3)=$D$1))

I was able to make similar replacements in code but that was of course more involved. Funny enough that was the easy part.

More involved is a procedure that conditionally fills in array and aggregate formulas. The result of asking the question, "I wonder if this might affect anything else in code?" I think it is manageable.

Right now it can deal with CountIf and AverageIf. I know there is also a SumIf. Is that all the aggregate If's to worry about substituting for criteria?

 

[SkipVought]

So is there a question in there?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lameid]

Just wondering if there are any worksheet functions other than SumIF, CountIF and AverageIf that might have the same issue. I have a procedure that fills in formulas and the function is passed so I need to trap them and special process them... At least when things are slow.

 

[SkipVought]

Its why I rarely use the aggregateIF formulas.

I much prefer SUMPRODUCT or other methods.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]