Excel Formula Help Required. Calling All Excel Experts!

placeholder · 17 Feb 2015 at 11:21

I have an excel formula problem that I am looking for some help with. I hope I can explain this as simply as possible, I’m sure its not actually that hard a problem to solve but by excel knowledge is reasonably hit and miss.

All of this refers to employees and their salaries.

I have a list of a few hundred employees that I get every month. Each employee has a name, an employee reference number, their salary for the month and a four digit code which tells me which area of the organisation their wages are paid from.

So for example

1000 John Smith 201403 £2,000

Would be Mr John Smith is employee number 201403 and he was paid £2,000 last month which was coded to cost centre 1000

I have another spreadsheet which I have created which I have sorted by the four digit cost centre number and has their total salaries for the year along with a whole pile of other information and calculations which doesn’t matter for the purposes of this question. Each month I use my spreadsheet to interrogate the monthly list and pull back the wage for each employee for that month and add it to their total for the year.

The purpose for all this is to compare budgeted salary totals for each area of the organisation to actual salaries paid as the year progresses. I use index and match to find the people from the monthly list based on matching their employee number and cost centre and returning the salary value

The spreadsheet works perfectly with one exception. It completely ignores any new employees who are employed throughout the year. They don’t exist in the initial spreadsheet and as such their info isn’t pulled back each month from the monthly sheet..

So here is my question if you are still awake!

Is there a formula that I could use which I can enter on the main spreadsheet below each cost centre which will look at the cost centre number and all the employee numbers and go to the monthly spreadsheet, find the same cost centre number and compare the employee numbers associated with that cost centre and return either a count or the values that don’t appear in both lists? This will give me a starting point for adding in any new employees every month.

Im not sure this makes any sense at all so apologies if its really really confusing. Any help is gratefully appreciated.

Grrrrr · 17 Feb 2015 at 13:26

Can you upload an example spreadsheet (or even just a screenshot) showing in red font what you'd like the output to be?

placeholder · 17 Feb 2015 at 14:59

Difficult for me to give an example or even a screenshot as its all held on the computer at work.

However the output that I’m looking for would be simply a number in a cell below each cost centre for example

1000 J.Smith 202403 £20000
1000 B Jones 202404 £19000
1000 W Rooney 202404 £18000

2

That would be the 3 employees on my spreadsheet coded to cost centre 1000 with their employee numbers and their earnings so far this year. What I currently do is use a formula that looks up the cost centre (in this case 1000) and the employee number (take your pick from any of the three) and look for a line that matches both in the monthly spreadsheet using the INDEX and MATCH functions. I then have the salary value from that line returned and I can add it to the running total for that employee.

However if a new employee starts that I don’t know about I wont get a value back for him because his details aren’t on my spreadsheet originally so no lookup is performed. What id want to happen is to create a formula that I can enter below each cost centre which will look up the cost centre (1000 in this case) and find any employee numbers on the monthly spreadsheet that are against that cost centre but not in my spreadsheet and return a count in the cell – the example above the 2 below the cost centre tells me that there are two employee numbers in the monthly spreadsheet against cost centre 1000 that aren’t in my spreadsheet so I now know that I need to go and find out which they are. I can do this manually no problem at all and simply add them in then next month the original formula will pick them up and the 2 will be replace by a 0 (assuming no other new employees). Im guessing it would be done with some form of combination of COUNT, LOOKUP or INDEX and MATCH but beyond that I don’t really know. Essentially I just want something to alert me to the fact that I have new employees to build in. Once I know that the process for picking them up and entering their data will only take minutes.

Does that make any more sense or am I making it worse??? Its basically a matching exercise, im just not quite sure how!

Grrrrr · 17 Feb 2015 at 15:53

The index match function you are already using should throw up an error when it tries to find an employee that doesn't exist in the source spreadsheet. Can you not count these errors?

Grrrrr · 17 Feb 2015 at 16:33

Code:

=SUM(--ISERROR(MATCH(empl_rng,IF(cc_mnthrng=cc,empl_mnthrng),0)))

Entered by pressing Ctrl+Shift+Enter

cc_mnthrng is the range in the monthly spreadsheet with the cost centre numbers

empl_mnthrng is the range in the monthly spreadsheet with the employee names

cc is the cost centre you want to find missing employees for. (In your example this would be 1000)

empl_rng is the range of employees for cost centre 1000 (In your example this is J Smith, B Jones, W Rooney)

There is a pretty high change this won't work because I can't see exactly how your spreadsheet is laid out, but give it a try.

placeholder · 17 Feb 2015 at 16:34

Grrrrr said:
The index match function you are already using should throw up an error when it tries to find an employee that doesn't exist in the source spreadsheet. Can you not count these errors?

No it doesn't. The index function exists in the original spreadsheet and looks up the employee number in that spreadsheet and then matches it in the monthly one, so its only looking up the numbers that already exist in my spreadsheet if that makes sense? Its never tries to match with the any new employees because they don't exist on my spreadsheet yet. I need some way of looking up the monthly spreadsheet and referencing the entries that have no match. I will then know that they are new.

I appreciate your help and I also appreciate that im probably not explaining this very well!

placeholder · 17 Feb 2015 at 16:36

Grrrrr said:
Code:

=SUM(--ISERROR(MATCH(empl_rng,IF(cc_srcrng=cc,empl_srcrng),0)))

Entered by pressing Ctrl+Shift+Enter

cc_srcrng is the range in the source spreadsheet with the cost centre numbers

empl_srcrng is the range in the source spreadsheet with the employee names

cc is the cost centre you want to find missing employees for. (In your example this would be 1000)

empl_rng is the range of employees for cost centre 1000 (In your example this is J Smith, B Jones, W Rooney)

There is a pretty high change this won't work because I can't see exactly how your spreadsheet is laid out, but give it a try.

Thanks I will, It could be a good starting point at least. Its just awkward that im posting here when im not at work so I cant readily try it out and report back!

Grrrrr · 17 Feb 2015 at 16:56

I think I'm slowly beginning to understand

I can also tell you that formula above won't work, I'll have to come back to it later.

placeholder · 17 Feb 2015 at 17:07

No bother! Even help with how to approach it would be good and I can play about with it myself too. My excel knowledge is learned through finding solutions to specific problems rather than a good overall knowledge so sometimes I can be stumped by relatively simple things!

Agent Smith · 25 Feb 2015 at 05:52

Hi,

I would be looking to create this using a table and utilising the power of the pivot table and slicers within excel. you will be able to do everything you need and compare year vs year or month vs month etc...

it is very simply to set up and with dymanic charting and data it would help.

If you need some help them give me a shout.

Shaun

placeholder · 25 Feb 2015 at 08:47

Im not going to lie, I wouldn't know where to start doing that!!!

What I currently have is one file split into sheets for each "Budget Manager". With each "Budget Manager" sheet there is a list of all their "Cost Centres" along with the employees who are charged to each Cost Centre. Then each month I do the look up.

I don't really know a great deal about pivot tables or such like, as I say my Excel knowledge is needs driven, although I do use it quite a lot its probably on quite a superficial level.

Maybe I need to rethink my entire approach to the issue. I probably have the scope to change it as we approach the new financial year.

HeX · 25 Feb 2015 at 09:47

This does indeed sound like something that'd be super easy by using a pivot table you just refresh each month.

You'd just throw all your data into a single sheet like this:

Then slap it in a pivot like so:

That gives you a month on month breakdown of salary along with a Year to Date cumulative total, by cost centre and employee number. You can see that employee 458215 left in month 03, and that a new employee 741547 started.

placeholder · 25 Feb 2015 at 10:02

Wow!!! Ok this needs some investigation! I'm going to have a play with this and see what it does. I suspect I will have some questions but many thanks to all who have responded.

Ethermaster · 25 Feb 2015 at 23:00

Pivot tables for the win