Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.