How to take the highest value from below the average of a range

[helpneeded14]

Hi everyone,

I'm coming across a problem for a greater issue and was hoping someone could help with the first step. I'm trying to put together a staffing and sizing chart (the big problem, that I may ask for help with at a later date) but have a question for the staffing component.

My issue is as follows:

I have the monthly volume of clients entering our office and would like Excel to automatically calculate the highest value that is below the average of the same range. Basically, I'd like to find out the amount of staff we would need in the slow season and how many in the peak (using the MAX formula in the peak). I figure that if I take the average volume for the entire year, then anything below this average is the slow season and anything above is the peak.

Let's say that the volumes are as follows:


January - 1000
February - 1100
March - 900
April - 1300
May - 1800
June - 800
July - 700
August - 1800
September - 1700
October - 1200
November - 900
December - 600

The average here is 1150, the highest value below the average is 1100, so I want Excel to find this number to which I can divide it by the working days of the month and divide it again by the staff's capacity in a day. Is there a formula in Excel that can help automatically calculate the above?

If there is a logic gap here as well, I'm very open to ideas!!

From there, I hope to then plug in some estimates on how big the space needs to be with that type of volume. I might be back asking for how to best do this on a later post but hopefully with the above it will help me get to the next step!

Thanks very much everyone in advance!

 

[pgc01]

Hi
Welcome to the board

Try this array formula:

=MAX(IF(B1:B12<AVERAGE(B1:B12),B1:B12))<average(b1:b12),b1:b12))


This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.</average(b1:b12),b1:b12))

 

[helpneeded14]

Excellent! Worked like a charm! Thank you so much!!

 

[pgc01]

You're welcome.