Question description:
This user has given permission to use the problem statement for this
blog.
Need a formula that counts (1,2,3,4 ect..in cell M21) a range of cells, N4:N20, which are annual premium values. But I only want to add a sale count if the average monthly premium is above $30 per sale. So if someone has 4 sales but they don't equal a total of $120/mth ($30 x 4 = $120)... they would only get a count of 3 sales.
Solved by S. E. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
26/01/2018 - 02:49
Thanks for choosing Got It Pro
Excelchat Expert
26/01/2018 - 02:50
do you have a file with data
User
26/01/2018 - 02:51
i don't that I can provide
Excelchat Expert
26/01/2018 - 02:51
ok
User
26/01/2018 - 02:51
right now my formula is using COUNTIF if there are any values in the annual premium
User
26/01/2018 - 02:51
so they only enter annual premium if sold and issued
User
26/01/2018 - 02:52
I need a quality assurance factor, where the sales have to average over $30/mth to count
Excelchat Expert
26/01/2018 - 02:53
what does N4:N20 contains
Excelchat Expert
26/01/2018 - 02:53
the premium in amount?
User
26/01/2018 - 02:53
Annual premium amounts
Excelchat Expert
26/01/2018 - 02:53
ok
Excelchat Expert
26/01/2018 - 02:53
so if the premium is below $30/month
Excelchat Expert
26/01/2018 - 02:54
then it should not be counted
User
26/01/2018 - 02:54
correct
User
26/01/2018 - 02:54
or $360 annual
User
26/01/2018 - 02:54
correct
Excelchat Expert
26/01/2018 - 02:54
ok so annually $360
Excelchat Expert
26/01/2018 - 02:54
ok
User
26/01/2018 - 02:55
yeah so if there are 4 sales but they don't add up under $360x12 then the count is 3
User
26/01/2018 - 02:55
i guess i should have left out word "don't" above
Excelchat Expert
26/01/2018 - 02:55
so can I assume that if the premium is less than $360 then the count should not be considered
User
26/01/2018 - 02:56
on average, yes
User
26/01/2018 - 02:56
but if someone has a sale of 300 and 3500, then they should get a count of 2
Excelchat Expert
26/01/2018 - 02:56
so for example in column N if we have 300, 400, 500 & 600
Excelchat Expert
26/01/2018 - 02:56
then it shuold count 3
User
26/01/2018 - 02:56
not less because average is above 360 per policy
User
26/01/2018 - 02:57
but not more because they had a big sale
Excelchat Expert
26/01/2018 - 02:57
so you mean the average
Excelchat Expert
26/01/2018 - 02:57
okay
Excelchat Expert
26/01/2018 - 02:57
so the annual premium is in N4:N20, right
User
26/01/2018 - 02:57
your example would be 4 sales
Excelchat Expert
26/01/2018 - 02:58
you have to give me the exact number of rows
Excelchat Expert
26/01/2018 - 02:58
in which the anual premium are there
Excelchat Expert
26/01/2018 - 02:58
then only we can average, right
User
26/01/2018 - 02:58
the sales all count as 1 sale only, as long as the average premium per sale is at least $360
Excelchat Expert
26/01/2018 - 02:59
I got it
Excelchat Expert
26/01/2018 - 02:59
so i need the exact number of rows
User
26/01/2018 - 02:59
exact is N4:N50
User
26/01/2018 - 02:59
46 rows
Excelchat Expert
26/01/2018 - 03:00
ok
Excelchat Expert
26/01/2018 - 03:02
working on it
Excelchat Expert
26/01/2018 - 03:03
can in the annual premium, some rows could be blank also
User
26/01/2018 - 03:03
yes
Excelchat Expert
26/01/2018 - 03:04
ok
User
26/01/2018 - 03:04
they might have a sale not issue so leave annual premium cell blank but have one underneath or above that issues and would have annual premium
Excelchat Expert
26/01/2018 - 03:08
so it should only count if it is above average
Excelchat Expert
26/01/2018 - 03:08
not equal to and above, right
User
26/01/2018 - 03:08
equal to and above
User
26/01/2018 - 03:08
so 360 is good
Excelchat Expert
26/01/2018 - 03:08
ok
User
26/01/2018 - 03:08
so >359 good
Excelchat Expert
26/01/2018 - 03:09
let me change the formula
Excelchat Expert
26/01/2018 - 03:10
uploading the file
[Uploaded an Excel file]
Excelchat Expert
26/01/2018 - 03:10
please check if this is what you are looking for
User
26/01/2018 - 03:12
it should be 3 sales because there are 3 sales and the average annual premium is 400
Excelchat Expert
26/01/2018 - 03:12
you mean all are above 360, right
User
26/01/2018 - 03:13
the sales count if average premium checks out to 360 or higher
Excelchat Expert
26/01/2018 - 03:14
ok got it
Excelchat Expert
26/01/2018 - 03:14
let me tweak the formula
User
26/01/2018 - 03:14
so a sale under 360 can still count if the other sales are higher and bring average up
Excelchat Expert
26/01/2018 - 03:16
do you want the count when the average is premium is above the total average
Excelchat Expert
26/01/2018 - 03:16
or when it is above 360
Excelchat Expert
26/01/2018 - 03:16
the first formula does that
Excelchat Expert
26/01/2018 - 03:17
so 2 were greater than the average of the 3
Excelchat Expert
26/01/2018 - 03:17
so it counted 2
Excelchat Expert
26/01/2018 - 03:18
if you want I can change it to count all that are above 360
Excelchat Expert
26/01/2018 - 03:18
or do you want to count if average is greater than 360
User
26/01/2018 - 03:19
Its the average and the sale amount
User
26/01/2018 - 03:19
because I don't want a huge sale of $4000 annual and one that is $200 annual and they get more than 2 sale counts
Excelchat Expert
26/01/2018 - 03:19
is sale amount same as premium
User
26/01/2018 - 03:20
it would only be 2 sales in that scenario
User
26/01/2018 - 03:20
can't get more sale amounts than actual sales
Excelchat Expert
26/01/2018 - 03:20
so in this case they should get 1 count?
User
26/01/2018 - 03:20
but if you have 4 sales at $100 annual, you only get credit for 1 sale
Excelchat Expert
26/01/2018 - 03:21
I am sorry but you will have to define the criteria more clearly
User
26/01/2018 - 03:21
sale is just a submitted application, so different than premium, but I don't consider a sale a sale if not worth at least 360 annual
Excelchat Expert
26/01/2018 - 03:21
for me to come up with the correct formula
Excelchat Expert
26/01/2018 - 03:22
so sale is a separate field?
User
26/01/2018 - 03:22
can you paste what you have so far... i might be able to run with it
Excelchat Expert
26/01/2018 - 03:22
can you upload a small sample of your data
User
26/01/2018 - 03:23
yeah hangon
User
26/01/2018 - 03:27
can you see what I just pasted
Excelchat Expert
26/01/2018 - 03:27
yes
User
26/01/2018 - 03:27
the yellow box at the bottom to the left is what we are solving for
User
26/01/2018 - 03:28
ignore the colum with the #ref and the yellow box at bottom right
Excelchat Expert
26/01/2018 - 03:29
where is the sale and premium amount
User
26/01/2018 - 03:29
sale amount is the bottom left box
User
26/01/2018 - 03:29
premium amount is blank at this time but there is annual premium header
User
26/01/2018 - 03:30
annual premiums would go in there. right now the sales are just counted if anything is in commission column
Excelchat Expert
26/01/2018 - 03:30
sale amount is in which cell
User
26/01/2018 - 03:30
A50
Excelchat Expert
26/01/2018 - 03:30
ok
User
26/01/2018 - 03:30
I assumed we need to move to count the annual premium column instead of commission column
Excelchat Expert
26/01/2018 - 03:31
yes I believe you wanted the count of the premium
Excelchat Expert
26/01/2018 - 03:32
what average should i take
Excelchat Expert
26/01/2018 - 03:33
the premium, right
Excelchat Expert
26/01/2018 - 03:33
or commission
User
26/01/2018 - 03:33
yes
User
26/01/2018 - 03:33
premium
User
26/01/2018 - 03:33
not commission
Excelchat Expert
26/01/2018 - 03:34
is the condition correct in the formula
User
26/01/2018 - 03:35
no it would be 4 sales
Excelchat Expert
26/01/2018 - 03:35
what is there in column A
Excelchat Expert
26/01/2018 - 03:36
how?
Excelchat Expert
26/01/2018 - 03:37
you give me some numbers to enter in col D
User
26/01/2018 - 03:37
there are 4 sales, and the average premium is $450 per sale
Excelchat Expert
26/01/2018 - 03:37
so it would be 2 right
User
26/01/2018 - 03:37
when you add up the 4 annual premiums and / 4 you get 450
Excelchat Expert
26/01/2018 - 03:38
300 & 400 are below the average, right
User
26/01/2018 - 03:38
no they get the sale count....AS LONG as they average premium's check out to 360 on average
Excelchat Expert
26/01/2018 - 03:38
so shall I change the average to 360 in formula
User
26/01/2018 - 03:39
yeah but the average premium amount is used to validate the sale amounts as good
User
26/01/2018 - 03:39
yeah 360 needs to be in there
Excelchat Expert
26/01/2018 - 03:39
is commission same as sale
User
26/01/2018 - 03:41
its almost like the formula needs to count the how many times total annual premium/360 but not more than countif:(N3:N49)
Excelchat Expert
26/01/2018 - 03:42
sorry, did not get what you are saying
User
26/01/2018 - 03:42
=SUM(N:4:N:49)/360 but not more than COUNTIF(N:4:N49)
User
26/01/2018 - 03:43
something like that
Excelchat Expert
26/01/2018 - 03:43
ok let me try
Excelchat Expert
26/01/2018 - 03:44
what should be the criteria for counting?
User
26/01/2018 - 03:45
something in the cell N:4:N:49 other than $0.00
Excelchat Expert
26/01/2018 - 03:45
which number should come there
User
26/01/2018 - 03:47
a count (1,2,34 etc..) on how many cells have value different than $0.00
User
26/01/2018 - 03:48
but count is reduced if the average annual premium is less than $360 per policy.
User
26/01/2018 - 03:49
formula takes the lower of values is N:4-N:49 and how many times 360 goes into the sum of N:4-N:49
Excelchat Expert
26/01/2018 - 03:49
please create a new session
Excelchat Expert
26/01/2018 - 03:49
he time is up
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
