An Excel question
Thread Starter
An Excel question
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
'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
Join Date: Jan 2008
Location: Timbuktu
Posts: 962
Likes: 0
Received 0 Likes
on
0 Posts
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:
In this case the formula returns 507 quid. Correct?
In this case the formula returns 507 quid. Correct?
Thread Starter
Solved ...
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
Knew it had to be there somewhere - many thanks Booglebox
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
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
[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
Last edited by ExGrunt; 23rd Jul 2014 at 09:53.
Join Date: Aug 2002
Location: Earth
Posts: 3,663
Likes: 0
Received 0 Likes
on
0 Posts
But, if HLOOKUP works for you then don't fix what ain't broke.
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
Join Date: Jan 2008
Location: Timbuktu
Posts: 962
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by ExGrunt
their use is discouraged in spreadsheets where rows of data are being inserted and deleted
