Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comission split based on 365 days
I have to calculate the commission split of a selling agent based on what he
has been closing during the last 365 days. These are the columns that are involved in this calculation H N Total sale - closing date When I enter the latest closing date (plus the corresponding total sale), the system reviews this date and the previous ones (about 30 of 40 records) and check which ones falls in the €ś365 days€ť condition, and then, gives as result the sum of the total sales of those days that enter in that condition. I used a IF formula but, only allowed me 7 conditions€¦ =(IF((N31-$N$2)<=365,SUM($H$2:H31),IF((N31-$N$3)<=365,SUM($H$3:H31),(IF((N31-$N$4)<=365,SUM($H$4:H31),(IF((N31-$N$5)<=365,SUM($H$5:H31),(IF((N31-$N$6)<=365,SUM($H$6:H31),(IF((N31-$N$7)<=365,SUM($H$7:H31),(IF((N31-$N$8)<=365,SUM($H$8:H31),0))))))))))))) Could anyone help me with this? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comission split based on 365 days
If I understand correctly, you're using the series of IFs to determine the
'cutoff' item and adding all the entries following. I think this can be handled more easily and flexibly with a SUMIF function: =sumif(n$2:n31,"=" & (n31-365),h$2:h31). This will check all entries in the range n2:n31 for dates in the range [n31-365,n1] and add the corresponding sale amount in h2:h31 when the date condition is satisfied. Am I interpreting correctly? --Bruce "haidith" wrote: I have to calculate the commission split of a selling agent based on what he has been closing during the last 365 days. These are the columns that are involved in this calculation H N Total sale - closing date When I enter the latest closing date (plus the corresponding total sale), the system reviews this date and the previous ones (about 30 of 40 records) and check which ones falls in the €ś365 days€ť condition, and then, gives as result the sum of the total sales of those days that enter in that condition. I used a IF formula but, only allowed me 7 conditions€¦ =(IF((N31-$N$2)<=365,SUM($H$2:H31),IF((N31-$N$3)<=365,SUM($H$3:H31),(IF((N31-$N$4)<=365,SUM($H$4:H31),(IF((N31-$N$5)<=365,SUM($H$5:H31),(IF((N31-$N$6)<=365,SUM($H$6:H31),(IF((N31-$N$7)<=365,SUM($H$7:H31),(IF((N31-$N$8)<=365,SUM($H$8:H31),0))))))))))))) Could anyone help me with this? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
comission split based on 365 days
Hi Bruce
Thanks so much. This is exactly what i was needing. Thanks again for your help. "bpeltzer" wrote: If I understand correctly, you're using the series of IFs to determine the 'cutoff' item and adding all the entries following. I think this can be handled more easily and flexibly with a SUMIF function: =sumif(n$2:n31,"=" & (n31-365),h$2:h31). This will check all entries in the range n2:n31 for dates in the range [n31-365,n1] and add the corresponding sale amount in h2:h31 when the date condition is satisfied. Am I interpreting correctly? --Bruce "haidith" wrote: I have to calculate the commission split of a selling agent based on what he has been closing during the last 365 days. These are the columns that are involved in this calculation H N Total sale - closing date When I enter the latest closing date (plus the corresponding total sale), the system reviews this date and the previous ones (about 30 of 40 records) and check which ones falls in the €ś365 days€ť condition, and then, gives as result the sum of the total sales of those days that enter in that condition. I used a IF formula but, only allowed me 7 conditions€¦ =(IF((N31-$N$2)<=365,SUM($H$2:H31),IF((N31-$N$3)<=365,SUM($H$3:H31),(IF((N31-$N$4)<=365,SUM($H$4:H31),(IF((N31-$N$5)<=365,SUM($H$5:H31),(IF((N31-$N$6)<=365,SUM($H$6:H31),(IF((N31-$N$7)<=365,SUM($H$7:H31),(IF((N31-$N$8)<=365,SUM($H$8:H31),0))))))))))))) Could anyone help me with this? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No. of days split into periods | Excel Discussion (Misc queries) | |||
Conditional formatting based on a date + 30 days | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Split cell values based on content | Excel Worksheet Functions | |||
How can I calculate Vacation Time earned based on length of emplo. | Excel Discussion (Misc queries) |