assign data to users based on totals and countif results

L

lheunis

New member
Joined
Mar 27, 2018
Messages
13
Reaction score
0
Points
0
Excel Version(s)
2010
hi, not sure if this is even possible
i have a spreadsheet of about 31 users, these users are split into categories based on the total of Y and N indicators, basically at the end of the day they are either "big", "Medium" or "small"

what i need is a way to take my total numbers of accounts and split them into the 3 categories above (big, small medium) this cannot be split equally

i want to use the countif function to see how many "bigs" there are for example and then allocate the accounts to them

let’s say i have 4 big, 19 Medium and 8 Small users and my total accounts is 41427, i need the 41427 split between them using the numbers above

i will attach my spreadsheet, i want to be able to just change the total (in yellow) and the formula to run through and change results of how to split population (in green) - this should also at bottom again total to 41427

you will see my formulas and used and hopefully understand what i tried to do, i am stuck

i used the following to identify the split in columns H - K
=COUNTIF(B4:B24,"big")
=COUNTIF(B5:B25,"medium")
=COUNTIF(B5:B25,"Small")
=COUNTIF(B5:B25,"No_Handover")

and the following to accumulate final results in column D -
=IF(B4="big",$L$8,IF(B4="Medium",$L$9,IF(B4="Small",$L$10,$L$11)))


 

Attachments

  • Hard Rules Handovers.xlsx
    14.7 KB · Views: 17
In your sample file you say this:

Big should get the largest no of accounts and then medium etc.
Click to expand...

This is very vague. You need to be clear on how you expect this to happen: are there weightings involved? What relevance does the count of small, medium and big have on this? The directions are not clear enough at present, and it would be of more use to us if you manually mocked up a solution that you would want to see rather than showing us what you've done that isn't what you want.
 
hi, im not sure if i understand correctly, but my attachment is exactly what i want, i give each user a weighting based on the columns showing the Y and N, i use a countif -
=IF(COUNTIF($O9:$Y9,"Y")<4,"No_Handover",IF(COUNTIF($O9:$Y9,"Y")=4,"Small",IF(COUNTIF($O9:$Y9,"Y")=5,"Medium",IF(COUNTIF($O9:$Y9,"Y")=6,"Medium","Big"))))

this in turn tells me i need to give a bigger population to the Big users, and the smallest population to the Small users - the issue i am having is i am battling to split my total population according to this as i do not want to give them equal amounts

on my file in column O is basically the result i am looking for using the calculations done next to it
there are 4 Big Users, 19 Medium and 8 Small - the total users are 31
total population is 41 427 - i need the 41 427 to be split according to the Users and just doing a Total / 4 is splitting the population into 4 equal parts, which then leaves nothing for the other users.
Total 41,427BigMediumSmallTotal
419831
total split -
Big10,357
Medium2,180
Small5,178

dont know if this makes more sense
 
No, this makes no more sense at all. You have simply repeated what is obvious, but have not addressed the questions I asked at all. You have recognised that the way you have split the total is not correct, but you have not given an idea of the results you DO want.

Unless you address the specific queries I have I cannot help you.
 
Specifically, you know that these figures are incorrect:

Excel 2016 (Windows) 32 bit
T​
U​
7​
total split
8​
Big10,357
9​
Medium2,180
10​
Small5,178
Sheet: Sheet1

So what would you expect them to be? How do you want to weight the split for small, medium and big? If you don't understand what I mean by 'weight', tell me. In other words, wat proportion of the total do you think the bigs should get between them - how much of the total do you want allocating to the big share and then splitting between them? If you want Excel to work it out, you need to tell it the rules.
 
Last edited:
hi, that is my whole problem i am having, i am not sure how to tel excel to weight the split, BIG should get the BIGGEST portion, there are however just 4 BIG users based on other calculations already done, then the 19 MEDIUM users will get the next Portion if you can call it that and the remaining 8 SMALL Users will get the rest
i am not sure how to do that calculation or if it's even possible

sorry if i sound confused, i have been battling to figure this out for a while now, that i am even making myself confused

i have tried percentages - but i still not getting the right answer (the way i will know its correct is - if you check my original attachment - the Total at the bottom of Column O in green called Rules on Handover will sum to my original total of 41 427
Total 41,427BigMediumSmallTotal
419831
of total users 12.90%61.29%25.81%
difference 87%39%74%
total split -
4Big9,020total*difference/4
19Medium844total*difference/19
8Small3,842total*difference/8
 
OK - I'll have a look at this in a bit. I have to go out for a while. You definitely need a weighting system, but the percentages aren't going to work because your number of bigs is smaller than medium and small. If it were me, the proportion of the total would be weighted 50% for the bigs, 30% for the mediums, 20% for the smalls - how does that sound?
 
this is basically what i need at the end of the day if it makes any more sense then what i said

UserResult Split
DPL4BIG 4,004
DMC4BIG 4,000
NRB4BIG 4,000
VVM4BIG 4,000
MCS4BIG 4,000
CRE4BIG 4,000
CON0Medium 2,000
GRL4BIG 4,002
GVMNMedium 2,000
CCI0Medium 3,455
MBD4Small 800
VDM4Small 800
NDC4Small 800
SSD4Small 800
LGR4Small 800
NUD5Small 800
SAY0Medium 1,166
Total 41,427
 
that should work, if i use only percentages as per your suggestion i should be able to figure it out, what made my mind confused is exactly what you said in regards to big being small than medium etc.

i will play on my side too and wait for you reply, thanks for the patience
 
No problem! :)

If you weight the proportions 50-30-20, you will get this:

Excel 2016 (Windows) 32 bit
T​
U​
7​
total split
8​
Big5,178
9​
Medium654
10​
Small1,036
Sheet: Sheet1

How does this sit with you?
 
lheunis said:
this is basically what i need at the end of the day if it makes any more sense then what i said

UserResultSplit
DPL4BIG4,004
DMC4BIG4,000
NRB4BIG4,000
VVM4BIG4,000
MCS4BIG4,000
CRE4BIG4,000
CON0Medium2,000
GRL4BIG4,002
GVMNMedium2,000
CCI0Medium3,455
MBD4Small800
VDM4Small800
NDC4Small800
SSD4Small800
LGR4Small800
NUD5Small800
SAY0Medium1,166
Total41,427
Click to expand...

Just seen this and don't understand - why are the amounts not equal for all the bigs, for example? If this is what you want, then what calculation have you imagined would get you there?
 
should work, but only issue is that when i apply the numbers to my users it does not total to 41,427 but to 45,259 (see below)

this is where i got stuck the whole time i was trying to figure this out

EDCCode Result Rules on Handover
RIN4 Big 5,179
DPL4 Medium 655
DMC4 Medium 655
NRB4 Medium 655
VVM4 Small 1,036
GVM5Medium 655
MCS4Small 1,036
CRE4No_Handover -
CON0Big 5,179
GRL4Small 1,036
GVMNBig 5,179
CCI0Big 5,179
STO4Small 655
MBD4Small 655
VTX4Medium 1,036
VDM4Medium 1,036
C&A4Medium 1,036
NIM4Small 655
KOE4Medium 1,036
NDC4No_Handover -
SSD4Medium 1,036
LGR4Medium 1,036
AFC4Medium 1,036
DIN4Small 655
NUD5Medium 1,036
PTP4Medium 1,036
HPA4No_Handover -
SAY0Medium 1,036
PSA0Medium 1,036
ADR4Medium 1,036
KRB0Medium 1,036
GM30Medium 1,036
SAN0Medium 1,036
LEX5No_Handover -
HTN4Small 655
45,259
 
this was previous numbers we used and basically thumb sucked it until we balanced with the total, that is why i need a formula or something that can do it so that the big have the same amount etc
 
It works fine here - have a look.

PS You got the numbers wrong. ;)
 

Attachments

  • Hard Rules Handovers AliGW.xlsx
    15.2 KB · Views: 10
nice, it works perfectly, i did a roundup on my side, probably why i got them wrong, thanks a million for your assistance i am sure i sounded mighty confused at first
will give through to my management and hopefully they are happy with that
thanks again
 
You're welcome! :)

Yes, I did find your initial request a tad confused, but I think trying to get you to grasp what I was suggesting was the main problem - I obviously wasn't clear enough. Hope the management team are happy!
 
Hello lheunis
As I think has been pointed out, your data doesn't identify anything that can be used for apportionments. You obviously start with a total (in this case a population of 41927), and we want to assign (apportion) a suitable part of this to each user. To do this we need a set of numbers that reflect the distribution pattern required, then we simply apply the number pattern to a total to create a list of numbers adding up to 41927 that reflects the proportions shown in the original number pattern.
In this instance, I looked at the way you had classified your groups and there is a correlation between the "Y" flags. Big has 7 or 8; Medium has 5 or 6; Small has 4.
So I decided to assign based on the number of "Y" flags (168 excluding 3 or less)
Take a look at my attachment, with additional data in yellow.
 

Attachments

  • Hard Rules Handovers1.xlsx
    17.1 KB · Views: 6
Hello lheunis
When I posted #17, I wasn't able to view the page 2 postings. I can't agree with using what appears to be an arbitrary 50/30/20 split, as this takes no account of the number of users in each group. For example if there was only one "Big" it would be assigned 20714. Also, it doesn't properly reflect their relative sizes (e.g. a small user gets a larger allocation than a medium one). Looking at your post #3, you pointed out that you were using the table of Y/Ns to calculate the proportions, which I think is the best approach (as illustrated in my #17 attachment), as your assigning based on the best performing users.

That said, if you are happy that what you have does the job for you thats fine, but I felt that I should point out that it isn't a robust solution to this kind of problem.
 
this is exactly what i was looking for, i made some small changes that the users with Y count below 4 does not get anything, but it works out perfectly

1000 thank you's for all the help
 
You must log in or register to reply here.
Back
Top