Multiple cell queries

[briandouglas10]

Hi

I'm trying to set up a worksheet for calculation of comission.

Columns by month for 4 years, then rows for comission paid, period paid over and month paid.

I then want to set a formula that if the month paid = column row, the divide comission by period paid and enter this in that months cell and the next 47 cells accross.

I am working it on various if functions at the moment but this is very complex .... HELP!

[Back2Basics]

are you trying to get something that will spread the cost of the commision over several months?

I don't fully understand what you want to get.
Could you post a spreadsheet sample with the column and row headers that you want and then sample entries into some of the cells. Don't worry about formula just numbers of what you would be expecting if that is okay.

[briandouglas10]

Yes, thats right.

Total -Indemnity On risk
comm period Oct-08 Nov-08 Dec-08 Jan-09
567 24 Jan-09 0.00 0.00 0.00 £23.63

As above i need it to look at the on risk date, and where this matches the column date divide the commission by the number of months and then enter that sum into that month and the next 23 months.

Hope this makes sense.

Thanks in advance for your help

Brian

[DonkeyOte]

If we assume:

Row 1 are headers... first month being in D1

Row 2
A: holds Total to be Split
B: holds Period by which Total to be Split
C: holds Start Month

Then

D2:
=IF(AND(D$1>=($C2-DAY($C2)+1),D$1<DATE(YEAR($C2),MONTH($C2)+$B2,1)),$A2/$B2,0)
copied across for remaining 47 months

Above assumes you are not apportioning the first & last month based on day of month (C)... ie using your example Jan-09 gets a full 1/24 split as opposed to a apportioned month / 24 split.

[briandouglas10]

FAN bloody tastic

Genius !!

Cant thank you enough

Brian