Help with IF-function

**BirgerGov** · 10-25-2016, 07:33 AM

Hi!

I'm trying to use my Excel-document for staffing at a Call center. I have all the data I need, but I want column F (Agents) to automatically find a number that will give corresponding row in coumn F (Service level), let's say 80%.

How do I manage that? Quite new to Excel I'm afraid

Attached a sample file as well. It's the leaf that's called "staffing" I'm referring to. Hope I was clear enough in my explanation

**AliGW** · 10-25-2016, 07:35 AM

To attach a sample file:

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**BirgerGov** · 10-25-2016, 07:37 AM

Did that already

**AliGW** · 10-25-2016, 07:50 AM

Originally Posted by BirgerGov

Did that already

And you have amended your post, which originally said that you could not attach a file. I posted to help you with that.

**BirgerGov** · 10-25-2016, 07:58 AM

Hope all info, attachment and topic is correct now.

Looking forward to what must be, to you guys, a simple formula

**AliGW** · 10-25-2016, 08:02 AM

I have all the data I need, but I want column F (Agents) to automatically find a number that will give corresponding row in coumn F (Service level), let's say 80%.

What exactly does this mean? What are you expecting to see in column F (which currently contains a simple formula)?

**BirgerGov** · 10-25-2016, 08:05 AM

Well I want a formula that gives me exactly 80% Service level (column N), that column is based on the number of agents in column F.

**AliGW** · 10-25-2016, 08:07 AM

OK - so where do you want this information to appear? How would you manually calculate your expected outcome? What is obvious to you is not, yet, obvious to me - sorry!

**BirgerGov** · 10-25-2016, 08:13 AM

Well I want Excel to test any number between let's say 5-35 (that's min-max staffing for any given interval) in Column F and find a number that will make the corresponding row in Column N (Service level) hit 80%.

My guess is that I have to use IF-functions but I'm just not seasoned enough in Excel to manage it.

**AliGW** · 10-25-2016, 08:15 AM

I'm really sorry - I just don't get it.

**BirgerGov** · 10-25-2016, 08:19 AM

Well if you change F 9 to let's say 10 the corresponding row in Column F will change to 47,9. ok? that means with 10 agents in that interval I will have a service-level of 47,9%.

I don't want to fill in the number of agents by hand, I want excel to find what number of agents will result in 80% service level.

**Ash_Maverick** · 10-25-2016, 08:22 AM

What is in Column C ?

**BirgerGov** · 10-25-2016, 08:22 AM

Column C is the amount of incoming calls per hour

**chullan88** · 10-25-2016, 08:23 AM

Originally Posted by BirgerGov

Well if you change F 9 to let's say 10 the corresponding row in Column F will change to 47,9. ok? that means with 10 agents in that interval I will have a service-level of 47,9%.

I don't want to fill in the number of agents by hand, I want excel to find what number of agents will result in 80% service level.

Try Goal Seek

**JohnTopley** · 10-25-2016, 08:28 AM

You will probably need VBA to loop through all rows column F trying values of F to give a value of 80% (in F9 = 11.8)

Is F to be integer numbers only?

**BirgerGov** · 10-25-2016, 08:28 AM

Im not good enough in Excel to do that I'm afraid, need something more if possible

**Ash_Maverick** · 10-25-2016, 08:30 AM

Where is the Answered calls per interval ?

**BirgerGov** · 10-25-2016, 08:37 AM

F don't have to be integer numbers, one decimal is fine, but I have to round up/down in the end anyways so integer numbers is preferred.

**BirgerGov** · 10-25-2016, 08:39 AM

Ash, I don't have answered calls per interval since this is a meant as a prognose, I have a different report for results and such. This is forecasting and staffing alone

**JohnTopley** · 10-25-2016, 08:42 AM

Try simple macro:

Please Login or Register  to view this content.

Manually change values in F and then Click RUN button.

**Olly** · 10-25-2016, 08:51 AM

Here's one possible method, using VBA to loop through each row, then trying to use Goal Seek to set the number of agents.

Note that your data is not solvable to 80% in all cases (in these cases, the original formula will remain in Column F).

Please Login or Register  to view this content.

**BirgerGov** · 10-25-2016, 08:54 AM

Holy hell that actually worked

**JohnTopley** · 10-25-2016, 08:56 AM

Which one??!!!

**BirgerGov** · 10-25-2016, 09:00 AM

Thank you all for taking your time to help me. I created a button and recorded this macro so the correct cells were selected

**Olly** · 10-25-2016, 09:04 AM

Originally Posted by BirgerGov

I created a button and recorded this macro so the correct cells were selected

We're a bit confused as to which solution you ended up with. Want to share?!

**BirgerGov** · 10-25-2016, 09:04 AM

Yours John

Havent tried the rest yet but I will

**AliGW** · 10-25-2016, 09:06 AM

Please remember to add rep to those who helped you (Olly, John and Ash).

**BirgerGov** · 10-26-2016, 02:13 AM

Thanks again, reputation added to all who contributed

Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Re: Help with IF-function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result

Bookmarks

Bookmarks

Posting Permissions