Calculating the first day of the week

[jimbo]

I take my medicine for diabetes three times a week, every other day,
MWF. Sometimes my blood is drawn on the first day, sometimes the
second day, and sometimes on the third day. I have a different
calculation for each of those days, because no matter how you cut it
on Monday I haven't taken medicine for two days, on Wednesday I just
took the medicine the day before, and on Friday, I've been taking it
every other day for the whole week. If they take my blood on Monday it
will be slightly higher than Wednesday, and Friday will be the lowest
of the three. So I have three different calculations to make the
results equivalent, no matter what day they collect my blood.

M glucose = blood glucose - 30 (Beginning of the week calculation
(BoW))
W glucose = blood glucose -5 Middle of the week (MoW)
F glucose = blood glucose + 10 (end of the week (EoW)

My friends want me to run their blood glucose levels through this
"calculator", but they send me their medicine schedules and it's TThS,
or STTh, or sometimes they get mixed up and send me the day with the
gap in the middles, like FMW, and then what day their blood was
collected. So each time I have to 1) figure out the first day of the
week (it could be any day, even Sunday), then 2) apply the right
calculation.

I thought that in Excel there might be a way if I have a row of
schedules and another row of blood collection days, and the list of
three calculations, and determine the right equation to apply (BoW,
MoW, or EoW). For example

Schedule collection day
TThS Th
ST Th

 

[jimbo]

I didn't get to finish. Here's the whole post...

I take my medicine for diabetes three times a week, every other day,
MWF.  Sometimes  my blood is drawn on the first day, sometimes the
second day, and sometimes on the third day. I have a different
calculation for each of those days, because no matter how you cut it
on Monday I haven't taken medicine for two days, on Wednesday I just
took the medicine the day before, and on Friday, I've been taking it
every other day for the whole week. If they take my blood on Monday it
will be slightly higher than Wednesday, and Friday will be  the lowest
of the three.   So I have three different calculations to make the
results equivalent, no matter what day they collect my blood.

M glucose = blood glucose  - 30 (Beginning of the week calculation
(BoW))
W glucose = blood glucose -5  Middle of the week (MoW)
F glucose = blood glucose + 10  (end of the week (EoW)

My friends want me to run their blood glucose levels through this
"calculator", but they send me their medicine schedules and it's TThS,
or STTh, or sometimes they get mixed up and send me the day with the
gap in the middles, like FMW, and then what day their blood was
collected.  So each time I have to 1) figure out the first day of the
week (it could be any day, even Sunday), then 2) apply the right
calculation.

I thought that in Excel there might be a way if I have a row of
schedules and another row of blood collection days, and the list of
three calculations, and determine the right equation to apply (BoW,
MoW, or EoW).  For example

Schedule   collection day
TThS                Th MoW
STTh Th MofW

WSM S BoW
MW F F EoW
FMW F EoW

then, if I had the list somewhere on the page, then I could say
something like IF BoW, then calculate BoW calculation automatically.

chedule collection day

TThS Th MoW blood glucose -5

STTh Th MofW blood glucose -5
WSM S BoW glucose = blood glucose - 30
MW F F EoW glucose blood glucose +10
FMW F EoW glucose blood glucose +10

As you can see, without some help, it can be very confusing figuring
out the right day, so I can apply the right formula. There always
seems t o be a solution that in Excel that I didn't realize was there
for me. Thanks for your help

 

[barry houdini]

Hello jimbo,

Perhaps I'm missing something but it's not clear to me how you get EoW
or BoW or MoW from that data, can you explain the logic? Also what day
is "S", Saturday or Sunday? I only see "S", how are you distinguishing
between them, or is one of those days never used?

 

[jimbo]

Hello jimbo,

Perhaps I'm missing something but it's not clear to me how you get EoW
or BoW or MoW from that data, can you explain the logic? Also what day
is "S", Saturday or Sunday? I only see "S", how are you distinguishing
between them, or is one of those days never used?

You are correct, I forgot to identify Sunday. Plese assume all "S"
refer to Saturday. If you say that MWF represents a typical schedule,
then Monday is the first day of the week and Wedensday is in the
middle, and Friday is the end. The day representing beginning of the
week (BoW) always has two days before it, regardless how the schedule
is written

Sunday Monday Tuesday Wednesday Thursday
Friday Saturday
MWF X
X X
Here, the first day is Monday becasue two days are before Monday,
Saturday and Sunday. Wednesday only has one day in front, Tuesday,and
Friday as well. But Friday has two days after it, so it is the end of
the week, and Wednesday is the middle of the week

Sunday Monday Tuesday Wednesday Thursday
Friday Saturday
STTh
X X X
Here, since Tuesday is the day with two days before it, it is the BoW,
and Saturday has two days after, so it is the EoW, and Thirsday is the
MoW.

Sunday Monday Tuesday Wednesday Thursday
Friday Saturday
FMW X
X X
Here, since Monday is the day with two days before it, it is the BoW,
and Friday has two days after, so it is the EoW, and Wednesday is the
MoW, so even though it's FMW, it's the same as MWF

So, first to know is the schedule, then the day their blood is
collected. Once it is determined whether it is BoW, MoW, or EoW, then
the calculation can be made. So in the last example, FMW, if the
blood is collected on a .Wednesday, it is the middle of the week: so
the calculation is (blood glucose -5), so if the glucose was 100, the
recalculated gucose would be 95. Had the same 100 been drawn on
Monday, the recalculated blood glucose would be 70, and if it was on
Friday (EoW), the recalulated blood glucose would be 110.

So the data elements are

Schedule Blood Colection Day Blood Glucose Recalulated
blood glucose
MWF M
100 70
FMW W
85 80
STTh S
120 90

I hope that clarifies the problem. The primary issue is that the
schedule comes in a variety of combinations of three, but it has to be
standardized and the BoW, MoW, and EoW designation has to occur for
the right calcuation to occur.

Thanks for your help

JP

 

[barry houdini]

Hello Jimbo,

I think the simplest way to go would be to construct a table. There
are only 7 possible weekday combinations, even if you include Saturday
and Sunday, i.e. MWF,TThSa,WFSu,ThSaM,FSuT,SaMW and SuTTh. Even if
these can be listed in any order there are still only 42 so......

If you list all 42 possibilities in A2:A43 and then in the top row
B1:H1 list the days, Su, M, T, W, Th, F, Sa

Then in the relevant cells put B, M or E, e.g. if A2 is MWF put B in
C2, M in F2 and E in G2, do the same for all rows and leave all the
other cells blank

Now assuming you have a specific schedule in J2, blood collection in
K2, Blood Glucose in L2 then you can use this formula in M2 to get the
recalculated level

=LOOKUP(VLOOKUP(J2,A$2:H$43,MATCH(K2,A$1:H$1,0),0),{"B","E","M"},
{-30,10,-5})+L2

Copy the formula down for more data in row 3 onwards

The formula will give #N/A if the schedule shown in J2 is invalid or
if the day shown in K2 isn't in that schedule. You could probably
expand the formula to give some different error messages but you might
like to start with the basic version.

If you want you could have the table on a separate sheet or somewhere
over to the side so that it isn't dispayed, I suggest you start where
I put it and cut and paste the table somewhere else later. References
in the formula will adjust accordingly

Post back if you can't make that work

 

[barry houdini]

Just to add......

In the formula I use {"B","E","M"}

These need to be in that order (alphabetical) for the formula to work.

You migt want to split the formula in two to show B, E or M in M2,
i.e.

=VLOOKUP(J2,Sheet2!A2:H43,MATCH(K2,Sheet2!A1:H1,0),0)

and to calculate the revised figure in N2 with

=IF(M2="B",-30,IF(M2="M",-5,IF(M2="E",10)))+L2