Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating the first day of the week
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating the first day of the week
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating the first day of the week
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating the first day of the week
On Dec 29, 7:14*am, barry houdini wrote:
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating the first day of the week
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculating the first day of the week
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto calculate day of week to week of the year (not as serial) | Excel Discussion (Misc queries) | |||
calculating a date using the day of the week as a starting point? | Excel Worksheet Functions | |||
Calculating a colmun to total a 40 hour work week | Excel Worksheet Functions | |||
Calculating total work week hours | Excel Worksheet Functions | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |