Log In
Register
Home
January 24, 2023
Offline
Hi All,
I have a formula that looks at a calendar week and if data in another worksheet corresponds then it returns data from the other worksheet.
This is what it looks like
=IF(MEDIAN(B$2,'owssvr'!$I2,'owssvr'!$J2)="EXTRACT SQN PW'!B$2,'owssvr'!$B2&" "&'owssvr'!$M2,0)
Now here's the query;
The formula appears to work without an issue unless an event occurs on a single day (it can't seem to see it) but
I can't find any examples of where you can use =IF(MEDIAN; all the ones I find are =MEDIAN(IF
So if I have a problem what is is; being that as I said, it works with the exception of single day events; and is there a possible solution to allow it to include single day events?
Happy but Confused
See Answer
Trusted Members
October 17, 2018
Offline
Hi,
I've never used MEDIAN but what if you include an IF condition to check if the event occurs on a single day before you calculate the MEDIAN?
I don't know if I'm making sense but that is the first thought that comes up
Moderators
January 31, 2022
Online
Please upload a file with some example events/dates and the formula you mentioned. It's otherwise difficult to determine what's right or wrong. The formula seems pretty straight-forward, otherwise.
January 24, 2023
Offline
Hi Riny,
Apologies; I had to cut significant pieces out to make it small enough to load.
Specifics:
Source Worksheet 'owssvr Active Courses
Cells I & J 12,13 & 16 (all 1 day courses)
Formula Worksheet 'Extract SQN PW'
Cells AU35 'source Cells I & J 16 - WORKS - day is first day of week
Cells AV31 & 32 'source Cells I & J 12,13 - DO NOT WORK - days are within a week
Well, I hope this is enough to go on.
TIA
Moderators
January 31, 2022
Online
Thanks Paul! I remember working on this with you earlier but find it difficult to see what you mean. I tried changing a few end dates to be the same as the start dates and can't find the problem. Perhaps I'm just looking in the wrong place.
Could you explain exactly where the problem lies?
January 24, 2023
Offline
Hi Riny,
I've highlighted the anomalies; If you look at
the 'owssvr Active Courses' worksheet you will see that
I16 and J16 are within a day and the data from B16 and M16 have been transferred to the
'Extract SQN PW' worksheet into cell AU35
This is based on the calculation
=IF(MEDIAN(AU$2,'owssvr'!$I16,'owssvr'!$J16)="EXTRACT SQN PW'!AU$2,'owssvr'!$B16&" "&'owssvr'!$M16,0)
This is a single day event
However the data for the following single day events is not transferred from the Source Worksheet 'owssvr Active Courses' to the formula worksheet 'Extract SQN PW'
the 'owssvr Active Courses' worksheet you will see that
I12 and J12 are within a day Yet the data from from B16 and M16 has not been transfered to:
'Extract SQN PW' worksheet into cell AV31
based on the calculation
=IF(MEDIAN(AV$2,'owssvr'!$I12,'owssvr'!$J12)="EXTRACT SQN PW'!AV$2,'owssvr'!$B12&" "&'owssvr'!$M12,0)
The above problem also exits in the 'Extract SQN PW' worksheet into cell AV32.
I hope this is a bit clearer.
TIA
Moderators
January 31, 2022
Online
Thanks for your patience with me perhaps being slow in understanding. But, when I look at the file you uploaded none of the events in the first sheet have start and end dates at the same day. And the dates are in columns G and H, not I and J. Furthermore, Column M is empty on that sheet.
Could you please upload a file where data actually corresponds with the above-mentioned formulas?
January 24, 2023
Offline
Riny,
My humblest apologise' and it is I that must thank you for your patience, I uploaded the wrong file (again I will blame age, for lack of anything else to blame).
Hopefully this file will make the issue clearer.
Regards
Paul
Moderators
January 31, 2022
Online
OK, clearer now. The MEDIAN function was designed to see iff the date in row 2 on the EXTRACT sheet would fall between the Start and End dates. in the red marked cells the date in row 2 is simply not the median. For instance on row 31, the median of 20 Nov, 23 Nov and 23 Nov equals 23 Nov with is not equal to 20 Nov. The fact that it works on column AU is just because all three dates are the same. Would need to re-think how to tackle that issue.
"I'll be back" !
Moderators
January 31, 2022
Online
Okay, now it's fixed but it aint pretty. You need to add some checks to see if the event spans less then a and doesn't include a the Monday of the week. I recall that you are working on an old Excel version, aren't you? Otherwise it could be done better.
Note I cut off the bottom part of the schedule to limit the file size.
Answers Post
January 24, 2023
Offline
Riny,
Thank you so much, you are indeed a Gentleman and Scholar. I see what you mean by a bit ugly; and yes I am on an old Excel version. However, I have been told we will be updating to Windows 11 and M365 in 2024 so fingers crossed; and I may just do it all manually until we get there: i will have to think on it as my workload is rapidly filling up for urgent actions whilst everyones away on holidays.
Anyway, Thanks again and a Very Merry Christmas and Happy New Year to you and yours.
Paul
1 Guest(s)
