1

I'm trying to get an AVERAGEIF a few conditions are met (from another sheet: SHEET1). If the conditions aren't met, then I want a different AVERAGEIFS formula to go into effect.

Not sure how to combine these two arguments. They work fine on their own, but I want Formula 2 to work if Formula 1's conditions aren't met.

Should I be using SUMPRODUCT or nest these formulas together? Would appreciate some help combining these.

Formula 1

=AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3)

  • where range to average: R3:R105

    A3:A105 = x

    S3:S105 contains EWS

    D3:D105 = SAV

    Q3:Q105 date within past 3 years <-- reason Formula 1 fails is because this last condition is not met

.

If cell doesn’t meet Formula 1's conditions, then activate Formula 2:

.

Formula 2

=AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!B3:B105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3)

  • where range to average: R3:R105 A3:A105 = x

    S3:S105 contains EWS

    D3:D105 = SAV

    B3:B105 date within past 3 years

I tried a few things, but they ended up with errors. Is there a way to use the IFERROR function? I tried to substitute Formula 2 into value_if_error, but got another error.

Community
  • 1
  • 1
que syrah sarah
  • 231
  • 1
  • 4
  • 14

2 Answers2

1

So try:

=iferror(your-ave-1,iferror(your-ave2,"check"))

If it returns “check” then both your average calcs have not worked. Quotes (") now corrected- a font on the smartphone...

I have not checked your average calcs as I am using a smartphone..

EDIT

updated the formula to include the correct "

=iferror(Formula 1,iferror(Formula 2,"check"))

substituted formulas:

=iferror(AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3),iferror(AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!P3:P107, "<>"), "check"))

Solar Mike
  • 7,156
  • 4
  • 17
  • 32
  • thanks! i tried your formula, substituting my formulas in, but i got an error saying i'm missing a parenthesis. i tried adding one more at the end, but still the same error... here is the formula: `=iferror(AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3),iferror(AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1!B3:B105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3),”check”)))` – que syrah sarah Aug 19 '18 at 01:22
  • i modified the formula a bit. the issue was the formula you gave had `""` going backwards(?) which apparently gsheets is finnicky about lol when i use the modified formula below, i get **check** instead of the avg of R3:R107. i understand `IFERROR` formula gives `check` instead of `ERROR` but why is it giving an error? there should be an avg. i can manually avg the values when i filter/sort, but i'd like it to be dynamic. formula: `=iferror(your-ave-1,iferror(your-ave2,"check")) – que syrah sarah Aug 19 '18 at 01:43
  • so i figured out that my **Formula 2** was slightly off. I changed parameters for last condition. It now gives me an average correctly!! my new formula `=iferror(AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3),iferror(AVERAGEIFS('SHEET1'!R3:R105, 'SHEET1'!A3:A105, "x", 'SHEET1'!S3:S105, "*EWS*", 'SHEET1'!D3:D105, "SAV", 'SHEET1'!P3:P107, "<>"), "check"))` Thank you very much for your help! – que syrah sarah Aug 19 '18 at 01:53
  • Excellent -glad it works for you. And the quotes thing is a font on the smartphone ... – Solar Mike Aug 19 '18 at 06:48
0

try:

=IFERROR(ArrayFormula(IF(SHEET1!Q3:Q105 > month(today())&"/"&day(today())&"/"&year(Today())-3,
                         AVERAGEIFS(SHEET1!R3:R105, SHEET1!A3:A105, "x", SHEET1!S3:S105, "*EWS*", SHEET1!D3:D105, "SAV", SHEET1!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3),
                         AVERAGEIFS(SHEET1!R3:R105, SHEET1!A3:A105, "x", SHEET1!S3:S105, "*EWS*", SHEET1!D3:D105, "SAV", SHEET1!B3:B105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3))),)

update:

=IFERROR(ArrayFormula (IF (SHEET1!Q3:Q105 > month(today())&"/"&day(today())&"/"&year(Today())-3,                       
AVERAGEIFS(SHEET1!R3:R105, SHEET1!A3:A105, "x", 
                           SHEET1!S3:S105, "*EWS*", 
                           SHEET1!D3:D105, "SAV", 
                           SHEET1!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3),                      
AVERAGEIFS(SHEET1!R3:R105, SHEET1!A3:A105, "x", 
                           SHEET1!S3:S105, "*EWS*", 
                           SHEET1!D3:D105, "SAV", 
                           SHEET1!B3:B105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3))),)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • thanks for this. i tried it out and kept getting an error, saying i was missing a parentheses – que syrah sarah Aug 19 '18 at 01:11
  • i added some and then changed the formula to: `=IFERROR(ArrayFormula(IF(SHEET1!Q3:Q105 > month(today())&"/"&day(today())&"/"&year(Today())-3), AVERAGEIFS(SHEET1!R3:R105, SHEET1!A3:A105, "x", SHEET1!S3:S105, "*EWS*", SHEET1!D3:D105, SAV", SHEET1!Q3:Q105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3), AVERAGEIFS(SHEET1!R3:R105, SHEET1!A3:A105, "x", SHEET1!S3:S105, "*EWS*", SHEET1!D3:D105, "SAV", SHEET1!B3:B105, ">"&month(today())&"/"&day(today())&"/"&year(Today())-3)),)` unfortunately, it didnt work; gave a blank (even though should give #) – que syrah sarah Aug 19 '18 at 01:12
  • @quesyrahsarah yeah, your formula from comment was broken – player0 Aug 21 '18 at 08:57