T Nation

Any Excel Function Experts Here?


#1

Hello fellow T-tards,

I am looking to set up a visit window calculator in an Excel document, where visits will be scheduled either every 4 weeks (+/- 5 days) or every 6 weeks (+/- 5 days) relative to the initial visit.

For example, say I met someone today, 11Aug and they are on the 4 week plan. Visit 2 would need to be within 23 to 33 days from that date (3Sep to 13Sep), V3 would need to be between 51 to 61 days (1Oct to 11Oct), V4 would need to be between 79 and 89 days (29Oct to 8Nov), etc. I would like the function to calculate these ranges for me based on my single input at Visit 1.

V1 would be entered in cell E7, with the subsequent visit windows occurring in every cell in row 6 from G onwards (if that matters for an explanation).

Anyone know how to go about doing this?


#2

V1 (earliest date) =today()+23
V1 (latest date) =today()+33

V2 (earliest date) =today()+51
V2 (latest date) =today()+61

V3 (earliest date) =today()+79
V3 (latest date) =today()+89

Like that?


#3

That’s going to change the revisit dates everyday.

You can put two dates into one cell using the & symbol so it becomes:

= E7 + 23 & " - " & E7 + 33

You can then use conditional formatting to make the cell yellow when the date is approaching and green when it is in range or whatever.

Note: I dont have excel infront of me so this could be a bit (or a lot) off.


#4

Close, but it only works for either the +23 or +33, not both (converts to two numbers separated by the -, otherwise).

But, showing me that gave MUCH better context to a lot of the crazy ass equations floating around the web, so I was able to make it work by formatting the targeted cells to “general” and entering =TEXT(E7+23,“ddmmmyy”)&" - "&TEXT(E7+33,“ddmmmyy”).

I don’t know what conditional formatting is, but I like the sound of it. My job as it relates to this is retrospective monitoring; the purpose being to ensure that the actual subject visit took place within the specified visit window. Can a cell containing the actual visit date be manipulated such that an out of window date causes a color change, or is that too much shit to shove into one cell (or does changing the visit range cell to a general format make that impossible)?


#5

yes, it can. Hard to explain through a forum, but yes.

Apply the conditional formating to your cell using a rule that you are wanting, in this case the comparison of the dates. The formula rule would be something like visit_date>window_date and then the formating would change to a specific color of your choosing. You could scale it to change to yellow if the date is close, red if its over… whatever you want.


#6

Word, thanks man. The initial function will work with a date-based format after all, so hopefully that eases the process.

Realistically, it doesn’t much matter because this is (obviously) an extremely simple data point to confirm, but little conveniences add up, and over time I’d like to streamline more of these small checks to save energy and sanity for the more important shit (you’d think in 2016 this wouldn’t be a problem, but from what I can tell Big Pharma execs would rather spend money to their yachts than their oversight systems in clinical research).


#7

Like this


#8

Just about, but for me it’s one tab per person since it includes data for the visit, as well. E.g., :

Basically, just a clinical protocol’s schedule of assessments, but with various functions incorporated to double check, at the very least, the minutiae of safety variables (e.g., percentage change in BP from previous visit) I might not properly evaluate for whatever reason. Many of these are already programmed into the data capture software that’s used to report this information officially, but the turnaround time for detected deviations is often inexcusable.

In any event, I figure I’ll just determine what I need to have done and contract someone to punch it all in for me, since it would be nice to have within the next decade. I appreciate the help, though.


#9

Resurrection

Can anyone explain to me why this simple if function isn’t working?


#10

because you fucking suck


#11

Shit, do you even know how to open excel…?


#12

Make sure your data types are the same. You might have column B as General, P as text.


#13

I actually tried that and it still didn’t work. Column P is a vlookup from another tab. I’ve tried adjusting the data types in both.

It’s not crucial, but it’s annoying…

I just use it as a check to make sure the division codes match the corresponding product types (which are hidden in the picture).


#14

Thats what I was gonna say!

No really though, are there any rules about using “y” in a function for anything other than a variable?

I know thats gonna look dumb.


#15

Not in an if function because it’s just returning the letter or word you place in quotes.


#16

Again depending on the data types, you could also have a leading space in one of your fields. So it may be comparing [space]17 to 17. 3 characters is not the same as 2, so it is returning the no.


#17

I’ll double check and see if that’s the case. Thanks.

It’s just weird because it works for some, but not all of the line items.


#18

I still think you are ending up with different data types somehow. The give-away is the numbers in column B are right-justified and P is left-justified.

Maybe check the data types of the column you are looking up against.

I guarantee if you copy/paste special -> values and set your data types to general or number for both fields, the formula will work. But that’s probably defeating whatever it is you’re trying to accomplish.

Your formula is fine, your data are apples to oranges somehow.


#19

Would it make a difference if it were phrased differently?

" If B=P Then “y” Or “no” " seems different than a basic If Then statement because there is an Or.

As written, the first and second commas would both stand for Or with no Then.


#20

I can write out a neat looking table without any formula or anything, but that’s about the extent of it…