Kaz09
New Member
- Joined
- Jun 1, 2015
- Messages
- 8
- Office Version
- 2013
- Platform
- Windows
I am trying to create a formula to determine the result of a lookup table based on two criteria. Eg. If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6 to calculate sum.
Any guidance will be greatly appreciated
| Single Book Target | Single Books Sold | Percentage of Sold Boks | No. of Book Sets Target | No. of Book Sets old | Percentage Book Set Sales | % Volume Bonus Single Books | Volume Bonus Amount Book Sets (70-100%) | Volume Bonus Amount Book Sets (101-150%) | Volume Bonus Amount Book Sets (150%+) | Volume Bonus Payable | ||
| 7 | 7 | 100.00% | 50 | 59 | 118.00% | 70% | 101% | 150+% | ||||
| 90.00% | £300.00 | £500.00 | £750.00 | |||||||||
| 100.00% | £450.00 | £800.00 | £1,050.00 | |||||||||
| 115.00% | £550.00 | £950.00 | £1,200.00 | |||||||||
| Examples of what I am trying to calculate | ||||||||||||
| If F5 >=90% but less than 100% and C5 >= 70% but less than 101% then multiple F5 by J6 | Answer should be: £17,700 |
Any guidance will be greatly appreciated
