I posted an issue in Oct and recieved the email below which worked. I'm importing the excel file into a program called Crystal Xcelsius and it doesn't like any of the formulas below.
Can anyone provide a solution using the functions that Xcelsius supports - I attach the latest email from their tech support.

Hello David,

After looking at your file, the issue seems to be a result of the syntax of your SUMPRODUCT function. Currently you have the following:
=-SUMPRODUCT((B43>G49:G52)*((B43-G49:G52)*(H49:H52))*B48)+H55

Unfortunately Xcelsius is strict on how the function can be used, which will have to closely follow how SUMPRODUCT is defined in the Excel help guide:

SUMPRODUCT(array1,array2,array3, ...)

For example: =SUMPRODUCT(A2:B4, C2:D4)

Xcelsius does not support conditional arrays and therefore cannot evaluate: (B43>G49:G52) or (B43-G49:G52). So in the SWF runtime, the SUMPRODUCT portion of that formula is not evaluated (null or zero) and is then added by H55 which is - £51k. Therefore you will need to use functions supported by Xcelsius in order to produce the same result, such as the SUMIF functions, using the syntax defined in the Excel help guide.

Here is the section specifically on supported excel functions: http://www.xcelsius.com/help/crystal...tionsAlpha.htm







ANSWER TO POST IN OCTRon Coderre has just replied to a thread you have subscribed to entitled - Different royalty rate depending in unit sales - in the Excel Worksheet Functions forum of Excel Help Forum.

This thread is located at:
http://www.excelforum.com//showthrea...9&goto=newpost

Here is the message that has just been posted:
***************
Here are a couple options...

With:
C2: (unit price)
C4: (units)


Single-formula, no-tables approach:
=SUM((C4/10000>{0,25,50,75})*(C4/10000-{0,25,50,75})*10000*({2.5,1.5,3.5,1.5}/100))*C2

Or....using a lookup table in H1:J5
Units Rate Incremental
0 2.5% 2.5%
250,000 4.0% 1.5%
500,000 7.50% 3.5%
750,000 9.0% 1.5%

and this formula:
=SUMPRODUCT((C4>H2:H5)*((C4-H2:H5)*(J2:J5))*C2)


Is that something you can work with?