Question description:
This user has given permission to use the problem statement for this
blog.
Need help putting together formula where it counts the number of sales but only if the average annual premium is above $360.
So a sales = Sum(N:2:N:48)/360....but not greater than COUNTIF(N:2:N:48) ""
Solved by O. J. in 43 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
26/01/2018 - 03:56
Hello :)
User
26/01/2018 - 03:56
hey
User
26/01/2018 - 03:57
Solving for the yellow box, counting number of life insurance policy sales. But to count as a sale the average premium sold the month must be $360 annual or greater
User
26/01/2018 - 03:57
so someone can't sell 4 $120 annual premium policies and get 4 sale counts
User
26/01/2018 - 03:58
they would bet 1 sale count because total premium is $460. Have to have $360 in annual premium to count
Excelchat Expert
26/01/2018 - 03:58
can you please create
Excelchat Expert
26/01/2018 - 03:58
mplate
Excelchat Expert
26/01/2018 - 03:58
i need to see columns
User
26/01/2018 - 03:58
i pasted one, do you see
Excelchat Expert
26/01/2018 - 03:58
No
User
26/01/2018 - 03:58
colums K-M
User
26/01/2018 - 03:59
down to row 49
Excelchat Expert
26/01/2018 - 03:59
i see now
Excelchat Expert
26/01/2018 - 03:59
ait please
Excelchat Expert
26/01/2018 - 03:59
so please fill as an example
Excelchat Expert
26/01/2018 - 03:59
row 2
Excelchat Expert
26/01/2018 - 03:59
where will be your sales?
User
26/01/2018 - 04:00
thats what we are solving for in yellow box
Excelchat Expert
26/01/2018 - 04:00
so average annual sales are in column E
Excelchat Expert
26/01/2018 - 04:00
yes?
Excelchat Expert
26/01/2018 - 04:00
or in D?
User
26/01/2018 - 04:00
column E is commision amount
User
26/01/2018 - 04:01
D is the amount of annual premium
User
26/01/2018 - 04:01
so in my scenario they should only get 1 sale count
Excelchat Expert
26/01/2018 - 04:01
so then the rows are days?
Excelchat Expert
26/01/2018 - 04:01
why?
User
26/01/2018 - 04:01
there are two sales entries, but average premium per policy is not at or above $360
Excelchat Expert
26/01/2018 - 04:01
because D2 and D3 Are aboce $360?
Excelchat Expert
26/01/2018 - 04:01
above*
User
26/01/2018 - 04:02
rows are life insurance sales
User
26/01/2018 - 04:02
I want someone getting sale count only if average premium is $360 annual per sale
User
26/01/2018 - 04:02
but they can't get more sale counts then applications sold
Excelchat Expert
26/01/2018 - 04:02
just to clarify last time please :)
Excelchat Expert
26/01/2018 - 04:03
if D2/360 is above $360 then count 1
Excelchat Expert
26/01/2018 - 04:03
yes?
User
26/01/2018 - 04:03
yes, but need to account for the other application premiums such as D3
User
26/01/2018 - 04:03
then D4, D5, D6 etc...
User
26/01/2018 - 04:04
as an average ............so average of D2:D49
User
26/01/2018 - 04:04
but sales can't be more than applications
Excelchat Expert
26/01/2018 - 04:05
ah ok
User
26/01/2018 - 04:05
so if someone sells a big $5000 annual premium, they don't end up with a lot of sale counts
User
26/01/2018 - 04:06
but if they only sell small $100 annual premiums they don't get credit on each application, only at $360 annual premium do they get a count
Excelchat Expert
26/01/2018 - 04:06
so if first are 400 and 300 then 3rd is 5000
Excelchat Expert
26/01/2018 - 04:06
output will be 1 for count
Excelchat Expert
26/01/2018 - 04:06
or 3?
User
26/01/2018 - 04:06
no it would be 3
Excelchat Expert
26/01/2018 - 04:06
ok
Excelchat Expert
26/01/2018 - 04:06
let me try now
User
26/01/2018 - 04:07
if first 100 and second 100 and third 200... only 1 count
Excelchat Expert
26/01/2018 - 04:11
why?
Excelchat Expert
26/01/2018 - 04:11
those average is 133
User
26/01/2018 - 04:11
total of 400.
Excelchat Expert
26/01/2018 - 04:11
and this is below 360
User
26/01/2018 - 04:11
get count at 360 annual premium
User
26/01/2018 - 04:11
but no more than the amount of actuall applications
User
26/01/2018 - 04:12
sorry if I confused
Excelchat Expert
26/01/2018 - 04:12
really i m totally confused
Excelchat Expert
26/01/2018 - 04:12
:(
User
26/01/2018 - 04:12
lets start over, im sorry
Excelchat Expert
26/01/2018 - 04:12
ok please
User
26/01/2018 - 04:13
so they get count for every 360 of premium
User
26/01/2018 - 04:13
but no more than the amount of actual applications
User
26/01/2018 - 04:13
when I say "count" I'm referring to "a sale count"
Excelchat Expert
26/01/2018 - 04:13
ok
Excelchat Expert
26/01/2018 - 04:15
please look at column F
Excelchat Expert
26/01/2018 - 04:15
is it correct?
Excelchat Expert
26/01/2018 - 04:15
400 > 360 so the count = 1
Excelchat Expert
26/01/2018 - 04:15
300 < 360 the count = 0
Excelchat Expert
26/01/2018 - 04:15
and etc.
Excelchat Expert
26/01/2018 - 04:15
yes?
User
26/01/2018 - 04:18
yes, sort of. because the sum of column D = 5,761 and there are 4 applications (5,761/4 = 1,440 avg premium per sale).. they would get 4 sale counts
User
26/01/2018 - 04:18
the average premium per sale is 360 or higher
User
26/01/2018 - 04:18
larger sales can lift the average for lower sales
User
26/01/2018 - 04:19
so they get a sale count for every $360 of annual premium but no more than the number of actual applications
Excelchat Expert
26/01/2018 - 04:22
please look
User
26/01/2018 - 04:23
ok
Excelchat Expert
26/01/2018 - 04:23
verage from D2 to D6 is above 360
Excelchat Expert
26/01/2018 - 04:23
he count will be 5
Excelchat Expert
26/01/2018 - 04:23
but the averagefrom D2 to D7 is less 360
Excelchat Expert
26/01/2018 - 04:23
then count remains 5 yes?
User
26/01/2018 - 04:24
thats correct
User
26/01/2018 - 04:25
does it work if all the premiums are $1,000 per year
Excelchat Expert
26/01/2018 - 04:25
yes
User
26/01/2018 - 04:25
to now equal 8 count/sale
User
26/01/2018 - 04:29
so it's basically... Count how many times $360 divides into sum of Annual Premium, but no more than number of appllications
Excelchat Expert
26/01/2018 - 04:30
guess i ve solved it
Excelchat Expert
26/01/2018 - 04:30
pls
User
26/01/2018 - 04:30
they will only enter annual premium if applicaion complete.
Excelchat Expert
26/01/2018 - 04:30
1 min pls
Excelchat Expert
26/01/2018 - 04:31
ready :)
Excelchat Expert
26/01/2018 - 04:31
ase check
Excelchat Expert
26/01/2018 - 04:31
please*
Excelchat Expert
26/01/2018 - 04:31
F and G are working columns dont delete them
Excelchat Expert
26/01/2018 - 04:31
just drag them down
User
26/01/2018 - 04:34
what does just drag down mean
User
26/01/2018 - 04:34
just select those and copy
Excelchat Expert
26/01/2018 - 04:35
i put formulas
Excelchat Expert
26/01/2018 - 04:35
yes
Excelchat Expert
26/01/2018 - 04:35
select the last one and copy down
Excelchat Expert
26/01/2018 - 04:35
i did
Excelchat Expert
26/01/2018 - 04:36
please don't forget to rate our session, Sir
Excelchat Expert
26/01/2018 - 04:36
once it ended
User
26/01/2018 - 04:37
i will for sure
User
26/01/2018 - 04:38
ok thank you!!! looks great!
Excelchat Expert
26/01/2018 - 04:38
thank you to for your patience!
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.
