Averageifs formula no longer working

[Marieme]

Hi
I am encountering a rather puzzling issue: I updated a file I created in Oct with November sales and 2 of my formulas are no longer working: "averageifs" and "index match" coupled with "max".
The file is the same format as before just added a new list, the formulas are exactly the same but I just get "0" everywhere except on the first line.

And yes just in case anybody is wondering I have put the "{}" signs for the array formula.
Can anybody help please?
Marieme

[Jonmo1]

And yes just in case anybody is wondering I have put the "{}" signs for the array formula.

What exactly do you mean by that? Did you type them in by hand?
and Why? Avererageifs and Index/Match formulas do not require {array entry}

Can you post a sample of the book?

[FDibbins]

Have you checked to make sure that he "values" are actually numbers and not text that just looks like a numner?
Test with =ISNUMBER() on a few of them - FALSE indicates text

[Marieme]

Hi FDibbins
That was my first check.
Jonmo1 no I didn't enter them manually but through ctl+shift+enter not for the averageifs but rather for the "index match max if" formula.

[Jonmo1]

Can you post a sample of the book?
Or at least show us the formulas?

[Marieme]

here is one on the formulas
($K3-$D3)*IFERROR(AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;Invoice_List_RTM_test_2606!$Y:$Y;CONCATENATE($B3;$C3);Invoice_List_RTM_test_2606!$F:$F;">="&$A3;Invoice_List_RTM_test_2606!$F:$F;"<="&Stock!$H3);0)][/COLOR]

Please Login or Register  to view this content.

[Marieme]

here is one on the formulas
($K3-$D3)*IFERROR(AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;Invoice_List_RTM_test_2606!$Y:$Y;CONCATENATE($B3;$C3);Invoice_List_RTM_test_2606!$F:$F;">="&$A3;Invoice_List_RTM_test_2606!$F:$F;"<="&Stock!$H3);0)][/COLOR]

Please Login or Register  to view this content.

[Marieme]

Jonmo1
I suspect it has to do with the "Invoice..." sheet because when I open the Oct file and use that same sheet as the reference sheet it works.
I would like to share a sample file with you but I'd would have to make a lot of changes since it has confidential info and I am afraid of corrupting the file rendering it useless for you.

[Marieme]

And the other formula causing trouble
=IFERROR(INDEX(Invoice_List_RTM_test_2606!$B:$B;MATCH(MAX(IF(Invoice_List_RTM_test_2606!$T:$T=Stock!$J3;IF(Invoice_List_RTM_test_2606!$F:$F<=Stock!$H3;Invoice_List_RTM_test_2606!$F:$F)));IF(Invoice_List_RTM_test_2606!$T:$T=Stock!$J3;Invoice_List_RTM_test_2606!$F:$F);0));0)*K3

Please Login or Register  to view this content.

[Jonmo1]

Possible causes for that formula (from post #7) to return 0
1) K3-D3 = 0

2) The result of the Averageifs function is an error (therefor trapped by IFERROR, and returning 0)
a) Possible cause for error in averageifs, Errors pre-existing in any of the referenced ranges
b) Or There are no numeric values in the Average Range corresponding to rows where the criteria are all met.
c) None of the rows met all of the criteria

3) The result of the Average just happens to be 0
a) Cause of this is there are both positive and negative numbers in the average range) corresponding to rows where the criteria are all met.
b) Or All numbers in average range are 0 corresponding to rows where the criteria is met


I'd say the most likely cause is either 2b or 2c


My first step for troubleshooting would be to remove the IFERROR, and the (K3-D3) parts, and test only the averageifs by itself.
If still 0, then you likely have 0's in the average range (or mix of positive and negative numbers and the average happens to be 0).
If you get Div/0 error, then you have either no numeric values in the average range, or none of the rows met the criteria.
If you get some other error like #N/A or #Value!, then that error likely exists in one of the other ranges referenced by the formula.

Start testing it with 1 criteria at a time.

Which of these return the correct results, which don't?
AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;Invoice_List_RTM_test_2606!$Y:$Y;CONCATENATE($B3;$C3))
AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;;Invoice_List_RTM_test_2606!$F:$F;">="&$A3)
AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;Invoice_List_RTM_test_2606!$F:$F;"<="&Stock!$H3)

Repeat each of the above, but use COUNTIF instead and remove the average range..

[FDibbins]

TYpe some ot the function results in manually, and see where it breaks down? Something like this...
INDEX(Invoice_List_RTM_test_2606!$B:$B;MATCH(10;Invoice_List_RTM_test_2606!$F:$F)
I used 10 for the MAX)IF() part, you would use something that works for you

edit: I think jonmo and myself are using the same approach

[Marieme]

Tried the step by step approach as suggested: taken separately there is no problem, the break down happens when I put them together. Apparently it is cause 2c of Jonmo options. What I don't understand however is this problem did not occur last month (this is a listing of all invoices made since January 2014).

[FDibbins]

OK, so you broke the big formula down into it's component parts, and each 1 gave a valid answer.

Now manually enter those answers into the "big" formula and see if it works. If not, then test to make sure than numbers being returns (and then used in a search) are actually numbers
Remember that concat returns text, not numbers
CONCATENATE($B3;$C3)

[Jonmo1]

I'd stop thinking about why it worked last month but not this month.
Ultimately at this point it doesn't matter, it doesn't narrow down the list of possible causes.
It's only giving you a headache.
Once the cause is discovered, the why will probably make itself known.

So each formula using 1 criteria resulted in expected values?

Now try doing 2 criterias..
You have a total of 3 criterias, so do 1 formula for 1 and 2, another for 1 and 3, and another for 2 and 3.
Does that help narrow it down?


Try using the AutoFilter for each criteria so you can actually see which rows do and do not meet each criteria...


Also, still in the back of my head..I noticed both formulas you posted from post #7 & #9 refer to K3...a commonality??
What does =$K3-$D3 return?

[Marieme]

Sorry FDibbins and Jonmo I should have specified that after doing each separately , I combined them as in Jonmo's last post and that's when I noticed that it is when criteria 3 is mixed with another than I get #Div/0 (into the "big" formula without the iferror).
K3 and D3 are volume.

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jonmo1]

Criteria 3 being...
Invoice_List_RTM_test_2606!$F:$F;"<="&Stock!$H3
??

But when you did that criteria by itself...
=AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;Invoice_List_RTM_test_2606!$F:$F;"<="&Stock!$H3)
You DID get a valid result? not an error?


OK, then it appears there is nothing actually wrong with the formula or the data.
It just so happens that NONE of the rows actually meet all the criteria this month..
And the formula is indeed returning the correct result of 0

[Marieme]

It just so happens that NONE of the rows actually meet all the criteria this month..

I do believe the formula is correct and there are rows that do meet the criteria; the problem is that even the previous months yield 0 everywhere (hence the reason I kept refering to the previous month).

[Jonmo1]

It's time to post a sample book.

[Marieme]

I think so. Lemme see how I can tweak my file without making a lot of mess

[Jonmo1]

There is one other thing I found odd about the formula, but I've ignored it because you said it worked as desired in previous months...
($K3-$D3)*IFERROR(AVERAGEIFS(Invoice_List_RTM_test_2606!$B:$B;Invoice_List_RTM_test_2606!$Y:$Y;CONCATENATE($B3;$C3);Invoice_List_RTM_test_2606!$F:$F;">="&$A3;Invoice_List_RTM_test_2606!$F:$F;"<="&Stock!$H3);0)

Criteria 2 compares column F to A3 without any sheet reference to A3, so it refers to the sheet the formula is on
Criteria 3 compares the same column F to Stock!H3.

It's peculiar that the same column would be compared against 2 different criterias from 2 different sheets.

It's quite possible that was intentional and is supposed to be that way.
it's just one thing that stands out...

[Marieme]

Ok here is a sample book.

[Jonmo1]

You might want to trim that down some more, and double check the formula after you 'cleaned' the sheet.
We really only need about 10 rows or so of formulas and reference data.

You have the formula checking column Y for CONCATENATE($B3;$C3)
But on the reference sheet, column Y is empty.

AFter looking, it seems you probably meant for that to be looking in column M
But, there are no values in column M that match the result of the concatenate..


I'm out of time for today, but I'll try to pick it up some more tomorrow.

[Marieme]

Hi Jonmo
Sorry I've been travelling all day. Just got to a place where I could check emails and stuff.
I'll look at the file asap; it's true that I did the sample late last night, tired and deleting stuff that I deemed irrelevant and didn't check the formulas after.

[Marieme]

Cleaned up version; my apologies for the mess!
And now it seems like I am getting values for my index match... function

[Jonmo1]

That looks like it's still the same file.
Formula on the Stock Sheet L2
=($J3-$D3)*IFERROR(AVERAGEIFS(Invoice_List_RTM_test_2606!$A:$A,Invoice_List_RTM_test_2606!$Y:$Y,CONCATENATE($B3,$C3),Invoice_List_RTM_test_2606!$F:$F,">="&$A3,Invoice_List_RTM_test_2606!$F:$F,"<="&Stock!$G3),0)

It's looking at column Y for matches to the concatenate function
But column Y on the Invoice sheet is empty.

It's looking for dates in column F that are between 2 given dates.
But there are no dates in column F of the Invoice sheet.

[Marieme]

ooooohhh looks like the lack of sleep is really catching up with me.
I re-attached last night's file, sorry about thet.
Here is the right one

[Jonmo1]

The problem is 2c as described in Post #10

ok, on the stock sheet, L3, Formula is
=($J3-$D3)*IFERROR(AVERAGEIFS(Invoice_List_RTM_test_2606!$A:$A,Invoice_List_RTM_test_2606!$M:$M,CONCATENATE($B3,$C3),Invoice_List_RTM_test_2606!$D:$D,">="&$A3,Invoice_List_RTM_test_2606!$D:$D,"<="&Stock!$G3),0)

Now that's searching column M from the Invoice sheet for matches to CONCATENATE($B3,$C3)
The result of CONCATENATE($B3,$C3) is "3306130cl RGB x 24"
The value "3306130cl RGB x 24" does not exist in column M of the Invoice sheet.
So the Averagifes formula returns Div/0! Error (because there are 0 rows that meet ALL the criteria).
Iferror traps that error and returns 0.
Formula returns 0.


Hope that helps.

[Marieme]

I do not get since the value "30cl RGB...3 does exist. Even if it doest exist for customer B3 on 31st July, there is value at an earlier date.
Furthermore column K which has the index match function is now returning values on the sample sheet but not on my original book.

[martindwilson]

as jonmo1 says where does that exist 3306130cl RGB x 24 when joined with customer number in column M? of Invoice_List_RTM_test_2606 column m only contains 30cl RGB x 24
column m of Invoice_List_RTM_test_2606 says concatenate as a header but there is no concatenation in there
surely column m of that sheet should be =C2&L2 or=concatenate(C2,L2)

[Jonmo1]

I do not get since the value "30cl RGB...3 does exist.

But like I said, the formula is NOT looking for "30cl RGB x 24"
It's looking for the result of CONCATENATE($B3,$C3)
And the result of that concatenate function is "3306130cl RGB x 24"
That value does not exist in column M of the Invoice Sheet.

As Martin pointed out, column M of the Invoice sheet probably used to contain a concatenate function as well, but it's not there in the book you posted.