Excel Formula Expert Required

  Evolution 11:24 12 Mar 2006


I have put together the following spreadsheet:

% Net Sales Commission
Revenue Commission £25,000.00

Up to £14,000 3.75% £14,000.00 £525.00
£14k- - £18k 3.50% £4,000.00 £140.00
£18k - £22k 3.25% £4,000.00 £130.00
£22k+ 3.00% £3,000.00 £90.00

The only calculating columns are Net Sales and Commission. Net Sales is where I have my issue.

The only figure manually input is the Net Sales total, in this case £25,000.00

This amount, then has to be split in to the four rows as shown above and as follows:

Row 1 - Always the first £14,000 of the Net Total
Row 2 - the next £4000 (maximum) or the balance
Row 3 - the next £4000 (maximum) or the balance
Row 4 - the balance, regardless of the amount

Currently I am entering these four amounts manually but this means I have to do it every time I have a different Net Sales Total.

Ideally I just want to enter the Net Sales total and use a formula in the four Net Sales cells to split the total amount automatically, by row, as previously indicated.

Note that if the Net Sales Total was £16,000, the spreadsheet would read as follows:

Row 1 - £14,000
Row 2 - £2000

I hope that I have explained myself clearly enough and hope that there is a formula out there for me to acheive this.

Thanks in advance for any help!


  Simsy 12:26 12 Mar 2006

though there may be a better way of doing this, depending on the layout...

I've named the cell with Net Sales Total in "NetSales"

Row 1 formula =IF(NetSales>14000,14000,NetSales)

Row 2 formula =IF(NetSales<=14000,"",IF(NetSales>=18000,4000,NetSales-14000))

Row 3 formula =IF(NetSales<=18000,"",IF(NetSales>=22000,4000,NetSales-18000))

Row 4 formula =IF(NetSales<=22000,"",NetSales-22000)

Note also that it may be better to have the various thresholds in cells and have the formula refer to those cells... that way any changes can be made to the cells not the formula.

I suspect a lookup table may be the best way of approaching this really, but it's difficult to preguess any other variables that may be involved.

However, I think what I've done above will work if you just need it quickly.



  Evolution 15:57 12 Mar 2006

Hey Simsy,

Thank you so much for your advice.

Did exactly what you said, put key values in seperate cells and made a couple of other slight tweaks to avoid some Value comments occuring in the commssion cells!

Everything works perfectly!

You certainly put me on the right track and I can't thank you enough!!

Absolutely brilliant!

Thank you, thankyou, thankyou!!


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires