PDA

View Full Version : An Excel question


Senior Paper Monitor
20th Jul 2014, 10:16
The project in hand is a Excel workbook which will handle the allocation of 'jobs completed per month bonus kitty' and the allocation of such bonuses to qualifying staff members in any given month.

'Worksheet1' primarily calculates the bonus kitty amount earned for a specific number of completed jobs in any month.

There are other calculations there (minimum bonus guarantees, total earnings, projected annual earnings etc) but these do not affect the core question.

The core data lines are:

1) Job number (1 - 40)
2) Amount allocated to 'the kitty' per job (jobs 1 - 6 earn £22 each, jobs 7 - 15 earn £25 each, jobs 16 - 25 earn £30 each etc etc)

and from this a simple calculation of:

3) Total amount allocated to allocated to 'the kitty' in a given month based on qualifying jobs completed


'Monthly worksheets' count the qualifying jobs completed - and will subsequently share out the bonus kitty.

Again there are some other calculations subsequently (monthly staff costs, actual revenue, operating profit/loss etc) but again these do not affect the core question.


What I want to do is use the 'value' in the box showing qualifying jobs in the month (a number between 1 and 40) to 'lookup' the corresponding 'kitty value' in 'worksheet 1' and insert it in another cell in the monthly worksheet.

i.e
qualifying jobs counted = 16 (calculated on 'monthly worksheet')
16 jobs completed = £426 bonus (already calculated and displayed on 'worksheet 1
insert £426 in appropriate cell on 'monthly worksheet'


As I understand it, 'if functions' can't cope with this because of the number of options (1 - 40, in practice I could probably do away with 1 - 5 but this doesn't make a difference) but will be delighted if someone tells me otherwise.

I have looked at 'lookup' (in its various versions: VLOOKUP etc) and 'pivot tables' (neither of which are my areas of expertise admittedly) but can't see a way to do this.


If someone could just point me in the right direction I have a coffee table full of manuals to work through the detail - thanks in advance

Booglebox
20th Jul 2014, 11:12
I think VLOOKUP is exactly what you want. As I am chained to my desk and have nothing better to do, I tried to interpret your post into an example:

http://i.imgur.com/S7f6pUF.png

In this case the formula returns 507 quid. Correct?

Senior Paper Monitor
20th Jul 2014, 11:15
looks good - am off to play - back in 10 minutes

Senior Paper Monitor
20th Jul 2014, 11:43
Yup - switch to 'H' (rather than 'V') in my case, remove gaps between data rows, insert 'other sheet pointer' and Bob's yer Aunty's Uncle !!


Knew it had to be there somewhere - many thanks Booglebox

ExGrunt
23rd Jul 2014, 09:37
SPM,

[Pedant mode: on]
Strictly, HLOOKUP and VLOOKUP are legacy functions and their use is discouraged in spreadsheets where rows of data are being inserted and deleted. This is because they tend to be a bit 'brittle' when dealing with changing ranges and it is often not obvious that an error has crept in. The preferred functions are a combination of INDEX and MATCH (or MMULT or SUMPRODUCT).

eg: INDEX( Row Range I want a return value from , MATCH ( Lookup Value , Row Range I want to check against , 0 For exact match), Column offset)

So using Booglebox's example table the function would be:
=INDEX(D3:D42, MATCH(H2,B3:B42,0),0)

[Pedant mode:off]

But, if HLOOKUP works for you then don't fix what ain't broke.

However, I would strongly urge that you perform check calculations on anything that involves money. This is a completely separate calculation that shows there is no mechanical error in the spreadsheet. A possible example is the reverse look up eg lookup the kitty value and see that it matches the correct number of completed jobs.

Further, if you are doing cross sheet lookups I would recommend using named ranges. So select B3:B42 on sheet 1 and key ALT+I, N , D and enter 'Jobs'. Then select D3:D42 and use the same key sequence and enter 'Cumulative'. Then the formula on every sheet becomes:

=INDEX(Cumulative, MATCH(H2,Jobs,0),0)

HTH

EG

mixture
23rd Jul 2014, 11:19
But, if HLOOKUP works for you then don't fix what ain't broke.

That's not the right attitude !

If you know there are likely to be issues down the line (as you outlined yourself), then better to swat the bug now rather than having to waste time finding the bug later and then fixing it.

Of course really he should be using a database rather than abusing an Excel spreadsheet as I've got doubts about his scalability... but that's another story ;)

Booglebox
23rd Jul 2014, 14:28
their use is discouraged in spreadsheets where rows of data are being inserted and deleted
That is very true. Index and Match are far more robust, and also more flexible, but also a bit more confusing.