• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate AVERAGEIF when the value in adjacent cell matches one of the values in a table.

R

Richard Monreal

New Member
Hello,

First, a big THANK YOU to Chandoo and the ninjas for all of the great information on this site. While I was pretty good at Excel years ago, this site has improved my humility.

So, the problem we have is to find the average BILLED_AMT of a given Type of Project from a very long list of projects. While I can do this when the Type of Project and BILLED_AMT are given in the same table, see attached Budget by Type of Project, I am frustrated by my inability to do it when you have a...
  • Table of Job_Numbers and their associated BILLED_AMTs.
  • Table of Types_of_Jobs
    • Type "A" = odd number, single digit jobs
    • Type "B" = even number, single digit jobs
    • Type "C" = job numbers with two or more digits
And we want to calculate the average billed amount for all Type "A," "B,", and "C" jobs. Please see PlayWithMe.

Your help in pointing me in the right direction would be much appreciated.

P.S. So as not to embarrass myself, I deleted my attempt at array formulas from PlayWithMe and forced one into E4 just to show the desired result and NOT the desired formula.

Sincerely yours,
Richard Monreal
 

Attachments

Somendra Misra said:
Hi Richard,

Don't know what file to work on, but see the attached file (Yellow Cells). Verify the results.

Regards,
Click to expand...

Dear Somendra,

Thank you!

I wish I was as good at anything in life as you are at Excel. :)

While it will take me a little while to fully understand the formulas, they deliver just what we need! I will perserve to understand. Unfortunately had I tried 20*20 times I would likely have failed to come up with your efficient and effective solution.

P.S. Sorry my post was not clearer. The only reason for posting the other sheet was to let you know where I started. Being my first post I wanted to follow the rules as best I could.

Thank you again,
Richard Monreal
 
@Richard Monreal

Firstly, don't be sorry. Your post was very much clear. After posting my comment I realise that the second file might be to get an idea of what you want, and I checked it again, and I was right. You wanted to give an idea about your logic.

Secondly, thanks for your kind words, I am just a starter in Excel.

And lastly, about the logic of the formulas. There are three logics in my formulas:

1. Check for single digit ODD...So odd numbers are number which when divided by 2 leaves remainder as 1, so I checked that first than any number is single digit when it gives 0 as integer portion when divided by 10. So I checked these condition in IF statement and get the B column value for average, min & max.

2. Check for single digit EVEN...so any number is even which gives 0 as remainder when divided by 2, for single digit logic goes as above and rest all follows as above.

3. Any number which is 2 or more digits, so this is simplest, divided the number by 10 the remainder will always be either equal to 0 or more than that.

Now we will get some FALSE in above formulas, and almost all aggregate formulas like sum,average, min, max, large, small and more tends to ignore text and logic values.

Hope this will help in understanding the formulas.

Regards,
 
Hello All,

I am sorry to say that the solution provided does not meet our needs.

You see, the solution works perfectly where the made up job numbers are grouped as shown:
  • Type "A" = odd number, single digit jobs
  • Type "B" = even number, single digit jobs
  • Type "C" = job numbers with two or more digits
But, in the real world the jobs in Type "A" are 1783, 1784, 1978, and 2175, it will not work. The PlayWithMe file was just so that I could play with the formulas before I got the real data.

In short, the question is that if I populate column A in the Types_of_Jobs table with the real world JOB_NUMBERs, what formula would add the BILLED_AMTs where the JOB_NUMBER from the table (A2:B21) matches any of the values in "A" (A25:A29). Ideally, we could enter as many JOB_NUMBERs in a column as we need

I trust this is clear. The PlayWithMe file is just an example and I made the numbers easy for me to work with, but in the real world things are not so easy.

Best regards,
Richard Monreal
 

Attachments

Hell again Somendra,

You have succeeded once again. I had the exact same formula without the TRANSPOSE. It seemed to me that TRANSPOSE was a silly function for why would I want to turn a column into a row. But, alas I was wrong. It certainly did the trick here.

Thanks you again! (and I mean it again!)

It is hard to believe people with the knowledge you have are able to make the time to share it with others. I wish you nothing but the best!

Richard Monreal

THE END for now.
 
Welcome back anytime & thanks for your kind words.

Another option can be:

=AVERAGE(IF(ISNUMBER(MATCH($A$2:$A$21,$A$25:$A$34,0)),$B$2:$B$21))


Regards,
 
Last edited:
Somendra,

Once again I am grateful to you for providing me a way to solve our problem. So, while I will not be back again for this problem, I will come back if I fail 20 times to solve our next problem.

Thanks!
Richard Monreal
 
You must log in or register to reply here.
Back
Top