| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel needs an AVERAGEIF function similar to SUMIF. Please! | Excel Discussion (Misc queries) | |||
| SUMIF conditions | Excel Worksheet Functions | |||
| AVERAGEIF AND SUMIF AND COUNTIF | Excel Worksheet Functions | |||
| EXCEL -- want to do have ''averageif'' - like sumif function - H. | Excel Worksheet Functions | |||
| SUMIF with two conditions ? | Excel Discussion (Misc queries) | |||
Hybrid Mode
