Excel Formula multiple Index Match and Average the result

0
votes
  1. I have two index match formulas looking at another excel tab pivot data

INDEX(MATCH())+INDEX(MATCH())

  1. Both formulas have IFERROR = 0

IFERROR(INDEX(MATCH()))+IFERROR(INDEX(MATCH()))

  1. And overall an IFERROR to return 0

IFERROR(IFERROR(INDEX(MATCH()))+IFERROR(INDEX(MATCH())))

Above works OK.

I now need to average so if the first index match return 30 and the second index match return 20 I need (30+20)=50/2 = 25

If the first index match return 0 and the second index match return 40 I need (0+40)=40/1 = 40 and vice versa

Is there a way to do this which doesn't involve IF(AND(, can AVERAGEIF or IFS be used or something else to keep it simple.

Example of Data Table being used

enter image description here

Formula

enter image description here

Thanks Gridlock but your example is missing last match in top formula e.g. if you had this

enter image description here

put formula in cell G3 =INDEX($M$3:$O$8,MATCH(G$1&$D3,$L$3:$L$8,0),MATCH($F3,$M$2:$O$2,0))

1
excelexcel-formula
thinking about it wonder I guess I could do a count something like this ? IFERROR(IFERROR(INDEX(MATCH()))+IFERROR(INDEX(MATCH()))) / COUNT(INDEX(MATCH()))+COUNT(INDEX(MATCH())) – Roger Clerkwell
I think you should upload an image that shows your data because there may be a better calculation formula for it. – Dang D. Khanh
Hi, have added a couple of images, hope they help – Roger Clerkwell
Hi, you say 20 and 10 but i see 30 and 20. Am I misunderstanding? – Dang D. Khanh
Sorry GridLock your right the first text I wrote was an example I have changed text to match image – Roger Clerkwell

1 Answers

0
votes

If the conditions are separate and unrelated, try this function:

=AVERAGE(SUMIF($L$3:$L$7,G1&F3&D3,$M$3:$M$7),SUMIF($L$3:$L$7,G1&F3&E3,$M$3:$M$7))

enter image description here

but I see you have general yearly conditions, so I'll take 2001 as a condition,so let's combination with wildcard in averageif:

=AVERAGEIF($L$3:$L$7,G1&"*",$M$3:$M$7)

enter image description here

Update:--------------------------------------------------------------------------

Hope it works for you!

=SUMPRODUCT(COUNTIF(G$1,LEFT($L$3:$L$6,4))*IF(INDEX($M$3:$O$6,,MATCH($F3,$M$2:$O$2,0))=0,OFFSET(INDEX($M$3:$O$6,,MATCH($F3,$M$2:$O$2,0)),1,),INDEX($M$3:$O$6,,MATCH($F3,$M$2:$O$2,0))))/2

in Excel>=2019

=AVERAGEIFs(INDEX($M$3:$O$7,,MATCH($F3,$M$2:$O$2,0)),$L$3:$L$7,G$1&"*",INDEX($M$3:$O$7,,MATCH($F3,$M$2:$O$2,0)),"<>0")

enter image description here