Question description:
This user has given permission to use the problem statement for this
blog.
I need to count scores less than 98 from last 5 months. After each month, a new month is added so it is a dynamic table range. Could you please suggest me an offset formula, not so complicated one
Solved by K. L. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
20/02/2018 - 10:42
Hi
User
20/02/2018 - 10:42
need help with offset formula. Can u check my earlier communication and let me know if it was resolved
Excelchat Expert
20/02/2018 - 10:43
Hi..
User
20/02/2018 - 10:43
email address zorawar81@yahoo.co.in
Excelchat Expert
20/02/2018 - 10:43
We do not send out emails
Excelchat Expert
20/02/2018 - 10:43
we will sort it out here right now..
Excelchat Expert
20/02/2018 - 10:43
Do you have a excel
Excelchat Expert
20/02/2018 - 10:43
?
User
20/02/2018 - 10:43
so the inquiry under that email is not resolved, I would presume
User
20/02/2018 - 10:44
The table I've pasted shows months in one row and scores in other row. I need an offset formula to count how many cells from past 5 cells are below 98%
Excelchat Expert
20/02/2018 - 10:45
If you had come earlier youcan contact the support to see if the other expert found a solution
User
20/02/2018 - 10:45
I tried something like the below but it doesn't work
User
20/02/2018 - 10:45
=OFFSET($D$17,0,1,COUNTIF(D17:M17, "<0.98"),-5)
Excelchat Expert
20/02/2018 - 10:46
You need to know how many cel'l's in the matix are below 98% right?
User
20/02/2018 - 10:46
This is a dynamic table and a new month will be added every month so the last 5 columns would change every month
User
20/02/2018 - 10:46
yes, how many cells below 98%
User
20/02/2018 - 10:46
but normal countif won't work as the table will add new column every month
User
20/02/2018 - 10:47
it needs to be mix of offset and countif
Excelchat Expert
20/02/2018 - 10:47
and you would need only the last 5 month's not the previous?
User
20/02/2018 - 10:47
yes
Excelchat Expert
20/02/2018 - 10:47
give me a min
User
20/02/2018 - 10:47
sure, thanks for ur time
Excelchat Expert
20/02/2018 - 10:49
just checking so next when Aug is added you need the formula to work for aug,july,june,may n april right?
User
20/02/2018 - 10:49
correct
Excelchat Expert
20/02/2018 - 11:00
the offset will not work.. looking into other formulas
Excelchat Expert
20/02/2018 - 11:03
hELLO ARE YOU THERE
Excelchat Expert
20/02/2018 - 11:03
Sorry caps lock on
User
20/02/2018 - 11:03
yes
User
20/02/2018 - 11:03
ok
Excelchat Expert
20/02/2018 - 11:04
few more min
User
20/02/2018 - 11:04
sure
Excelchat Expert
20/02/2018 - 11:17
[Uploaded an Excel file]
Excelchat Expert
20/02/2018 - 11:17
hi..
Excelchat Expert
20/02/2018 - 11:17
you there
Excelchat Expert
20/02/2018 - 11:17
its a simple formula =COUNTIF(LastMonth,"<98%")
Excelchat Expert
20/02/2018 - 11:18
ut created a namerange for the calculation purpose
Excelchat Expert
20/02/2018 - 11:18
Hello
Excelchat Expert
20/02/2018 - 11:18
Hi are you there
Excelchat Expert
20/02/2018 - 11:19
Hello
Excelchat Expert
20/02/2018 - 11:19
the formula is in K13
Excelchat Expert
20/02/2018 - 11:19
If you are there please respond
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.
