Averageif function with Average range subtracting columns

Hello!

I found a similar topic but I opened another one because I didn't really understand how to do this.

OK... so I have a spreadsheet with all these different dates and instead of having to create a column every time I want to subtract one from the other, I'd rather have the function do that for me (which it does). The problem is that it shows as invalid.

So for example I have

PO Create Date STA Date
3/18/2014 5/27/2014
3/18/2014 5/27/2014
3/18/2014 6/24/2014

I want my average if to subtract the 2 values instead of having to create a column to do that for me.

I hope you guys understand what I'm trying to do here!

So you want the average of column B minus Column A ?

Try an array formula entered with CTRL + SHIFT + ENTER
=AVERAGE(B2:B100-A2:A100)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Yes, in this case it would be an average if. Can I do the same thing if I use average if?

No, AverageIF won't be able to do it.
But you can still use an Array Formula Average(If

=AVERAGE(IF(A1:A100="criteria",C1:C00-B1:B100))

Still entered with CTRL + SHIFT + ENTER
That will give an average of C-B IF A="criteria"

Worked perfectly!!! Although I have to leave the header out. Since this is a tab that will have constant updates and we'll be adding more rows of info what should I do in terms of the range, A1:A9999999?

No, don't do that..

You should have a 'reasonable' expected upper limit to the # of records in the file.
Add 10% to that.

If you can't come up with a reasonable limit, look into using Dynamic Named ranges.
Or do
=(SUMIF(A:A,"criteria",C:C)-SUMIF(A:A,"Criteria",B:B))/COUNTIF(A:A,"criteria")

The count/sum/average IF functions are smart enough to only process the actual used range.
But an array formula Average(IF is not, it will evaluate every single row. = Poor Performance.

Although, you already touched on the best solution yourself.

Originally Posted by miguelcristovao

instead of having to create a column to do that for me.

That really is the best/most efficient solution.
To put =C2-B2 in column D then use that in the average.


I don't understand why so many people are against using additional columns for calculations.
It's not a bad thing.
Excle gives you over 16000 columns, might as well use a few of them here and there.

That works way better actually!!! Thank you so much

Yes I know, but what happens is that I'm trying to create a system where someone else just has to come to this tab and copy paste it without having the need to create the column all over again. This function captures the info all at once which is perfect

Originally Posted by miguelcristovao

without having the need to create the column all over again.

Don't they already have to modify it a little by putting in the formula? They can't put in 2 formulas?

I have a tab where you throw all the info in and another tab that has all the formulas. Basically a tab that works as a table and another that works like a query

Then I don't understand why you can't have the helper column of =C2-B2 then?

Supposedly it would have to reference the sheet that has the data
=Data!C2-Data!B2

I see what you're saying but I'd need multiple columns with that info and then a simple AVG function would do the same thing. I'm just trying to make this more effecient... with a more complicated formula I guess.

Any chance you can help me with with Listing Unique values? I can't make this Index, Match, Countif thing work!

I would submit that using the helper column IS more efficient then combining the calcs into 1 formula.

Also, I find in many cases that it's helpful to have the 'intermediate' calculation in it's own cell for future reference.
You never know if a couple weeks later, you have a requirement to see what C-B actually was.
Then you'll be making another formula to show that, and now you have 2 formulas doing the same work twice.


I'd start another thread for listing unique values.
It's not my specialty, but there are several others who are quite good at it.

OK thanks for the heads up. I'll take on your advice and actually do that, totally forgot to see it from that prisma! Once again, thank you

You're welcome.