Calcs stop at a certain structure point

**lescrowe** · 09-03-2014, 10:53 PM

Using the if calc to calculate commission payable using a percentage (b17:b23) of a cell based on certain criteria (a17:a23)
Have copied and pasted b7 along row b.
For some reason the calcs stop at 15% and will not exceed that apart from the calc in b7 which works fine.
Have searched the string of calcs but unable to spot any difference
Really need some help.....

Spread sheet attached

**micope21** · 09-04-2014, 02:31 AM

Hi

Try this

B17:B23 is fine.

Change A17: A23 to this
0
1500
1600
1700
1800
1900
2000

B7

Formula:

Please Login or Register  to view this content.

copy a cross

Regard

**samba_ravi** · 09-04-2014, 03:17 AM

in Cell "B7"
Use =IF($B$2<1500,B4*$B18,IF(AND($B$2>1499,$B$2<1600),B4*$B19,IF(AND($B$2>1599,$B$2<1700),B4*$B20,IF(AND($B$2>1699,$B$2<1800),B4*$B21,IF(AND($B$2>1799,$B$2<1900),B4*$B22,IF(AND($B$2>1899,$B$2<2000),B4*$B23,IF(AND($B$2>1999),B4*$B24,))))))) instead
=IF($B$2<1500,B4*$B17,IF(AND($B$2>1499,$B$2<1600),B4*$B18,IF(AND($B$2>1599,B$2<1700),B4*$B19,IF(AND($B$2>1699,$B$2<1800),B4*$B20,IF(AND($B$2>1799,$B$2<1900),B4*$B21,IF(AND($B$2>1899,$B$2<2000),B4*$B22,IF(AND($B$2>1999),B4*$B23,)))))))