Adding add-on per kg untill add-on changes

**j1985** · 08-24-2010, 01:39 AM

=IF(ISERROR(VLOOKUP(S68;Template!$A$2:B79;2;FALSE));T67+Template!$B$22;(VLOOKUP(S68;Template!$A$2:B79;2;FALSE)))

I need to change the red part of the formula above in such a way that instead of adding the same add on throughout the whole column it adjusts when the add-on changes. At the same time excel should remain copying the weight based values.

In the formula S68 refers to column weight_max in worksheet2, T67 refers to a value in worksheet 2, column zone 1. $B$22 refers to 0.24.

Worksheet 1 is the original while worksheet 2 is what it should eventually become. I want excel to copy values for 10, 15, 20, 25 kg etc. But in between I need to add the add on. So 16 kg will have the value of 79,5 + 0.24. However after 30 the add-on changes and this will change another few time throughout the document.

Thanks alot already for any help. If this is not possible please tell me as well than I can stop trying

**teylyn** · 08-24-2010, 01:59 AM

j, welcome to the forum.

please post a workbook, not a picture. Otherwise, I can give you a few good photoshop links.

**j1985** · 08-24-2010, 02:30 AM

My apologies, attached is the workbook

**teylyn** · 08-24-2010, 06:56 AM

That's much better, thanks!

My suggestion is to remove the Add-on rows from column A completely, and instead build an Add-on lookup table, for example in columns D and E (see attached file). Then you can use this formula on worksheet 2

Please Login or Register  to view this content.

If Lookup does not find an exact match in column D, it will use the next smallest number in D and return the value from column E. So, if the value is 11, but can't be found, the next smallest number will be 10 and the corresponding value in column E is 0.24. Thus, you can easily maintain your Add-ons when they change, or you can set different Add-on thresholds.

cheers,