Question description:
This user has given permission to use the problem statement for this
blog.
I have a table showing month, agent names, and scores. What I want is calculate scores which are less than 98% from last 5 months for each agent. If the agent has scores for only past 4 months and past 5th month was empty, the formula should look for score before the 5th month to count only the last 5 columns with values
Solved by I. Q. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
21/02/2018 - 09:55
hi welcome to got it pro
User
21/02/2018 - 09:55
Hi I have added the table
Excelchat Expert
21/02/2018 - 09:56
sure thanks
Excelchat Expert
21/02/2018 - 09:56
let me have a look pls
User
21/02/2018 - 09:56
sure
User
21/02/2018 - 09:57
In column L (highlighted), I want to count for no. of times the agent scored below 98% from past 5 months. If new month is added, the count should be still from past 5 months
Excelchat Expert
21/02/2018 - 09:57
yes pls
User
21/02/2018 - 09:58
Moreover, the count should ignore the blank cells
User
21/02/2018 - 09:58
count only past 5 weeks below 98% where we have scores
Excelchat Expert
21/02/2018 - 09:58
i got you give me a moment pls
Excelchat Expert
21/02/2018 - 09:58
i wil work for u
User
21/02/2018 - 09:58
sure
User
21/02/2018 - 09:58
thanks
Excelchat Expert
21/02/2018 - 09:59
i have downloaded excel sheet,..
Excelchat Expert
21/02/2018 - 09:59
thanks
Excelchat Expert
21/02/2018 - 10:04
hi dear i just did working for u,..are you able to see that
Excelchat Expert
21/02/2018 - 10:04
pls
User
21/02/2018 - 10:05
just a min plz
Excelchat Expert
21/02/2018 - 10:05
sure
User
21/02/2018 - 10:06
could u plz add the count to the last column
Excelchat Expert
21/02/2018 - 10:06
yes pls
User
21/02/2018 - 10:06
After Feb. I need to check if I make a cell blank, will it go and count the next column
Excelchat Expert
21/02/2018 - 10:07
yes pls it will go
User
21/02/2018 - 10:07
Now, the count shows '4'as the answers but it should show 3 as there are 3 values less than 98% in past 5 months
Excelchat Expert
21/02/2018 - 10:07
do u want me to create a templete for u
Excelchat Expert
21/02/2018 - 10:08
i will do it for u
User
21/02/2018 - 10:09
thanks
User
21/02/2018 - 10:09
but the answers are incorrect for now.
Excelchat Expert
21/02/2018 - 10:09
they will be right
User
21/02/2018 - 10:09
I mean when u added the count column after 'FEb', it is showing incorrect values
Excelchat Expert
21/02/2018 - 10:09
yes i am fixing it
User
21/02/2018 - 10:10
sure, thanks. Also, the formula should only count last 5 values if all of them have data. If all the last 5 values do not have data, then formula should extend to the last 5 values having data
User
21/02/2018 - 10:10
In short, it should ignore the blank cells
Excelchat Expert
21/02/2018 - 10:10
Yes pls
User
21/02/2018 - 10:11
Also, everytime, a new column is added 'March', April, etc, the formula should change to count to new last 5 columns
Excelchat Expert
21/02/2018 - 10:11
Yes Pls
User
21/02/2018 - 10:11
thanks
Excelchat Expert
21/02/2018 - 10:12
can i do working on my sheet Pls
Excelchat Expert
21/02/2018 - 10:12
and then upload
Excelchat Expert
21/02/2018 - 10:12
so that you can down load
User
21/02/2018 - 10:12
sure, thanks
Excelchat Expert
21/02/2018 - 10:13
did u just saw week 2 working
User
21/02/2018 - 10:14
no, it shows incorrect results
Excelchat Expert
21/02/2018 - 10:15
are we talking week 2
User
21/02/2018 - 10:15
yes
User
21/02/2018 - 10:15
Cell N7 shows value as 1 but there are 3 values which are less than 98% from past 5 weeks
User
21/02/2018 - 10:16
Similarly all other cells except N2 also show incorrect values
Excelchat Expert
21/02/2018 - 10:17
dear let me explain you,..
User
21/02/2018 - 10:18
sure
Excelchat Expert
21/02/2018 - 10:18
week 2 is taking data from Jan to May
Excelchat Expert
21/02/2018 - 10:18
watch this
Excelchat Expert
21/02/2018 - 10:18
5 Month
Excelchat Expert
21/02/2018 - 10:20
in week 2 Just press F2
User
21/02/2018 - 10:20
is it the standard countif formula or anything other formula is added?
Excelchat Expert
21/02/2018 - 10:21
nope for numeric value = countif is the Perfects
User
21/02/2018 - 10:22
but when new column is added, it is not taking the last 5 columns.
User
21/02/2018 - 10:22
the formula need to shift to last 5 columns everytime a month is added
User
21/02/2018 - 10:22
standard countf will not shift the range and will work on fixed table and not on dynamic table
Excelchat Expert
21/02/2018 - 10:22
yes for that you have to create a new column and copy paste every five month
Excelchat Expert
21/02/2018 - 10:23
or you can also do is create a templete for 1 year
User
21/02/2018 - 10:23
that I already know. I was looking for a formula which counts the last 5 columns. I heard about offset formula and that can work
User
21/02/2018 - 10:23
if you can check that - combine offset formula with countif
Excelchat Expert
21/02/2018 - 10:24
sure givem me a momentspls
Excelchat Expert
21/02/2018 - 10:26
give me a moment pls
Excelchat Expert
21/02/2018 - 10:26
al most done
User
21/02/2018 - 10:27
please make sure that it only counts the last 5 cells which have data and should extend to 6th or 7th cell if 1 or 2 cells in last 5 cells are blank
Excelchat Expert
21/02/2018 - 10:27
sure
User
21/02/2018 - 10:34
will u be able to do it. I think it will be in best interest of our time that we disconnect the chat if you are not well versed with offset and countif functions together
User
21/02/2018 - 10:34
Sorry for thinking out loud
Excelchat Expert
21/02/2018 - 10:35
dear,. i can add the Offset for mulae however that will not resolve your purropse
User
21/02/2018 - 10:36
ok, so will u able to provide an alternative which serves the purpose
Excelchat Expert
21/02/2018 - 10:38
looking as per this report and data that u r looking at ,..you req a templete
Excelchat Expert
21/02/2018 - 10:38
for no of weeks you reuire
Excelchat Expert
21/02/2018 - 10:38
or else Macro will be and alternative for u
User
21/02/2018 - 10:39
I would prefer a formula but if that doesn't work, macro will work too
Excelchat Expert
21/02/2018 - 10:40
nope in macro,. you can give condition,..
User
21/02/2018 - 10:41
I don't have any experience with macro so that would be tough to alter or edit, if required in future
Excelchat Expert
21/02/2018 - 10:41
on got it pro you can get MACRO expert as well
Excelchat Expert
21/02/2018 - 10:41
ohh i see dear
Excelchat Expert
21/02/2018 - 10:42
for the mean time you an try using the count if function,..that will resolve ur purpose
User
21/02/2018 - 10:43
but I need to add it again every month. Moreover, I want to count the last 5 values but countif will count not extend the range if any of the last 5 cells do not have a value
Excelchat Expert
21/02/2018 - 10:44
yes agreed,..
Excelchat Expert
21/02/2018 - 10:44
however all u will require is few copy and paste and chnage of rages
User
21/02/2018 - 10:45
k, I will try other alternatives. thanks
Excelchat Expert
21/02/2018 - 10:45
sure good luck with ur work,.. TC
Excelchat Expert
21/02/2018 - 10:45
thanks for contacting got it pro
Excelchat Expert
21/02/2018 - 10:45
bye
User
21/02/2018 - 10:46
bye
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.
