Problem with Index/Match formula

**djfatboyfats** · 05-18-2009, 05:41 AM

Please see the attached file, i am baffled why this not working and its doing my head in, please can i ask for some help.

In G8 i have a formula thats looking at data from columns Z:AK the figure im supposed to be getting is around the £600 mark but instead im getting the figure £17,324,993.07

Help!!!

Thanks in advance

DJ

**DonkeyOte** · 05-18-2009, 06:29 AM

You would get £750 prior to multiplying by C6, is that the expected result ?

**djfatboyfats** · 05-18-2009, 07:06 AM

The figure should give £808.50

**DonkeyOte** · 05-18-2009, 07:30 AM

I understand the math, ie:

(875-700/4999.99)*3100

but I think you would be best served breaking the above formula into component parts (stored in helpers) ... else you will have a long formula.

For ex...

Please Login or Register  to view this content.

Even in the above there are still lots of repetitive calcs which could be dispensed if you were to use more helper cells (say to hold thresholds etc...)

EDIT: ignore above and see post #8 (Roger Govier)

**DonkeyOte** · 05-18-2009, 07:39 AM

Below is a non-helper approach...

Please Login or Register  to view this content.

**djfatboyfats** · 05-18-2009, 08:06 AM

Thats good but not really working, it will still give someone a bonus if the figures in H5 is less then 100, they can only get a bonus with over 100 payments.

HTML Code:

The tread above shows what i want to do, this is working correctly and just what i need, but all i wanted to do on this worksheet is amend this code to place it in a differant place

Thanks

DJ

**DonkeyOte** · 05-18-2009, 08:19 AM

Why not just encase that provided with an IF ?

=IF(N(H5)<100,0,formula provided)

**Roger Govier** · 05-18-2009, 08:29 AM

Hi

Your problem is that you are 1 column out in your second Match.

MATCH(H5,{100,200,275},1)*3)
should be
(MATCH(H5,{100,200,275},1)*3)+1)

Change the formula to
=IF(ISNA(INDEX($Y$9:$AJ$17,MATCH(C6,$Y$9:$Y$17,1),(MATCH(H5,{100,200,275},1)*3)+1)*C6),"No Bonus",INDEX($Y$9:$AJ$17,MATCH(C6,$Y$9:$Y$17,1),(MATCH(H5,{100,200,275},1)*3)+1)*C6)

and you will get the result of £808.50