• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Counting of Rows

O

ojaskulkarni

New Member
Hi, I have a growing database of Sales leads and I have to calculate their eligibility for incentives. Now the incentive plan is in "tiers". Meaning Lead 1 to 5 (chronologically by attended Date) is defined as Tier 1. Lead 6 to 10 is Tier 2. Each "Tier" has a different amount to be calculated. This has to be done across 100+ rows every time, hence wanted to automate. I already used "defined tables" and use table functions like #ThisRow etc. within CountIF to validate other criterion. The eligibility is dependent on 3-4 variables and basis this the record is marked as "Eligible" or "Not Eligible" within every calendar quarter. The last step is to mark Tier for each record and then amount can be calculated. Please help.


An example of formula I use to validate count of records eligible in each quarter:

(this may be irrelevant, but will give an idea of controls in use already)

=IF(LeadsTrxn[[#This Row],[Plan type]]="A",IF(AND(COUNTIFS(LeadsTrxn[[#Data],[Campaign Owner]],LeadsTrxn[[#This Row],[Campaign Owner]],LeadsTrxn[[#Data],[Status]],"Attended",LeadsTrxn[[#Data],[Eligible Qtr]],LeadsTrxn[[#This Row],[Eligible Qtr]])>8, LeadsTrxn[[#This Row],[Status]]="Attended", LeadsTrxn[[#This Row],[PaidLeads]]="Not Paid"),"Eligible","Not Eligible"),IF(AND(COUNTIFS(LeadsTrxn[[#Data],[Campaign Owner]],LeadsTrxn[[#This Row],[Campaign Owner]],LeadsTrxn[[#Data],[Status]],"Attended",LeadsTrxn[[#Data],[Eligible Qtr]],LeadsTrxn[[#This Row],[Eligible Qtr]])>11, LeadsTrxn[[#This Row],[Status]]="Attended", LeadsTrxn[[#This Row],[PaidLeads]]="Not Paid"),"Eligible","Not Eligible"))
 
Hi ojaskulkarni,


Welcome to the forums, it would be great if you could upload a sample file, it is demn difficult to understand this formula ;)


Regards,

Faseeh
 
Thanks for attempting to help @Faseeh. The data is confidential. But I understand and let me clarify.

Lets say:

I have 100 records named as Lead1, Lead2, ... , Lead100 in column A

I have various dates against each of these in column B

In column C, (and using the date value in column B) I want a formula or Macro to mark first 5 records as "Level 1"; here 'first' 5 records are by the dates occurring first.

then the next 5 records as "Level 2"; so on...

until we have the last 5 records marked as "Level 20". Hope this helps.
 
Hi Mr. Kulkarni!


We understand your privacy.. but we are just asking for SAMPLE data.. you don't have to provide complete and accurate your Lead's / Account but change the data / name in some random Name / data.. and just upload a sample file.. It help us to decide whether date contain Duplicate... do we need to check is two same date occurs.. does date are really in DATE format..

here 'first' 5 records are by the dates occurring first.
Click to expand...

I also guess... I have read the above the line correctly..


but regardless you data / structure.. I came up with below formula.. Check the same with you data..


Code: 
="Level "&ROUNDUP(MATCH(SMALL(COUNTIF($B$1:$B$100, "<"&$B$1:$B$100), ROW()), COUNTIF($B$1:$B$100, "<"&$B$1:$B$100), 0)/5,0)


Confirm the formula by Ctrl + Shift + Enter, Not just Enter


and your above 1st question.. doesn't contain any relationship with Question 2


Regards,

Deb
 
You must log in or register to reply here.
Back
Top