Using formulas in conditions (SUMIF, AVERAGEIF)

[Jan Kucera]

Hi,
I want to use formula in condition syntax, but I don't know how to
reference the value being tested.

Like:
SUMIF(A1:A10;">10 AND <20")

Or:
AVERAGEIF(A1:A10;"ISODD(...)")

Thanks for any ideas.

Jan

 

[Ragdyer]

For your <<< SUMIF(A1:A10;">10 AND <20") >>>

Try:
=Sumif(A1:A10,">10")-Sumif(A1:A10,">=20)

OR

=SUMPRODUCT((A1:A10>10)*(A1:A10<20)*A1:A10)

 

[Ragdyer]

Forgot a dbl quotes:

=Sumif(A1:A10,">10")-Sumif(A1:A10,">=20")

 

[T. Valko]

For the sum if, try this:

=SUMPRODUCT(--(A1:A10>=10),--(A1:A10<=20),A1:A10)

For the average if is odd, try this array formula** :

=AVERAGE(IF(MOD(A1:A10,2),A1:A10))

The ISODD/ISEVEN functions won't work on arrays. Also, I'm assuming the
values in question are integers and there are no text entries in the range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If you're using Excel 2007 there is an AVERAGEIF function but I don't use
Excel 2007 so I don't know the correct syntax. The above array formula will
work in any version.

 

[Jan Kucera]

Hi Ragdyer,
well, although this is nice math trick, is it impossible to combine
expressions?

Jan

 

[Jan Kucera]

Hello Biff,
thank you for your reply. Unfortunately, the first solution uses math
trick as well and the second one profits from being able to express the
ISODD function using modulo 2. I am actually looking for general way to pass
the tested value to a function, be it ISERR, ISBLANK, ISTEXT, WEEKDAY,
SEARCH or whatever....

Thanks, Jan

 

[Peo Sjoblom]

No it's not possible, thus the answers you got


--

Regards,

Peo Sjoblom

 

[Jan Kucera]

Fair enough, thank you for answering.

Jan

No it's not possible, thus the answers you got


--

Regards,

Peo Sjoblom

 

[Jan Kucera]

Okay,
is there a similar math trick for average of values in range?
Like AVERAGEIF(B1:B10, >=1.1.2000 AND < 1.2.2000, A1:A10)

Thanks, Jan

 

[Ragdyer]

Can you elaborate on the figures that you're using as your criteria?

If not typos, 1.1.2000 and 1.2.200 are what ... exactly?

 

[Ragdyer]

Don't you consider the expressions in the Sumproduct formula as a sort of
combination?<bg>

 

[Peo Sjoblom]

They are dates from another regional setting so one should be able to use
the DATE function as the criteria


--

Regards,

Peo Sjoblom

 

[Ragdyer]

I might have guessed that if he had at least used 2007 somewhere in there,
instead of making myself appear dense.<bg>

 

[Jan Kucera]

Okay, sorry guys, did not realized that I use another locale, that was just
for example to simplify the thing I'm trying to do.
They are 1/1/2000 and 2/1/2000.

Jan

 

[RagDyer]

One way would be to enter your date criteria in cells, that you can then
reference in the formula, so that you could change the dates without having
to change the actual formula itself.

Say C1 = start date
And C2 = end date, which in your case would be 31.1.2000:

=SUMPRODUCT((B1:B10>=C1)*(B1:B10<=C2)*A1:A10)/SUMPRODUCT((B1:B10>=C1)*(B1:B10<C2))

Another way could be this *array* formula:

=AVERAGE(IF((B1:B10>=C1)*(B1:B10<=C2),A1:A10))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

 

[Guest]

I don't have XL2007, but in XL2000 there is a conditional sum add-in
(Tools/Add-Ins/Conditional Sum Wizard). I've never played w/it, but from the
description in XL help, it appears that it will build the conditional sum
array formula for you. It may be a good starting point.