Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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

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

OR

=SUMPRODUCT((A1:A1010)*(A1:A10<20)*A1:A10)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jan Kucera" wrote in message
...
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


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default Using formulas in conditions (SUMIF, AVERAGEIF)

Forgot a dbl quotes:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
For your <<< SUMIF(A1:A10;"10 AND <20")

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

OR

=SUMPRODUCT((A1:A1010)*(A1:A10<20)*A1:A10)

--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-

"Jan Kucera" wrote in message
...
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



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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

Jan


"Ragdyer" wrote in message
...
Forgot a dbl quotes:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
For your <<< SUMIF(A1:A10;"10 AND <20")

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

OR

=SUMPRODUCT((A1:A1010)*(A1:A10<20)*A1:A10)

--
HTH,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------

-

"Jan Kucera" wrote in message
...
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





  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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



  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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


--

Regards,

Peo Sjoblom





"Jan Kucera" wrote in message
...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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





  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using formulas in conditions (SUMIF, AVERAGEIF)

Fair enough, thank you for answering.

Jan

"Peo Sjoblom" wrote in message
...
No it's not possible, thus the answers you got


--

Regards,

Peo Sjoblom





"Jan Kucera" wrote in message
...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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




  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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



  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jan Kucera" wrote in message
...
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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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






  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jan Kucera" wrote in message
...
Hi Ragdyer,
well, although this is nice math trick, is it impossible to combine
expressions?

Jan


"Ragdyer" wrote in message
...
Forgot a dbl quotes:

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

--
Regards,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"Ragdyer" wrote in message
...
For your <<< SUMIF(A1:A10;"10 AND <20")

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

OR

=SUMPRODUCT((A1:A1010)*(A1:A10<20)*A1:A10)

--
HTH,

RD


-------------------------------------------------------------------------

-
-
Please keep all correspondence within the NewsGroup, so all may benefit

!

-------------------------------------------------------------------------

-
-

"Jan Kucera" wrote in message
...
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




  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,268
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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


--

Regards,

Peo Sjoblom






"Ragdyer" wrote in message
...
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?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jan Kucera" wrote in message
...
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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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





  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
They are dates from another regional setting so one should be able to use
the DATE function as the criteria


--

Regards,

Peo Sjoblom






"Ragdyer" wrote in message
...
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?

--
Regards,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"Jan Kucera" wrote in message
...
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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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






  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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" wrote in message
...
I might have guessed that if he had at least used 2007 somewhere in there,
instead of making myself appear dense.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
They are dates from another regional setting so one should be able to use
the DATE function as the criteria


--

Regards,

Peo Sjoblom






"Ragdyer" wrote in message
...
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?

--
Regards,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-
"Jan Kucera" wrote in message
...
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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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






  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jan Kucera" wrote in message
...
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" wrote in message
...
I might have guessed that if he had at least used 2007 somewhere in
there,
instead of making myself appear dense.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
They are dates from another regional setting so one should be able to
use
the DATE function as the criteria


--

Regards,

Peo Sjoblom






"Ragdyer" wrote in message
...
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?

--
Regards,

RD


--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may
benefit

!

--------------------------------------------------------------------------

-
"Jan Kucera" wrote in message
...
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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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










  #16   Report Post  
Posted to microsoft.public.excel.newusers
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Using formulas in conditions (SUMIF, AVERAGEIF)

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.

"Jan Kucera" wrote:

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



"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Jan Kucera" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel needs an AVERAGEIF function similar to SUMIF. Please! Roseland Excel Discussion (Misc queries) 1 May 16th 06 02:22 PM
SUMIF conditions AAMIFC Excel Worksheet Functions 3 March 15th 06 04:24 AM
AVERAGEIF AND SUMIF AND COUNTIF kathi Excel Worksheet Functions 15 February 22nd 06 03:14 PM
EXCEL -- want to do have ''averageif'' - like sumif function - H. Glenda Excel Worksheet Functions 1 April 5th 05 08:58 PM
SUMIF with two conditions ? Mestrella31 Excel Discussion (Misc queries) 9 December 22nd 04 01:09 AM


All times are GMT +1. The time now is 08:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"