comission split based on 365 days

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Bruce
Thanks so much. This is exactly what i was needing.

Thanks again for your help.

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

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