AVERAGEIFS Formula With A Range

1. AVERAGEIFS Formula With A Range

Hello,

I've been trying to use the averageifs formula to determine the aveage FTE needed for 1st, 2nd, 3rd, 4th and years beyond for different client types. Column A displays the client type, Column B the number of years live and Column C is I've come up with the following formula but I can't seem to get it to work right. Was hoping someone might be able to point me in the right direction to get it correct? I also apologize in advance if a similar question has already been asked, but I searched through the posts and was unable to find anything that helped with my issue.

=AVERAGEIFS(C2:C16,A2:A16,"C",B2:B16,">=0",B2:B16,"<1")

Client Years FTE
C 3.61 2.49
P - 0.00
P 4.61 0.16
M 0.94 0.00
M 3.28 0.57
P 0.94 0.00
C - 0.06
C 3.61 0.51
C 3.45 0.29
P 3.12 4.18
C 4.12 1.58
C 5.37 1.25
M 4.12 0.63
P 7.29 0.36
M 2.61 1.27

2. Re: AVERAGEIFS Formula With A Range

so that formula is saying

=AVERAGEIFS(C2:C16,A2:A16,"C",B2:B16,">=0",B2:B16,"<1")

Where C exists in column A
AND
Where B is between 0 and less than 1 (not =1)
for the rows where those two conditions are TRUE
work out the average for those rows only

is that what you need ?

C 3.61 2.49

C - 0.06
C 3.61 0.51
C 3.45 0.29
C 4.12 1.58
C 5.37 1.25

But None of column B are between 0 and 1
unless - is formatting for 0
in which case the result is just that 1 row

so 0.06

3. Re: AVERAGEIFS Formula With A Range

Stupid mistake on my part, thanks for pointing it out. Everything is working fine now!

4. Re: AVERAGEIFS Formula With A Range

excellent - glad you worked it out - often happens to me, i look at something for hours and then when i go over it with a colleague they spot something straight away

thanks for the rep

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1